Loading…

Andrew J. Price | Technical Blog

Office Servers and Services MVP | Founder of UC and Cloud Day | Founder of Microsoft Cloud User Group

Powershell Script: Set your Skype for Business Databases into the correct state for SQL AlwaysOn

Hello Readers

I have been recently working with a lot of Skype for Business Deployments with SQL AlwaysOn Backend Servers and decided to make a little script which helps take some of the pain out the process. This script will Alter the SQL Databases into Full Recovery Mode, execute a SQL Backup and Robocopy the CsData Folder Directories from the Primary SQL Server to the Secondary SQL Server. This will then allow you add the databases into the Availability Group with out performing any more manual steps to the Skype for Business databases and build process.

This script will alter and backup the following Skype for Business databases

  • cpsdyn
  • rgsconfig
  • rgsdyn
  • rtcab
  • rtcshared
  • rtcxds
  • xds
  • lis
  • LcsCDR
  • QoEMetrics
  • LcsLog

If one of the above database is not located on the SQL Backend this script will skip the missing database and continue altering all existing Skype for Business databases. You will receive the below output if the database is not found

image

1)

Chapter 1. How do I use this Backup-SQLDatabaseSFB.ps1 script?

Launch Powershell as an Administrator on your Primary SQL Server

image

Please note: If you dont run this script as an Admin you will receive the below error

image

Input your Primary SQL Server Name

image

Input your SQL Server Instance Name (if you are using the Default Instance, just press enter)

image

If you haven’t deployed all your Skype for Business Databases to the Primary SQL Server you may see failure output referencing the missing database(s) as shown below

image

If successful and all Skype for Business Databases exist you should see the below output

image

You will now be prompted for the Source Location of the CSData Folder Struture that needs to be copied to all other SQL AlwaysOn Nodes.

image

Enter your source which will be the same as the Primary SQL Server you entered earlier in this script.

You now need to specify the following

  • Primary SQL Database Drive Location
  • Primary SQL Log Drive Location

In my lab else are set to the following and this will need to be in the $ format

  • e$
  • f$

image

image

You now need to specify the following

  • Target SQL Server Name
  • Target SQL Database Drive Location
  • Target SQL Log Drive Location

Again In my lab else are set to the following and this will need to be in the $ format

  • e$
  • f$

image

image

You will now be prompted for any additional SQL AlwaysOn nodes. If you have more servers to copy the folder structure to Press “Yes” and specify the target location. If no, Press “No”

image

When you press “No” you will have successfully copoed directories from Primary SQL Server to the Secondary SQL Server(s) and you will receive a SUCCESS!!! Notification Window

image

In order to download this script, please click on the following link https://gallery.technet.microsoft.com/Alter-Your-Skype-for-4d3d91f9

If you have any feedback, please drop me an email ( andrew.price@lyncme.co.uk )

 

Chapter 2. Notes

.SYNOPSIS
Configures the necessary prerequisites for Skype for Business Enterprise 2015 with SQL AlwaysOn 2012/2014.

.DESCRIPTION
Invoke all Skype for Business Databases into Full Recovery Mode in the Back End Server and then Backs up all Skype for Business Back End Databases. This script runs against all Skype for Business Databases

.NOTES
Version                : 1.3 – See changelog at http://lyncme.co.uk/skype-for-business-2015/skype-for-business-sql-always-on-database-script-change-log/
Wish list            : Better error trapping
: Prompt for additional Secondary SQL Servers
Rights Required        : Local administrator on server
: SQL SysAdmin
: Powershell in Administrator Mode
Sched Task Required    : No
SQL Server Version    : SQL Server Enterprise 2012/2014 (tested through Service Pack 3)
Author/Copyright        : © Andrew J. Price, Office Servers and Services MVP – All Rights Reserved
Email/Blog/Twitter    : andrew.price@lyncme.co.uk    http://www.lyncme.co.uk @andrewpriceuc

Dedicated Post        : http://lyncme.co.uk/skype-for-business-2015/sql-alwayson-script

Disclaimer           : You running this script means you won’t blame me if this breaks your stuff. This script is provided AS IS without warranty of any kind.
I disclaim all implied warranties including,
without limitation, any implied warranties of merchantability or of fitness for a particular
purpose. The entire risk arising out of the use or performance of the sample scripts and
documentation remains with you. In no event shall I be liable for any damages whatsoever
(including, without limitation, damages for loss of business profits, business interruption,
loss of business information, or other pecuniary loss) arising out of the use of or inability
to use the script or documentation.

Acknowledgements             : Andrew Offor
:
Assumptions                    : ExecutionPolicy of AllSigned (recommended), RemoteSigned or Unrestricted (not recommended)
: Remote Connections are allowed to the SQL Server
Limitations                    : Only works with SQL Server 2012 Enterprise and SQL Server 2014 Enterprise.
Known issues                :

Version Changes           : 0.1 Initial Script Build
: 0.2 Minor Script Changes (Added SQL Database Check and Skip if not present)
: 0.3 Added Robocopy for Database/Log Information
: 1.0 Ready for General Release
: 1.1 Remove Persistent Chat Databases (Not Supported for AlwaysOn)
: 1.2 Robocopy points to the CsData Directory where Skype for Business Databases should be held
: 1.3 Check if Script is running as Administrator, Additional SQL Node Support
.LINK
http://www.lyncme.co.uk

.EXAMPLE
.\Backup-SQLDatabaseSFB.ps1

Description
———–
Runs script with default values.
.INPUTS
None. You cannot pipe objects to this script.

Regards

Andrew J. Price

Office Servers and Services MVP

Leave a Reply