Moving Lync databases including CMS and the monitoring databases to a new SQL server – #Lync

The topic for today’s work was to move a Lync installation from an old SQL to a new one. And that sounds pretty easy right? We all have kind of done it all the time when moving from a Standard Lync server to an Enterprise, when the customer moves out of PoC (and NO I don’t do PoCs on standard edition anymore since its 100% chance here that the customers decide to run Lync if they have already taken the decision to do a PoC, so if the customer has requirements to go for enterprise later on, we do it right from the beginning) anyway there is one requirement when moving this that I kind of missed before and that could have saved me sometime today, and that is that you can’t move the CMS to a server in the same pool. So you have to set up a Standard edition pool and then move the CMS to that pool, and then move the databases to the new SQL and restore them. And then move the CMS to the new SQL server.

This whole process took me about 4 hours. But that included the installation of the temporary Lync Standard server to host the CMS.

So the steps would be:

Backup your configuration.
1. Export-CSConfiguration –FileName c:\CSBackup\CsConfiguration120702.zip
2. Export-CSLISConfiguration –FileName c:\CSBackup\CsLIS120702.zip
3. Ref: http://technet.microsoft.com/en-us/library/gg398627
Prepare the Standard Edition server so that it can be used as a Central Management Server host.
Install a new CMS DB on the new SE server
1. Install-CSDatabase –CentralManagementDatabase –Clean –SqlServerFqdn NewSELync.Domain.com –SqlInstanceName rtc –UseDefaultSqlPaths
  1. Note that this command use the –clean switch. If this parameter is included, Install-CsDatabase will delete and reinstall databases as needed. If this parameter is not included, Install-CsDatabase will not overwrite any existing databases. You cannot use both Clean and Update in the same command.
  2. Ref: http://technet.microsoft.com/en-us/library/gg399044.aspx
Move the CMS to the temporary standard edition server (run this command on the server where you want to move the CMS TO, in this case the standard edition server)
1. Move-CSManagementServer
2. http://technet.microsoft.com/en-us/library/gg412921
Make sure that the replication is done before proceeding.
1. Get-CSManagementStoreReplicationStatus
2. Ref: http://technet.microsoft.com/en-us/library/gg399052
Run local setup on all servers that previously hosted the CMS
Backup the Lync Databases old SQL server run the following: (!!!!!! From this Point on your users will be in Limited functionality mode for about 30 minutes !!!!!)
1. First take the databases offline and set them in single user mode so that they can be properly backed up.
  
  ALTER DATABASE rgsconfig SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rgsconfig SET SINGLE_USER
  
  ALTER DATABASE rgsconfig SET ONLINE
  
  ALTER DATABASE rgsdyn SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rgsdyn SET SINGLE_USER
  
  ALTER DATABASE rgsdyn SET ONLINE
  
  ALTER DATABASE rtcab SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rtcab SET SINGLE_USER
  
  ALTER DATABASE rtcab SET ONLINE
  
  ALTER DATABASE rtcab1 SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rtcab1 SET SINGLE_USER
  
  ALTER DATABASE rtcab1 SET ONLINE
  
  ALTER DATABASE rtcdyn SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rtcdyn SET SINGLE_USER
  
  ALTER DATABASE rtcdyn SET ONLINE
  
  ALTER DATABASE rtc SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rtc SET SINGLE_USER
  
  ALTER DATABASE rtc SET ONLINE
  
  ALTER DATABASE LcsCDR SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE LcsCDR SET SINGLE_USER
  
  ALTER DATABASE LcsCDR SET ONLINE
  
  ALTER DATABASE lis SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE lis SET SINGLE_USER
  
  ALTER DATABASE lis SET ONLINE
  
  ALTER DATABASE QoEMetrics SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE QoEMetrics SET SINGLE_USER
  
  ALTER DATABASE QoEMetrics SET ONLINE
Back up the databases
1. This will back them up to g: so you would have to change it to a driver that works for you.
  
  USE Master;
  
  GO
  
  BACKUP DATABASE cpsdyn
  
  TO DISK = ‘G:\CSBackup\cpsdyn.bak’
  
  GO
  
  BACKUP DATABASE rgsconfig
  
  TO DISK = ‘G:\CSBackup\rgsconfig.bak’
  
  GO
  
  BACKUP DATABASE rgsdyn
  
  TO DISK = ‘G:\CSBackup\rgsdyn.bak’
  
  GO
  
  BACKUP DATABASE rtcab
  
  TO DISK = ‘G:\CSBackup\rtcab.bak’
  
  GO
  
  BACKUP DATABASE rtcab1
  
  TO DISK = ‘G:\CSBackup\rtcab1.bak’
  
  GO
  
  BACKUP DATABASE rtcdyn
  
  TO DISK = ‘G:\CSBackup\rtcdyn.bak’
  
  GO
  
  BACKUP DATABASE rtc
  
  TO DISK = ‘G:\CSBackup\rtc.bak’
  
  GO
  
  BACKUP DATABASE LcsCDR
  
  TO DISK = ‘G:\CSBackup\LcsCDR.bak’
  
  GO
  
  BACKUP DATABASE lis
  
  TO DISK = ‘G:\CSBackup\lis.bak’
  
  GO
  
  BACKUP DATABASE QoEMetrics
  
  TO DISK = ‘G:\CSBackup\QoEMetrics.bak’
Add the new SQL Store in the topology builder and publish it.
1. When you press publish the topology builder will create the databases on the new SQL server and it will also try to create them on the old server. Just uncheck the old server from the publishing wizard and continue, but don’t remove the old SQL store from the topology just yet.
2. If you hosted the file share on the SQL server it might be a good idea to update the location for it in this step as well.
3. Also point the monitoring server to the new database in this step.
Restore the databases to the new SQL server,
1. Copy the files that you backed up previously to the new SQL server and run the following to take the empty databases offline and in single user mode so that they can be overwritten.
  
  ALTER DATABASE cpsdyn SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE cpsdyn SET SINGLE_USER
  
  ALTER DATABASE cpsdyn SET ONLINE
  
  ALTER DATABASE rgsconfig SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rgsconfig SET SINGLE_USER
  
  ALTER DATABASE rgsconfig SET ONLINE
  
  ALTER DATABASE rgsdyn SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rgsdyn SET SINGLE_USER
  
  ALTER DATABASE rgsdyn SET ONLINE
  
  ALTER DATABASE rtcab SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rtcab SET SINGLE_USER
  
  ALTER DATABASE rtcab SET ONLINE
  
  ALTER DATABASE rtcab1 SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rtcab1 SET SINGLE_USER
  
  ALTER DATABASE rtcab1 SET ONLINE
  
  ALTER DATABASE rtcdyn SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rtcdyn SET SINGLE_USER
  
  ALTER DATABASE rtcdyn SET ONLINE
  
  ALTER DATABASE rtc SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE rtc SET SINGLE_USER
  
  ALTER DATABASE rtc SET ONLINE
  
  ALTER DATABASE LcsCDR SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE LcsCDR SET SINGLE_USER
  
  ALTER DATABASE LcsCDR SET ONLINE
  
  ALTER DATABASE lis SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE lis SET SINGLE_USER
  
  ALTER DATABASE lis SET ONLINE
  
  ALTER DATABASE QoEMetrics SET OFFLINE WITH ROLLBACK AFTER 10 Seconds
  
  ALTER DATABASE QoEMetrics SET SINGLE_USER
  
  ALTER DATABASE QoEMetrics SET ONLINE
Restore the databases by running the following.
1. You must manually change the paths
  
  RESTORE DATABASE [cpsdyn] FROM DISK = N’C:\CSBackup\cpsdyn.bak’ WITH FILE = 1,
  
  MOVE N’cpsdyn_data’ TO N’E:\SQL Server\MSSQL10_50.LYNC\MSSQL\DATA\cpsdyn.mdf’,
  
  MOVE N’cpsdyn_log’ TO N’F:\LyncSQLLog\CPSDYN.LDF’,
  
  NOUNLOAD, REPLACE, STATS = 10
  
  GO
  
  RESTORE DATABASE [rgsconfig] FROM DISK = N’C:\CSBackup\rgsconfig.bak’ WITH FILE = 1,
  
  MOVE N’rgsconfig_data’ TO N’E:\SQL Server\MSSQL10_50.LYNC\MSSQL\DATA\rgsconfig.mdf’,
  
  MOVE N’rgsconfig_log’ TO N’F:\LyncSQLLog\rgsconfig.LDF’,
  
  NOUNLOAD, REPLACE, STATS = 10
  
  GO
  
  RESTORE DATABASE [rgsdyn] FROM DISK = N’C:\CSBackup\rgsdyn.bak’ WITH FILE = 1,
  
  MOVE N’rgsdyn_data’ TO N’E:\SQL Server\MSSQL10_50.LYNC\MSSQL\DATA\rgsdyn.mdf’,
  
  MOVE N’rgsdyn_log’ TO N’F:\LyncSQLLog\rgsdyn.LDF’,
  
  NOUNLOAD, REPLACE, STATS = 10
  
  GO
  
  RESTORE DATABASE [rtcab] FROM DISK = N’C:\CSBackup\rtcab.bak’ WITH FILE = 1,
  
  MOVE N’rtcab_data’ TO N’E:\SQL Server\MSSQL10_50.LYNC\MSSQL\DATA\rtcab.mdf’,
  
  MOVE N’rtcab_log’ TO N’F:\LyncSQLLog\rtcab.LDF’,
  
  NOUNLOAD, REPLACE, STATS = 10
  
  GO
  
  RESTORE DATABASE [rtcab1] FROM DISK = N’C:\CSBackup\rtcab1.bak’ WITH FILE = 1,
  
  MOVE N’rtcab1_data’ TO N’E:\SQL Server\MSSQL10_50.LYNC\MSSQL\DATA\rtcab1.mdf’,
  
  MOVE N’rtcab1_log’ TO N’F:\LyncSQLLog\rtcab1.LDF’,
  
  NOUNLOAD, REPLACE, STATS = 10
  
  GO
  
  RESTORE DATABASE [rtcdyn] FROM DISK = N’C:\CSBackup\rtcdyn.bak’ WITH FILE = 1,
  
  MOVE N’rtcdyn_data’ TO N’E:\SQL Server\MSSQL10_50.LYNC\MSSQL\DATA\rtcdyn.mdf’,
  
  MOVE N’rtcdyn_log’ TO N’F:\LyncSQLLog\rtcdyn.LDF’,
  
  NOUNLOAD, REPLACE, STATS = 10
  
  GO
  
  RESTORE DATABASE [rtc] FROM DISK = N’C:\CSBackup\rtc.bak’ WITH FILE = 1,
  
  MOVE N’rtc_data’ TO N’E:\SQL Server\MSSQL10_50.LYNC\MSSQL\DATA\rtc.mdf’,
  
  MOVE N’rtc_log’ TO N’F:\LyncSQLLog\rtc.LDF’,
  
  NOUNLOAD, REPLACE, STATS = 10
  
  GO
  
  RESTORE DATABASE [LcsCDR] FROM DISK = N’C:\CSBackup\LcsCDR.bak’ WITH FILE = 1,
  
  MOVE N’LcsCDR_data’ TO N’E:\SQL Server\MSSQL10_50.LYNC\MSSQL\DATA\LcsCDR.mdf’,
  
  MOVE N’LcsCDR_log’ TO N’F:\LyncSQLLog\LcsCDR.LDF’,
  
  NOUNLOAD, REPLACE, STATS = 10
  
  GO
  
  RESTORE DATABASE [lis] FROM DISK = N’C:\CSBackup\lis.bak’ WITH FILE = 1,
  
  MOVE N’lis_data’ TO N’E:\SQL Server\MSSQL10_50.LYNC\MSSQL\DATA\lis.mdf’,
  
  MOVE N’lis_log’ TO N’F:\LyncSQLLog\lis.LDF’,
  
  NOUNLOAD, REPLACE, STATS = 10
  
  GO
  
  RESTORE DATABASE [QoEMetrics] FROM DISK = N’C:\CSBackup\QoEMetrics.bak’ WITH FILE = 1,
  
  MOVE N’QoEMetrics_data’ TO N’E:\SQL Server\MSSQL10_50.LYNC\MSSQL\DATA\QoEMetrics.mdf’,
  
  MOVE N’QoEMetrics_log’ TO N’F:\LyncSQLLog\QoEMetrics.LDF’,
  
  NOUNLOAD, REPLACE, STATS = 10
  
  GO
Enable database chaining on the Rtc and Rtcdyn databases.
1. Run the following
  
  GO
  
  sp_dboption ‘rtcdyn’, ‘db chaining’, TRUE
  
  GO
  
  sp_dboption ‘rtc’, ‘db chaining’, TRUE
Enable multiuser mode on all your databases

ALTER DATABASE cpsdyn SET multi_USER

ALTER DATABASE rgsconfig SET multi_USER

ALTER DATABASE rgsdyn SET multi_USER

ALTER DATABASE rtcab SET multi_USER

ALTER DATABASE rtcab1 SET multi_USER

ALTER DATABASE rtcdyn SET multi_USER

ALTER DATABASE rtc SET multi_USER

ALTER DATABASE LcsCDR SET multi_USER

ALTER DATABASE lis SET multi_USER

ALTER DATABASE QoEMetrics SET multi_USER
Restart your Lync services at one server at the time.
1. Stop-CSWindowsService
2. Start-CSWindowsService
Create a new CMS database on the new SQL server
1. Install-CSDatabase –CentralManagementDatabase –Clean –SqlServerFqdn NewSQL.Domain.com –SqlInstanceName Lync –UseDefaultSqlPaths
  1. Note that this command use the –clean switch. If this parameter is included, Install-CsDatabase will delete and reinstall databases as needed. If this parameter is not included, Install-CsDatabase will not overwrite any existing databases. You cannot use both Clean and Update in the same command.
  2. Ref: http://technet.microsoft.com/en-us/library/gg399044.aspx
Move the CMS to the new SQL server (run the following from the enterprise lync server)
1. Move-CSManagementServer
Make sure that the replication is done before proceeding.
1. Get-CSManagementStoreReplicationStatus
2. Ref: http://technet.microsoft.com/en-us/library/gg399052
Run local setup on all servers that previously hosted the CMS
1. This would now be the standard edition server
2. This will uninstall the Lync Server Master Replicator Agent and Lync Server File Transfer Agent service
Run local setup on the enterprise front end pool servers
1. This is to install the Lync Server Master Replicator Agent and Lync Server File Transfer Agent service
2. After they are installed you need to start them.
  1. Start-CSWindowsService fta
  2. Start-CSWindowsService master
Remove the SQL databases from the old SQL server
1. Run this on the OLD SQL server
2. Before you run it make sure to close the topology builder and all Lync management windows that you might have open on the servers.
3. DROP DATABASE cpsdyn, rgsconfig, rgsdyn, rtcab, rtcab1, rtcdyn, rtc, LcsCDR, lis, QoEMetrics;
That’s it.

Reference for this whole post is http://blogs.technet.com/b/nexthop/archive/2010/12/20/change-a-pool-database-instance-when-the-pool-hosts-the-central-management-server.aspx and you should read through it as well since it has some great detailed information that I left out due to laziness

Ultimate Communications

Microsoft Unified Communications by Lync MVP Tommy Clarke

Moving Lync databases including CMS and the monitoring databases to a new SQL server – #Lync