Skype for Business, SQL Server

Always On deployment in Skype for Business

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.

 

  1. Create all the DNS entries required. This includes
    1. The Cluster Name
    2. Listener Name
  2. 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

 

Install Clustering:

  1. 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.

 

  1. Create the actual cluster
New-Cluster -Name sqlcluster -Node sjcsql1,sjcsql2 -NoStorage -StaticAddress 10.1.1.36

 

 

 

  1. Add the FQDN of the cluster to DNS, you don’t need the reverse pointer record
  2. 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.
  1. On the file server running create a file share, for example SQLWitness.
  2. 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”

 

  1. The command will create the correct permmisions

  1. Now we can finally install SQL Server 2014 Enterprise Edition on the Both Servers, using the EXACT SAME CONFIGURATIONS!
    1. 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.
    2. 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

    3. Create a second account used for SQL Reporting services. This needs the same permissions, and don’t forget to do it to BOTH servers.
    4. Go into Installation and choose New Installation

    5. Do not check for updates, it is quicker to do it after the install
    6. Let it go through the tests, but I am guessing you did not check firewall requirements did you?
    7. Perform a new installation

    8. Enter the license key because you are not using MSDN in production.
    9. Read the EULA, and then click Accept
    10. Choose SQL Server Feature Installation

    11. Install Database Engine Services, Reporting Services Native (QoE will not work with SharePoint services), and Management tools
    12. Generally you can install the binaries on the C Drive, that is just the service and tools, we change the data location later

    13. Now Create the Instance, for example SFB2015EE

    14. For the Service accounts, pick the ones SQL Server Service Account for Agent and Engine. Chose the Reporting Service account for Reporting Service
    15. Change the agent service to automatic

    16. Use Windows Authentication and Add the Current User, and any other users that will manage the DB
    17. 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.

    1. Choose to Install and configure reporting services. It is just easier to do it now rather than later

    2. Make sure everything is configure happily as you want it and finally click install.
    3. 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.
  2. Configure Always On
    1. 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

    2. 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.

  1. Configure the SQL Server store in Skype for Business
    1. In topology builder Define a new SQL Server Store.
    2. 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.

    3. Install or upgrade the database

    4. Select the checkbox for the new SQL Server and highlight it and choose advanced

    5. 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

    6. 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.

    7. When you go to build your topology choose the SQL Listener Instance that you created

       

  2. 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.
    1. 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 = ‘ALTER DATABASE ? SET RECOVERY FULL WITH NO_WAIT;’

EXEC sp_MSforeachdb @command

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’

EXEC sp_MSforeachdb @command

  1. 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

 

  1. Configure Always on
    1. Open SQL Server management studio on the primary server expand AlwaysOn High Availability and choose New Availability Group Wizard

    2. Define a Name, make it something Descriptive

    3. 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
    4. Select all the databases except the reporting server, those are not supported

    5. Click on Add Replica and connect to the second server

    6. Check the box for automatic failover

    7. 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

    8. 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

    9. 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.

  2. Validating everything is configured correctly
    1. If you expand Availability groups, you will see the configuration listed

    2. Notice the comparison of security groups, they do not match up, so we need to fix that
  1. Fix the Permissions on the database by failing it over to the second server
    1. Right click on the group and choose Failover

    2. Select the new primary, only the other one is listed

    3. Click Connect

    4. 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

    5. Update the database from Powershell.
Install-CsDatabase -Update -SqlServerFqdn sfbsqlpool.berns.local -ConfiguredDatabases -ForInstance sfb2015ee

 

 

  1. 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.

  1. 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.
    1. 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.
    2. 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
    3. 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.
    4. It is very important that both databases are the exact same (physical things do not count, I mean application and databases).

52 thoughts on “Always On deployment in Skype for Business

  1. Pingback: brand cialis
  2. Pingback: buy cialis now
  3. Pingback: viagra on line
  4. Pingback: cialis vs viagra
  5. Pingback: viagra alternative
  6. Pingback: viagra
  7. Pingback: thesis help free
  8. Pingback: cheap ed pills
  9. Pingback: buy viagra online
  10. Pingback: cheap sildenafil
  11. Pingback: viagra
  12. Pingback: online levitra
  13. Pingback: viagra for sale
  14. Pingback: viagra for sale
  15. Pingback: viagra 100mg
  16. Pingback: tadalafil online
  17. Pingback: cialis dosage
  18. Discount Free Shipping Progesterone Pills Quick Shipping Pharmacy Cialis E Gravidanza [url=http://cialibuy.com]tadalafil cialis from india[/url] Macrobid 100mg Discount Worldwide Pharmacy Next Day California Can Amoxil Expire Amoxicilina Where To Buy Medication

  19. Bupropion Online Drugstore India Pay For Cialis With Paypal Zithromax And Alcohol Interaction [url=http://cialibuy.com]Cialis[/url] Viagra C’Est Quoi Acheter Cialis En Angleterre

  20. Buy Amoxcillin Overseas Viagra Ohne Zollprobleme Zithromax Shipped Overnight [url=http://cialibuy.com]Buy Cialis[/url] Comprar Cialis Receta Cialis Brausetabletten Pfizer Viagra Cheap

  21. 247overnightpharmacy Amoxicillin Dosage Dental Viagra Professional 100mg [url=http://cialibuy.com]Cialis[/url] Achat De Clomid Pharmacies En Ligne Cialis Da Comprare

  22. Reputable Online Pharmacies Uk Impacto De Propecia Cialis Temoignages [url=http://achetercialisfr.com]cialis rue monge[/url] Achat Pilule Viagra Popularity For Amoxicillin

Leave a Reply

Your email address will not be published. Required fields are marked *