STOP: READ ALL OF THE INSTRUCTIONS BEFORE STARTING!
What you will need
|Usage||Description||Section Used||Setting||IP Address|
|1st Server in the cluster||SQL Server Primary||2, 9.B||Sjcsql1|
|2nd Server in the cluster||SQL Server Secondary||2,13.D||Sjcsql2|
|The name of the cluster||The Cluser Name at the OS between the two servers||4||sqlcluster||10.1.1.36|
|Location of the Quorum||The file share used for the SQL Server Quorum||6.B||\\ca1.berns.local\SQLWitness|
|SQL Server Service Account||Account used to run SQL Server and access the shared files||7.A, 7.O||2014SQLService|
|Reporting Service Account||Service Account used for reporting||7.O||ReportingSvc|
|The Name of the SQL Instance||Configuring SQL server for SFB needs a SQL Server Instance||7.M||Sfb2015EE|
|SQL Administrator||Users to be added as admins during SQL Instance setup||7.Q||Administrators,Domain Admins|
|SQL Server file Locations for the instance Database||Location of where the databases will be stored for SfB||7.Q||C:\Program Files\Microsoft SQLServer\MSSQL12.SFB2015EE|
|SQL Server file Locations for the instance Logs||Location of where the Logs will be stored for SfB||7.Q||C:\Program Files\Microsoft SQLServer\MSSQL12.SFB2015EE|
|Temp DB file location||Location of the System Temp and Master DB and Logs||7.Q||Default|
|Backup File Location||Where the default backup files are unless specificed from a backup process||7.Q||Default|
|Availbility Grup Name||Defines the linkage between the two SQL Servers. Can be the same as the Listener Name||11.A||sfbsqlpool|
|Always On Listener Name.||This is what is used in topology builder for the SQL Instance Name||9.B, 11.G, 13.F||Sfbsqlpool||10.1.1.38|
|File Share for Replication on Always On||Used to copy initial files. Can be same as Withness share||11.H||\\ca1.berns.local\SQLWitness|
Note that in a real world production, or even a lab, I would never recommend leaving files, especially the user databases (the ones used for SfB), in the default location. And for the purpose of symplifying the SQL Always on, I would make it a nice short path to work with. Long ago in the early days of SQL server, long file paths would even cause performance issues, really!
- Networking: There should be multiple paths between the two systems. Meaning one dedicated for Cluster resources, and two configuring in teaming for HA.
- Disk: You need fast disks, in a configuration that will support single or multiple disk failure for both the databases and logs, which should be on separate volumes. You will want multiple disks for this. Separate disks for database and logs for Skype for Business, plus separate for the temp DB, plus backup, so at least 4 (temp can share a volume). You may even want another to install the SQL server instance to.
Create all the DNS entries required. This includes
- The Cluster Name
- Listener Name
- On each SQL Server install Failover Clustering. You will need to mount the Windows 2012 R2 ISO and assign it to letter d, or just change the –Source path to match
|Add-WindowsFeature Net-Framework-Core, Failover-Clustering, RSAT-Clustering-Mgmt,RSAT-Clustering-PowerShell -Source d:\sources\sxs|
- Once that is done you will want to test it to make sure the two nodes can talk
|Test-Cluster SJCSQL1,SJCSQL2 -ReportName c:\source\validationreport.mht|
In a lab you can ignore those errors, but what does that it mean? Remember, this is designed for a high performance always available SQL server, so it tests more than just connectivity. There is a lot to configuring HA on SQL server than this. There are so many variations on what it takes to make this all HA, more than what I have time for here. But if you look through that list, it is pretty simple to figure out the basics. But here is a summary of some of it. This is not a definitive guide. I am not a SQL guru.
Basically, make everything redundant. Once you have addressed the issues from the output to meet your needs, continue on.
- Create the actual cluster
|New-Cluster -Name sqlcluster -Node sjcsql1,sjcsql2 -NoStorage -StaticAddress 10.1.1.36|
- Add the FQDN of the cluster to DNS, you don’t need the reverse pointer record
- Build the Quorum. This can either be a SQL Server or a file share. Here are the recommendations for a Quorum
- Use a Server Message Block (SMB) share on a Windows Server 2003 or Windows Server 2008 file server.
- Make sure that the file share has a minimum of 5 MB of free space.
- Make sure that the file share is dedicated to the cluster and is not used in other ways (including storage of user or application data).
- Do not place the share on a node that is a member of this cluster or will become a member of this cluster in the future.
- You can place the share on a file server that has multiple file shares servicing different purposes. This may include multiple file share witnesses, each one a dedicated share. You can even place the share on a clustered file server (in a different cluster), which would typically be a clustered file server containing multiple file shares servicing different purposes.
- For a multi-site cluster, you can co-locate the external file share at one of the sites where a node or nodes are located. However, we recommend that you configure the external share in a separate third site.
- Place the file share on a server that is a member of a domain, in the same forest as the cluster nodes.
- For the folder that the file share uses, make sure that the administrator has Full Control share and NTFS permissions.
- Do not use a file share that is part of a Distributed File System (DFS) Namespace.
- On the file server running create a file share, for example SQLWitness.
- For the share permissions give the person full control of the file share, either through a group such as domain admins or specific user and remove the everyone group, and make sure the same user has equivalent NTFS permissions
|Set-ClusterQuorum –Cluster sqlcluster –NodeAndFileShareMajority “\\ca1.berns.local\SQLWitness”|
The command will create the correct permmisions
Now we can finally install SQL Server 2014 Enterprise Edition on the Both Servers, using the EXACT SAME CONFIGURATIONS!
- Create a SQL Server Service Account. The password should be set to never expire since if it does, and you don’t change it, SfB will come to a halt.
Make sure that account can login locally, logon as batch job, Lock pages in memory, and login as a service account to those servers. This can be done by adding it to the local administrators group or through specific group policies Do this on BOTH servers
- Create a second account used for SQL Reporting services. This needs the same permissions, and don’t forget to do it to BOTH servers.
Go into Installation and choose New Installation
- Do not check for updates, it is quicker to do it after the install
- Let it go through the tests, but I am guessing you did not check firewall requirements did you?
Perform a new installation
- Enter the license key because you are not using MSDN in production.
- Read the EULA, and then click Accept
Choose SQL Server Feature Installation
- Install Database Engine Services, Reporting Services Native (QoE will not work with SharePoint services), and Management tools
Generally you can install the binaries on the C Drive, that is just the service and tools, we change the data location later
Now Create the Instance, for example SFB2015EE
- For the Service accounts, pick the ones SQL Server Service Account for Agent and Engine. Chose the Reporting Service account for Reporting Service
Change the agent service to automatic
- Use Windows Authentication and Add the Current User, and any other users that will manage the DB
- Click on Data Directories Tab. This is where you can re-locate the databases easily, before you deploy them. In this lab, I just have it all on the C drive. IN realty I would have the user database directory and the user log directory on two different volumes. I would put the temp DB and Temp DB log on its own volume (it’s okay to share this), and then the backup directory on yet a different volume.
Choose to Install and configure reporting services. It is just easier to do it now rather than later
- Make sure everything is configure happily as you want it and finally click install.
- Download and install the updates. You can find the download list here: https://technet.microsoft.com/en-us/library/ff803383.aspx?f=255&MSPPError=-2147217396. Once you have started the download. Once the download has completed and the install process has completed, apply the latest update. Depending on other databases you may choose to install the update on all instances or just this one. The update takes longer than the install process.
Configure Always On
Open up the SQL Server Configuration Manager, Select SQL Server Service and get the properties for the SQL Server Instance and Select the tab AlwaysOn High Availability and Check the box to Enable AlwaysOn
- Restart the SQL Server Services
STOP! From here on out, use only the SQL server for the rest of the configuration information until stated otherwise.
Configure the SQL Server store in Skype for Business
- In topology builder Define a new SQL Server Store.
Enter the SQL Listener Name, NOT THE SQL SERVER, enter the instance, and under High availability choose SQL ALwaysOn Availability Groups, and then in the SQL Server FQDN, enter the SQL server that you have done in the configuration. We have to use the FQDN of the server we did the work on because the other half of the instance is not configured, so you may or may not be able to connect to the correct server. We will change this later.
Install or upgrade the database
Select the checkbox for the new SQL Server and highlight it and choose advanced
Choose the option to use instance defaults or physical enter the path to the files (such as l:\database), never choose to let the install decide
You did it all right, you should get this, but don’t get all excited, we just know now that everything is setup. Don’t bother looking in the databases, because nothing is there since there is nothing in topology connected to it. So now you need to deploy the rest of your topology using this database. Which I will not do since if you are reading this, you know SfB already.
When you go to build your topology choose the SQL Listener Instance that you created
Prepare the databases for alwaysOn. There is a lot we have to get ready, and if you don’t do this now, you will just have to start the AlwaysOn Wizard again.
To do it manually, follow these steps for each SfB Database
Go to propertiesàOptions and Change the Recovery Mode to Full
Go to tasksàBack Up… and choose the backup location as desired and click OK
OR to script it from SQL Server SQL Command, you can change the backup path if you want
|DECLARE @command varchar(1000)
SELECT @command = ‘BACKUP DATABASE ? TO DISK = N”C:\Program Files\Microsoft SQL Server\MSSQL12.SFB2015EE\MSSQL\Backup\?.bak” WITH NOFORMAT, NOINIT, NAME = N”?-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10’
- Create the file structure on the secondary server, but do not copy the files. I know the path here is different than other paths. My lab took a digger and I had to rebuild things, and when I did, during the DB install I did c:\csdata. But the concept is the same
|xcopy c:\CSData \\sjcsql2\c$\csdata /T /E|
Configure Always on
Open SQL Server management studio on the primary server expand AlwaysOn High Availability and choose New Availability Group Wizard
Define a Name, make it something Descriptive
- Now because you did the setup earlier to do the recovery mode and did the backup, you can check all the boxes, if you did not do that you the boxes will be gray and say Full Recovery mode is required of full backup required
Select all the databases except the reporting server, those are not supported
Click on Add Replica and connect to the second server
Check the box for automatic failover
Go to the Listener tab and for the Listener DNS Name, enter the Listener pool Name, use port 1433 and choose Static IP for Network Mode. Under the subnet/ IP Address field is an add button, it is hard to see, click add and add the IP address of the Listener pool name You should add this into DNS, but not required
Choose full file replication, this needs to be a file share both systems can access, you can use your Quorum file share if you want. It must be accessible by the SQL Server Service
Congratulations, if you see all green checks, you are good to go. IF the shared network shows an error you may need to add the service account used for the SQL Server Modify permissions on the share level and NTFS Level of the SQL Witness Share.
Validating everything is configured correctly
If you expand Availability groups, you will see the configuration listed
- Notice the comparison of security groups, they do not match up, so we need to fix that
Fix the Permissions on the database by failing it over to the second server
Right click on the group and choose Failover
Select the new primary, only the other one is listed
Go back to topology builder and edit the SQL server properties and for the SQL Server FQDN enter the second server, sjcsql2 and publish the topology
- Update the database from Powershell.
|Install-CsDatabase -Update -SqlServerFqdn sfbsqlpool.berns.local -ConfiguredDatabases -ForInstance sfb2015ee|
Last configure topology builder to use the pool name for SQL Server, this is the pool name, not the cluster name, this is the name you configured in the listener and publish the topology
So there are a few notes I want to mention here.
When I first did this, the intention was to move the Central Management Store to an Enterprise Pool. I kept getting stuck that I could not connect to the database during the move. I believe this is from one of two things.
- After I ran Install-CsDatabase -CentralManagementDatabase -SQLServerFQDN sjcsql1.berns.local -SQLInstanceName SFB2015EE I did not add it to the Always on Pool. However on the rebuild, I added the CMS database before I enabled the always on.
- It may have been an issue with SQL 2014 CU 2. Remember when I had to rebuild everything, I did not apply CU2, this is just RTM version
- So my problem could have been either a or b, since I did two changes at once, it’s hard to know. So that was a bad thing.
- It is very important that both databases are the exact same (physical things do not count, I mean application and databases).