Introduction:

In a recent article, published on "SQL Server Standard" magazine, I've described the methods available for SSAS administrators to plan ahead and implement a Disaster Recovery plan for their OLAP databases. One of the new methods, introduced in SSAS 2005 is database synchronisation. While not being purely a recoverability feature, database synchronisation can assist in delivering a more robust environment by enabling the easy transfer of data and meta data from one server to another.

When to use database synchronisation?

The most obvious scenario where you would want to use database synchronisation will be when you need to implement changes made on a development/staging database onto a production instance. The synchronisation feature lets you move all the updated meta data and data from the development/staging instance to the production instance, without affecting the availability of the synchronised production database. Users, who are connected to the synchronised production database, will still be able to query the database while it's being synchronised. The SSAS engine will switch the users' connection context to the new data (and database) once synchronisation is complete. Then, it will drop the old out of date data. The ability to maintain users' connectivity is by far the most attractive feature of the synchronisation method.

Surely, you can use database synchronisation the other way around as well. Sometimes you desperately need a copy of the production database to perform some urgent development work or maybe just to query it without interrupting users. You can then synchronise the database from the production environment to the development environment and work on the synchronised database.

In terms of Disaster Recovery, having a "working" copy of the database in a remote site is essential. For this purpose, SSAS administrators can either use the backup method, scripting the database, or synchronising the databases between the production site and the DR site. You can essentially get the same benefits from either using the Backup method or synchronising the databases. Scripting the database provides you with tools to recreate the database, but you will have to process the entire database to get all the data back.

How to synchronise databases?

It is a fairly simple procedure to synchronise databases. Right click on the server node of the destination server and choose: "Synchronise"

You will then be prompted to choose the source server and source database you wish to synchronise the database from.

Next, you need to specify the location of the data partitions, if they are different from the source database location. Note that changing partitions location will only be available if the source partitions are not located in the default data folder. If all partitions are indeed located in the default data folder, you will only have the option of moving them to the default data folder in the destination server. This applies to each partition individually, i.e. you will be able to change the location of a partition that has been stored in a location different then the default data folder, while all the partitions that are in the default data folder will be copied to the destination server's default data folder. This is something you would want to take into consideration when creating new databases/partitions.

Security settings are next, and you have three options to choose from:

1.SkipMembership Choosing this option will copy all security information, but will omit the users/groups associated with each security setting. This is useful when you synchronise the database between a development environment and a production environment, as usually the users and groups are different in these two environments.

2.CopyAll This will copy all security information, including users and groups associated with the security settings. DR implementation may be the appropriate scenario to use this option, where you want to retain the users and groups from the production environment, in case production environment has to fail over to the DR site.

3.IgnoreSecurity No security settings from the source database will be copied to the destination database. You may want to choose this option if you are synchronising the database to a local development environment, where no security settings are required.

This is where you can also choose whether to apply compression while synchronising the database. This will reduce network traffic congestion, but will incur a performance hit on the operation as a whole.

You can also choose whether you want to perform the synchronisation now, or create an XMLA script which you can later use, perhaps in a SQL Agent job on a scheduled basis, maybe every night or every week. This is most beneficial when you need to maintain a DR site with an updated copy of the database.

And now, you can commence the synchronisation process, and once completed, you will be able to query the new data on the destination database.

Some more notes:

When synchronising, SSAS actually creates a backup of the source database/synchronised objects, restores them on the destination server and then synchronises the relevant objects. While synchronising databases, you can not perform any operations which will change the database objects on the source database. On the destination server, as mentioned, the synchronised database is available for users and once synchronisation is complete, the old data is being replaced by new data and users immediately can view and query the new, synchronised data.

Conclusion:

Database synchronisation is an attractive new feature in SSAS 2005. It enables the administrator to move production databases to a DR site with a few clicks on a wizard, and it enables the developer to maintain an extremely manageable environment for developing and moving databases from a development platform to a production platform and vice versa.