Manual work around of the bug for DTE encryption on SQl servers When Mirroring Skype for Business

 

Now this is a Skype post, but it largely revolves around the failure of the Skype Topology Builder to be able to mirror that Database of the SQL back end, in Skype for Business.

The setup is you have a SQL back end, A SQL Mirror server, and a Witness server. Below is a work around for the Mirror Failing to create, when DTE encryption is enabled on the SQL server:

 

  • Run SQL management studio
  • Open a connection to all 3 servers
  • Open new query
  • On primary server setup a backup directory to backup the Databases to
  • On the Mirror setup a backup directory to copy backup files from primary to mirror server
  • Perform a table backup on the primary server to the backup directory
  • See Figure 1 and 2 Below

figure 1. sql2

Figure 2

Untitled

 

Once you have the Back up completed, open a file explorer on the mirror and copy the backup file from the primary to the mirror server drive

Execute the following commands from the SQL Studio manager for each backed up table to restore it to the Mirror server

****Executed on Mirror server

.use xds

go

restore database [xds] from disk=’d:\backups\xdstlog’ with norecovery ALTER DATABASE xds SET PARTNER = ‘TCP://cio-socc-ucsql1.osuad.osu.edu: yourport’

Execute the following commands from the SQL Studio manager on the Primary server for each backed up table to establish mirroring

****Executed on Primary after Mirrored server commands

ALTER DATABASE xds SET PARTNER = ‘TCP://cio-socc-ucsql2.osuad.osu.edu:2434’

Finish setup of Mirror by adding witness to the mirrored pairs

Goto properties on the Primary Table

sql3

Go TO Mirroring

sql4

Click Configure security to add the Witness to the mirror and then provide the Service account information and click Finish to establish Mirroring with a Witness

sql5

 

Microsoft report of the TDE encryption being a problem with the SQL 2008 Manager and appears to still be a problem with SQL 2014

https://blogs.msdn.microsoft.com/sqlserverfaq/2009/03/31/setting-up-database-mirroring-in-sql-server-2008-using-t-sql-when-the-database-is-encrypted-using-transparent-data-encryption/

If you have problems with the Database not being able to be configured for restore with the SQL commands check the following properties on the problem tables

On the Primary go to properties

 

sql6

Go to Options:

 

sql7

 

And check change the databases from Full to Simple click OK and then change them back to FULL

This should then allow you to restore the databases correctly and setup Mirroring

See this article for the normal script to restore the database. you may just use the install-csdatabase commands by this point. the scripts are also included in this article:

Skype for Business will now Re-Mirror

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s