Bookend your SQL database deployments with custom SQL scripts

ApexSQL Diff Professional can take the advantage of using custom SQL scripts before and after the SQL database synchronization process, which further increases the application’s flexibility by reducing the need for the user to manually perform the required preparation and final actions necessary for successful synchronization.

There are numerous cases when DBAs need this functionality, especially when maintaining multiple SQL databases

One such case would be maintaining one master SQL database and 100 slave databases to keep the identical structure as in the master database. You cannot create a single SQL database synchronization script for all of them because you cannot be sure that all slave databases have the same structure; so, you need to synchronize each slave SQL database separately. However, in order to make sure the synchronization is successful, you will need to “kick out” all of the users attached to those slave databases, and then, after the completed synchronization, bring the SQL databases back to their regular state

Therefore, you will need a pre-synchronization SQL script:

ALTER DATABASE <DatabaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

And a post-synchronization script:

ALTER DATABASE <DatabaseName> SET MULTI_USER 
GO

Alternatively, you might want to synchronize a master SQL database that contains tables from the “Product” schema, with multiple databases; but not all of these contain the SQL schema “Product”. In that case, you will need the pre-synchronization SQL script that will check if a “Product” schema exists, and if not, create it; otherwise, the database synchronization will fail

Here is an example of such a pre-synchronization SQL script:

IF NOT EXISTS( SELECT schema_name 
                 FROM information_schema.schemata 
                 WHERE schema_name 
                       = 
                       'Product' 
             ) 
    BEGIN EXEC sp_executesql N'CREATE SCHEMA Product';
    END;

If you need to add some different logins, windows logins or users to the destination SQL databases, which might exist in the source database, you can use a post-synchronization script to create them. Here are a few examples of such scripts:

To create a Windows Login:

CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS; 
GO

To create just a SQL Login:

CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>', 
    CREDENTIAL = <credentialName>; 
GO

To create a SQL User:

CREATE LOGIN <user_name> 
    WITH PASSWORD = '<enterStrongPasswordHere>'; 
USE <database_name>; 
GO 
CREATE USER <user_name> FOR LOGIN <user_name>;
GO

The Additional scripts tab of the Project dialog allows defining whether to add additional scripts to be executed before the SQL database synchronization process (Preprocessing script), or after it (Postprocessing script)

The options are as follows:

• Select Don’t use – not to use an additional SQL script
• Click Use embedded script to add and save the script for a project. Click Edit to open the Script editor and create a new one or make additional changes to the existing script
• Click Use this script to use a saved SQL script file

Using the pre/post-synchronization script reduces the burden on developers and DBAs. They do not have to manually perform pre and post-synchronization tasks; the advantage of using custom SQL scripts is quite evident