Repairing a Damaged SQL Azure Sync Group
As a follow-up to my TechNet Guide published previously on Configuring a Windows Azure SQL Sync Group, in this post, I will explain how to repair a broken sync group.
Firstly, let me explain how I broke it. I have two SQL Azure databases in sync: one database in the North Europe region and another in the West Europe region. Following some security advice for WordPress, I decided I wanted to change the table prefixes for my database from the defaults of wp_. I made all of the required change to the config.php file, on the database tables and all the other changes required as per the article guide I used at http://wpcanada.ca/2009/how-to-change-wordpress-table-prefix/. After doing this, I couldn’t do anything to the blog: edit posts, author new posts, delete posts. I busted out SQL Server Management Studio to manually delete the post I was trying to clear out and I got an error message that there was a problem with the trigger.
I’m not a SQL expert so I hadn’t encountered triggers before but I could tell from the trigger name, wp_posts_dss_delete_trigger what this was. The trigger was based on the old wp_ table prefixes and now my prefixes are different. I logged into Windows Azure Management console and tried to update the database schema through the Sync Group configuration which succeeded but nothing was working still.
After much T-SQL research and experimentation, I got the fix.
Backup Everything
I can’t stress this point enough which is why it is in red, a colour which I normally try to avoid at all costs.
If you chose to follow this post to help recover a broken SQL Azure Sync Group and a potentially broken application trying to use that database, please make sure you back everything up. The changes we will make to the database in the following steps have the potential to ruin your day if done improperly and I can’t be responsible for any data loss as a result.
The easiest way to backup a SQL Azure database is from the Windows Azure Management portal which allows you to generate a .bacpac file which is an export of the entire database schema and table contents. This .bacpac file will be written to a Windows Azure Storage BLOB Container of your choice.
Clear and Delete the Sync Group
Firstly, you need to clear out the Sync Group. Full instructions for this are on Windows Azure MSDN at http://msdn.microsoft.com/en-us/library/windowsazure/jj991914.aspx but in a nutshell, remove any reference databases from the Sync Group first, leaving only the Hub database. Once this is done, delete the Sync Group, leaving you with standalone, non-communicative databases.
Next, login to your SQL Azure database using either the Windows Azure SQL Management console or via SQL Server Management Studio from your own machine. You should be connecting to the database which was formerly the Hub database as this is our primary and the one which needs rescuing. We’ll deal with the Reference databases at the end of the process.
Drop the Sync Group Database Tables
View the tables in the database and you should see a number of tables which have the prefix DataSync instead of the usual dbo. There are four tables as standard for all databases which have the following names:
- DataSync.provision_marker_dss
- DataSync.schema_info_dss
- DataSync.scope_config_dss
- DataSync.scope_info_dss
There will also be other DataSync tables which match you existing pre-change table names. Drop all of the DataSync tables. The T-SQL query for this is as follows:
DROP TABLE DataSync.provision_marker_dss DROP TABLE DataSync.schema_info_dss DROP TABLE DataSync.scope_config_dss DROP TABLE DataSync.scope_info_dss
Repeat this process for your custom name DataSync tables. In my case these were WordPress table names such as DataSync.wp_posts_dss_tracking.
Drop the Sync Group Triggers
This was the hardest part for me to find information on and complete. With all of the tables dropped, we’re halfway there but the triggers are the actual problem not the tables. Each table has three associated triggers for INSERT, UPDATE and DELETE actions. The triggers are what tells the Sync Group that the Hub database has a change that needs to be replicated to other copies of the database.
First, we need to find all of your triggers. The following code is courtesy of Joe Stefanelli on Stack Overflow athttp://stackoverflow.com/questions/4305691/need-to-list-all-triggers-in-sql-server-database-with-table-name-and-tables-sch.
SELECT sysobjects.name AS trigger_name
,USER_NAME(sysobjects.uid) AS trigger_owner
,s.name AS table_schema
,OBJECT_NAME(parent_obj) AS table_name
,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
This will output a query result with a list of all of the triggers on the database, the trigger name and the table for which it is registered. Right click the trigger_name header in the query result and select the Copy option. Open a New Query window and paste the output into the query window which now gives you a list of all of the triggers. On the first line, prepend the syntax DROP TRIGGER to the line then copy the DROP TRIGGER syntax down onto the start of each line.
For me, this gave me the following query ready to execute (for WordPress remember):
DROP TRIGGER wp_term_taxonomy_dss_insert_trigger DROP TRIGGER wp_term_taxonomy_dss_update_trigger DROP TRIGGER wp_term_taxonomy_dss_delete_trigger DROP TRIGGER wp_commentmeta_dss_insert_trigger DROP TRIGGER wp_commentmeta_dss_update_trigger DROP TRIGGER wp_commentmeta_dss_delete_trigger DROP TRIGGER wp_terms_dss_insert_trigger DROP TRIGGER wp_terms_dss_update_trigger DROP TRIGGER wp_terms_dss_delete_trigger DROP TRIGGER wp_comments_dss_insert_trigger DROP TRIGGER wp_comments_dss_update_trigger DROP TRIGGER wp_comments_dss_delete_trigger DROP TRIGGER wp_usermeta_dss_insert_trigger DROP TRIGGER wp_usermeta_dss_update_trigger DROP TRIGGER wp_usermeta_dss_delete_trigger DROP TRIGGER wp_links_dss_insert_trigger DROP TRIGGER wp_links_dss_update_trigger DROP TRIGGER wp_links_dss_delete_trigger DROP TRIGGER wp_users_dss_insert_trigger DROP TRIGGER wp_users_dss_update_trigger DROP TRIGGER wp_users_dss_delete_trigger DROP TRIGGER wp_options_dss_insert_trigger DROP TRIGGER wp_options_dss_update_trigger DROP TRIGGER wp_options_dss_delete_trigger DROP TRIGGER wp_postmeta_dss_insert_trigger DROP TRIGGER wp_postmeta_dss_update_trigger DROP TRIGGER wp_postmeta_dss_delete_trigger DROP TRIGGER wp_posts_dss_insert_trigger DROP TRIGGER wp_posts_dss_update_trigger DROP TRIGGER wp_posts_dss_delete_trigger DROP TRIGGER wp_term_relationships_dss_insert_trigger DROP TRIGGER wp_term_relationships_dss_update_trigger DROP TRIGGER wp_term_relationships_dss_delete_trigger
Ensuring that your query is pointed at the correct database if you are using the SQL Server Management Studio, now press the Execute button to execute the query. You should get the response Command(s) completed successfully.
Testing the Database
With this done, the triggers are now history which should bring your database back to life. Either use your application which drives the database or if you are comfortable doing so, try some INSERT or UPDATE queries against the database to verify this. If you get any errors it means you either missed a table or a trigger during the drop phases. If your database works then congratulations, your application is no longer a brick but we now need to restore the Sync Group functionality.
Delete the Reference Database(s) (Optional)
This step is optional but I did it for cleanliness. As we’ve basically just completely doctored with the schema and operation of a database, I didn’t like the idea that my Reference replica of the database was in an even worse state. I elected to delete the Reference database and create a new database in it’s place. You can either delete the database from the Windows Azure Management portal or from SQL Server Management Studio while connected to the SQL Server.
Please make sure you delete the Reference Database and not the Hub database if you do this though as if you delete the Hub by accident then you’ve just deleted the database which we spent the time fixing up.
Recreate the Sync Group
Recreating the Sync Group to get you back in business for resilient SQL Azure databases services means following the steps in my previous post Configuring SQL Azure Sync Groups. The only thing which you need to account for is the fact that your SQL Servers already exist and your Hub database already exists. Assuming you performed the optional step above to delete the Reference databases, you’ll need to do the following:
- Create new Reference databases.
- Create a new Sync Group.
- Configure the Hub and Reference partnership for the Sync Group.
- Scan the database schema and select the options to Sync.
- Enable automatic time based sync (if you are using this mode).
The previous post has the full details and screenshots should you need it for referral.
Hopefully you’re all back in business now and this post has helped you get out of a hole. The lesson I learnt here is that if you are making major changes to a database which is configured in a SQL Azure Sync Group then consider removing the databases from the Sync Group and deleting the Sync Group first then, once all your changes to the schema and table names are complete, then you can re-create the Sync Group and get your resiliency back on.
