Uncategorized

Setting up SQL Server Replication

Posted on by derreckdean in Uncategorized

This is how to do it using the replication UI:

  1. Be sure that you are connected to the servers in SSMS on the server that is publishing the databases as the configured SA account, not a different login.
  2. You will need to have replication users set up – a Windows user as well as a SQL server user set up.
    • If this is a domain, only one domain user is required for replication. I use \ServiceRunner for this task. This user must have db_owner privileges on each database being replicated as well as db_owner on the system database called ‘distribution’ found on the master server. That database is created once initial setup of replication is done. If this is not on a domain, create the same user across both machines with the same password.
    • The SQL user – called 4LLrepl – must have the same permissions set up for the windows user above. This must be set up on both servers, the publisher and subscriber.
  3. If replication has never been set up on the server with the databases to be replicated, use the Configure Distribution Wizard to set this up by right clicking the Replication node and selecting that option.
    1. will act as its own distributor.
    2. The next screen asks for a path to set up replication – it has the local path to the replication root (e.g. C:\SQL\MSSQL13.MSSQLSERVER\MSSQL\ReplData), but it recommends a UNC fileshare path.
      1. Copy the path and open up Windows Explorer. Paste the path into the path bar.
      2. Go back a folder, right click the repldata folder, and select Sharing.
  • Share the folder allowing the Windows replication user read/write permissions.
  1. When done, copy the resulting path, \\\repldata
  2. Back in the wizard, paste the UNC path in and click Next.
  1. Next screen is distribution database details. Leave the defaults and click Next.
  2. Next screen is publisher defaults. Hit next.
  1. If not there, add the windows and sql replication users to SQL security.
    • If there are any unsafe assemblies in these databases, sysadmin server role is needed for both accounts.
    • The accounts must be db_owner for each database being replicated, as well as the system database called ‘distribution’.
    • The SQL user 4LLrepl also needs to exist on any subscriber servers as well.
    • If not using a domain account for ServiceRunner, the Windows account (ServiceRunner) would need to exist on each machine with the same password.
  2. The publisher’s firewall would need to be adjusted to allow the subscriber to connect.
  3. The subscriber’s firewall would need to be adjusted to allow the publisher to connect.
  4. Set up a publication for each database.
    • Right click the Local Publications node under the Replication node in the treeview for the publisher server and click New Publication.
    • Select the database to replicate.
    • Transactional publication.
    • Select everything to be included. NOTE – this does not include database synonyms which will have to be scripted to the target databases manually.
    • Next screen warns about things that could go wrong. If you see ‘Objects referenced by stored procedures are required’, most likely they are synonyms.
    • Next screen sets up filters for data.
    • Snapshot agent: Create a snapshot immediately should be checked. No need to check schedule agent.
    • Next screen is security. You will have to provide both the Windows and SQL replication accounts discussed earlier.
    • Wizard actions: Create the publication.
    • Name the publication the same as the DB.
    • Hit finish. The publication will be created. Launch Replication Monitor to ensure that the replication has started successfully.
  5. Add subscribers to each publication.
    • Publication: Select the publication to subscribe to.
    • Distribution Agent Location: Run agents at the distributor.
    • Subscribers: Add subscriber. It’s okay to connect to the DR as a windows user (e.g. you don’t have to be SA right now).
      1. Once you connect to DR using the standard connection dialog, an entry in the rows will be added. Select an existing database to put this data in or create a new one using the options found in the dropdown under Subscription Database.
      2. If creating a new database, be sure to set the owner of the database to the configured SA account (SA or 4LLSA), and select Simple Logging.
    • Distribution Agent Security: Click the ellipsis button to enter the Windows and SQL usernames and passwords.
    • Synchronization Schedule: Run continuously
    • Initialize Subscriptions: Run immediately
    • Wizard Actions: Create the subscription
  6. Add any missing synonyms to the target database.
  7. Grant permission for replication services to write to a specific folder for MSSQL. Without setting this, there will be a bunch of errors with an error message similar to “could not write temp files to a folder. System returned errorcode 5” (sic).
    • Grant everyone modify permissions to the folder C:\Program Files\Microsoft SQL Server\130\COM (130=SQL 2016, 120=SQL 2014, 110=SQL 2012, etc)
  8. Set the max replicated text size on both servers. -1 means there is no limit.
  • sp_configure ‘max text repl size’, -1; Go; RECONFIGURE; GO

Troubleshooting

  • Be sure the owner of all jobs and databases are the configured SA account.
  • Be sure each publication’s Publication Access List includes both the Windows and SQL replication accounts.
  • Be sure all replicated tables have a PK defined.

So, instead of using the UI to get the pub/sub set up, add this to any database and use it:

/****** Object: StoredProcedure [dbo].[Replication_SetupSingleDatabase] Script Date: 12/15/2017 10:37:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Replication_SetupSingleDatabase]
(
-- Required Input
@database NVARCHAR(MAX), -- Name of database, e.g. DKDWhatever, DKD_Custom_DKD
@action NVARCHAR(8), -- either 'setup' or 'teardown', depending on what you want to do
@execute BIT, -- 0 = add the commands to the publisher commands; 1 = execute those commands instead
@publishServer NVARCHAR(MAX), -- The HOSTNAME of the server that is the publisher. IP addresses don't work. Example: DKD-SQL1
@subscribingServer NVARCHAR(MAX), -- The HOSTNAME of the server that is the subscriber. IP addresses don't work. Example: DKD-DR1
@publisherReplicationServiceAccountUsername NVARCHAR(MAX), -- Windows account. Use ServiceRunner if following default platform configurations.
@publisherReplicationServiceAccountPassword NVARCHAR(MAX), -- Windows account password. Maybe you should execute this locally or encrypted connection.
@publisherReplicationSQLAccountUsername NVARCHAR(MAX), -- SQL account.
@publisherReplicationSQLAccountPassword NVARCHAR(MAX), -- SQL account password.
@subscriberReplicationServiceAccountUsername NVARCHAR(MAX), -- Windows account. If on a domain, same account as above. If not, copy the first account user/pass for this one.
@subscriberReplicationServiceAccountPassword NVARCHAR(MAX), -- Windows account password, same as first one above if not the same account.
@subscriberReplicationSQLAccountUsername NVARCHAR(MAX), -- SQL account. Same username/password as the first SQL account.
@subscriberReplicationSQLAccountPassword NVARCHAR(MAX), -- SQL account password, same as the other one.

— Output
@publisherCommands NVARCHAR(MAX) OUTPUT,
@subscriberCommands NVARCHAR(MAX) OUTPUT,

— Optional commands
@publicationName NVARCHAR(MAX) = NULL — Name of the publication. If NULL, uses _Publish
)
AS BEGIN
/*
PURPOSE
Creates or runs the scripts needed to set up replication between 2 servers, or destroy it.
Part of the script needs to be run in the context of the subscriber server to complete setup.
Made in response to how long it takes through the UI – no way we can keep wasting 15-60 minutes each setup.

If you’re running this for the first time – READ THIS FIRST. This script makes a lot of assumptions.
– Have you even set up replication to begin with, bro?!
– The publisher’s AND subscriber Windows AND SQL replication accounts have been created – both endpoints!
– If not on a domain, use 2 users, same account username and password.
– If on a domain, use a domain account.
– Use the same password for both SQL replication accounts.
– This account does not need login privileges, but may need ‘logon as batch job’ rights.
– The naming convention is \ServiceRunner for the service account and DKDrepl as the SQL account.
– If there are any UNSAFE assemblies in any databases, the SQL replication account needs sysadmin. THIS IS BAD, btw.
– During initial setup of replication, you’re using a UNC file path to store replication data (the repldata folder).
– The windows replication service account needs read/write permissions to this share.
– Databases on subscriber already exist, and have the same names as the source databases.
– The 2 aforementioned replication accounts have dbowner privileges on the source and destination databases.
– The 2 aforementioned replication accounts also have dbowner privileges on the distribution database (under system databases).
– The firewall allows a connection between the publisher and subscriber machines.
– The publisher and the subscriber must be able to communicate through a windows hostname. IP addresses are not allowed.
– Grant everyone modify permissions to the folder C:\Program Files\Microsoft SQL Server\130\COM (130=SQL 2016, 120=SQL 2014, 110=SQL 2012, etc)
– Run ‘sp_configure ‘max text repl size’, -1; Go; RECONFIGURE; GO’ on both servers to allow LOB data to be replicated.
– Be sure all databases are owned by the configured SA account.

What this script does:
– Sets up the publication as a transactional PULL publication. Cuz that’s how we use it at DKD. Additions are welcome.
– Loops through the tables, views, procedures and functions and adds each article to the publication.
– Adds the user accounts to the publication list.
– Starts the initialization snapshot agent.
– Writes out commands to run on the subscriber to finish setup.

Warnings:

What works for me may not work for you. You should add your own publication and subscription, and script it out.
Then compare the script to the script created by this procedure (with @execute = 0) to see if this will do what you want it to do.
Many of the things in here could be made into options.

Cool things to future me to do for a future version:
– Set up the configuration as extended properties on the database.

REVISION HISTORY
20171214 DKD
Created
*/

/* For testing.
— The publication name
DECLARE @publicationName NVARCHAR(MAX) = ‘MCSWalletDocuments_Publish’;

— The database to replicate
DECLARE @database NVARCHAR(MAX) = ‘MCSWalletDocuments’;

— The action – setup or teardown replication
DECLARE @action NVARCHAR(MAX) = ‘teardown’;

— The server that is publishing
DECLARE @publishServer VARCHAR(MAX) = ‘DKD-SQL1’;

— The server that is subscribing
DECLARE @subscribingServer VARCHAR(MAX) = ‘DKD-DR1’;

— Should we just print out what would be run rather than running it?
DECLARE @execute BIT = 0;

— Publish-side Windows replication account
DECLARE @publisherReplicationServiceAccountUsername NVARCHAR(MAX) = ‘DKD\servicerunner’;
DECLARE @publisherReplicationServiceAccountPassword NVARCHAR(MAX) = ‘yamomma’;

— Publish-side SQL replication account
DECLARE @publisherReplicationSQLAccountUsername NVARCHAR(MAX) = ‘DKDrepl’;
DECLARE @publisherReplicationSQLAccountPassword NVARCHAR(MAX) = ‘yamomma’;

— Subcriber-side Windows replication account
DECLARE @subscriberReplicationServiceAccountUsername NVARCHAR(MAX) = ‘DKD\servicerunner’;
DECLARE @subscriberReplicationServiceAccountPassword NVARCHAR(MAX) = ‘yamomma’;

— Subscriber-side SQL replication account
DECLARE @subscriberReplicationSQLAccountUsername NVARCHAR(MAX) = ‘DKDrepl’;
DECLARE @subscriberReplicationSQLAccountPassword NVARCHAR(MAX) = ‘yamomma’;

–*/

— DO WORK

SET NOCOUNT ON;
DECLARE @tmp NVARCHAR(MAX);
DECLARE @verdesc VARCHAR(MAX) = ‘DKD Replication Setup v0.0.0.1’;
DECLARE @outputCommands NVARCHAR(MAX) = ”;
SET @publisherCommands = ”;
SET @subscriberCommands = ”;

— Set Defaults
SET @publicationName = COALESCE(@publicationName, @database + ‘_Publish’);

— We need a synonym to be able to read the database schema. Without this, we would only be able to
— read the current database; this means you would have to switch to the DB you want to work with first.
— So this makes things a little more flexible. Just be sure this procedure ends, that’s where the cleanup happens. 😉
SET @tmp = ‘
USE tempDB
CREATE SYNONYM dbo.ReplSetupTmp_IS_T FOR [‘ + @database + ‘].INFORMATION_SCHEMA.TABLES;
CREATE SYNONYM dbo.ReplSetupTmp_IS_V FOR [‘ + @database + ‘].INFORMATION_SCHEMA.VIEWS;
CREATE SYNONYM dbo.ReplSetupTmp_IS_S FOR [‘ + @database + ‘].INFORMATION_SCHEMA.ROUTINES;
‘;
EXEC master..sp_executesql @tmp;

— Enumerate all articles that would be replicated
PRINT ‘Enumerating objects…’;
DECLARE @objs TABLE (ID INT PRIMARY KEY IDENTITY(1,1), T CHAR(1), OBJ NVARCHAR(MAX), SCH NVARCHAR(MAX));
–SELECT * FROM INFORMATION_SCHEMA.
WITH ctePrep (Obj, Val, Sch) AS
(
SELECT ‘T’ AS Obj, TABLE_NAME AS Val, TABLE_SCHEMA FROM TempDB.dbo.[ReplSetupTmp_IS_T] WHERE TABLE_TYPE = ‘BASE TABLE’
UNION ALL SELECT ‘V’, TABLE_NAME, TABLE_SCHEMA FROM TempDB.dbo.[ReplSetupTmp_IS_V]
UNION ALL SELECT CASE WHEN ROUTINE_TYPE = ‘PROCEDURE’ THEN ‘S’ ELSE ‘F’ END, ROUTINE_NAME, ROUTINE_SCHEMA FROM tempdb.dbo.[ReplSetupTmp_IS_S] WHERE SUBSTRING(ROUTINE_NAME, 1, 1) != ‘_’
)
INSERT INTO @objs
SELECT x.obj, x.Val, x.Sch FROM ctePrep x
WHERE
— ugh
x.val NOT LIKE ‘MSsubscription%’
AND x.val NOT LIKE ‘MSreplication%’
AND x.val NOT LIKE ‘sp_%diagram%’
AND x.val NOT LIKE ‘syncobj_%’
AND x.val NOT LIKE ‘MSpub%’
AND x.val NOT LIKE ‘MSpeer%’
AND x.val NOT IN (‘sysarticlecolumns’,’syspublications’, ‘syssubscriptions’, ‘sysarticleupdates’, ‘systranschemas’, ‘sysreplservers’, ‘sysarticles’, ‘sysschemaarticles’, ‘sysextendedarticlesview’)

DECLARE @count_t INT;
DECLARE @count_v INT;
DECLARE @count_s INT;
DECLARE @count_f INT;
SELECT
@count_t = (SELECT COUNT(*) FROM @objs WHERE T = ‘T’)
,@count_v = (SELECT COUNT(*) FROM @objs WHERE T = ‘V’)
,@count_s = (SELECT COUNT(*) FROM @objs WHERE T = ‘S’)
,@count_f = (SELECT COUNT(*) FROM @objs WHERE T = ‘F’)
;

PRINT CAST(@count_s + @count_t + @count_v + @count_f AS VARCHAR(MAX)) + ‘ total objects enumerated.’;
PRINT ‘ – ‘ + CAST(@count_t AS VARCHAR(MAX)) + ‘ user tables’;
PRINT ‘ – ‘ + CAST(@count_v AS VARCHAR(MAX)) + ‘ views’;
PRINT ‘ – ‘ + CAST(@count_s AS VARCHAR(MAX)) + ‘ user stored procedures’;
PRINT ‘ – ‘ + CAST(@count_f AS VARCHAR(MAX)) + ‘ user functions’;
PRINT ”;

DECLARE @startQuery VARCHAR(MAX) = ‘USE [‘ + @database + ‘];’ + CHAR(13) + CHAR(10) + ‘GO’ + CHAR(13) + CHAR(10);
DECLARE @endQuery VARCHAR(MAX) = CHAR(13) + CHAR(10) + ‘GO’;
DECLARE @type CHAR, @obj NVARCHAR(MAX), @sch NVARCHAR(MAX), @c INT = 0;

IF (@action = ‘setup’) BEGIN
PRINT ‘Beginning setup of replication.’;

PRINT ‘Setting up publish server.’;

— This enables replication
SET @tmp = @startQuery + ‘ EXEC sp_replicationdboption @dbname = ”’ + @database + ”’, @optname = N”publish”, @value = N”true”’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Replication DB option set to true.’;

— Add logreader agent
SET @tmp = @startQuery + ‘EXEC [‘ + @database + ‘].sys.sp_addlogreader_agent @job_login = N”’ + @publisherReplicationServiceAccountUsername + ”’, @job_password = ”’ + @publisherReplicationServiceAccountPassword + ”’, @publisher_security_mode = 0, @publisher_login = N”’ + @publisherReplicationSQLAccountUsername + ”’, @publisher_password = N”’ + @publisherReplicationSQLAccountPassword + ”’, @job_name = null’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Added log reader agent.’;

— Add publication
SET @tmp = @startQuery + ‘EXEC sp_addpublication @publication = ”’ + @publicationName + ”’, @description = N”Transactional publication of database ‘ + @database + ‘ from Publisher ‘ + @verdesc + ‘; generated by ‘ + @verdesc + ”’, @sync_method = N”concurrent”, @retention = 0, @allow_push = N”true”, @allow_pull = N”true”, @allow_anonymous = N”true”, @enabled_for_internet = N”false”, @snapshot_in_defaultfolder = N”true”, @compress_snapshot = N”false”, @ftp_port = 21, @ftp_login = N”anonymous”, @allow_subscription_copy = N”false”, @add_to_active_directory = N”false”, @repl_freq = N”continuous”, @status = N”active”, @independent_agent = N”true”, @immediate_sync = N”true”, @allow_sync_tran = N”false”, @autogen_sync_procs = N”false”, @allow_queued_tran = N”false”, @allow_dts = N”false”, @replicate_ddl = 1, @allow_initialize_from_backup = N”false”, @enabled_for_p2p = N”false”, @enabled_for_het_sub = N”false”’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Added publication.’;

— Add publication snapshot
SET @tmp = @startQuery + ‘EXEC sp_addpublication_snapshot @publication = N”’ + @publicationName + ”’, @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N”’ + @publisherReplicationServiceAccountUsername + ”’, @job_password = N”’ + @publisherReplicationServiceAccountPassword + ”’, @publisher_security_mode = 0, @publisher_login = N”’ + @publisherReplicationSQLAccountUsername + ”’, @publisher_password = N”’ + @publisherReplicationSQLAccountPassword + ”’;’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Added publication snapshot.’;

DECLARE c CURSOR FOR SELECT x.T, x.OBJ, x.SCH FROM @objs x;
OPEN c;
FETCH NEXT FROM c INTO @type, @obj, @sch;
WHILE (@@FETCH_STATUS = 0) BEGIN

PRINT ‘ – Adding type ‘ + @type + ‘ named ‘ + @obj + ‘ …’;

IF (@type = ‘T’) BEGIN

SET @tmp = @startQuery + ‘EXEC sp_addarticle @publication = N”’ + @publicationName + ”’, @article = N”’ + @obj + ”’, @source_owner = N”dbo”, @source_object = N”’ + @obj + ”’, @type = N”logbased”, @description = null, @creation_script = null, @pre_creation_cmd = N”drop”, @schema_option = 0x000000000803509F, @identityrangemanagementoption = N”manual”, @destination_table = N”’ + @obj + ”’, @destination_owner = N”dbo”, @vertical_partition = N”false”, @ins_cmd = N”CALL sp_MSins_dbo’ + @obj + ”’, @del_cmd = N”CALL sp_MSdel_dbo’ + @obj + ”’, @upd_cmd = N”SCALL sp_MSupd_dbo’ + @obj + ”’, @filter_clause = NULL’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘ – Added article.’;

— Adding the article filter
–SET @tmp = @startQuery + ‘EXEC sp_articlefilter @publication = N”@publicationName”, @article = N”ConfigurationSettings”, @filter_name = N”FLTR_ConfigurationSettings_1__68”, @filter_clause = N”1 = 2”, @force_invalidate_snapshot = 1, @force_reinit_subscription = 1’ + @endQuery;
–IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
–PRINT ‘ – Added article filter.’;

— Adding the article synchronization object
–SET @tmp = @startQuery + ‘EXEC sp_articleview @publication = N”’ + @publicationName + ”’, @article = N”’ + @obj + ”’, @view_name = N”SYNC_’ + @type + ‘_’ + @obj + ”’, @filter_clause = NULL, @force_invalidate_snapshot = 1, @force_reinit_subscription = 1′ + @endQuery;
–IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
–PRINT ‘ – Added article sync object.’;

END ELSE BEGIN

SET @tmp = @startQuery + ‘EXEC sp_addarticle @publication = N”’ + @publicationName + ”’, @article = N”’ + @obj + ”’, @source_owner = N”dbo”, @source_object = N”’ + @obj + ”’, @type = N”’ + CASE @type WHEN ‘V’ THEN ‘View’ WHEN ‘S’ THEN ‘proc’ WHEN ‘F’ THEN ‘func’ ELSE ‘unknown’ END + ‘ schema only”, @description = null, @creation_script = null, @pre_creation_cmd = N”drop”, @schema_option = 0x0000000008000001, @destination_table = N”’ + @obj + ”’, @destination_owner = N”dbo”;’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘ – Added article.’;

END;

SET @c = @c + 1;
FETCH NEXT FROM c INTO @type, @obj, @sch;
END;

PRINT ‘Processed ‘ + CAST(@c AS VARCHAR(MAX)) + ‘ articles in total.’;

CLOSE c;
DEALLOCATE c;

— Set up subscriber on publisher
SET @tmp = @startQuery + ‘EXEC sp_addsubscription @publication = N”’ + @publicationName + ”’, @subscriber = N”’ + @subscribingServer + ”’, @destination_db = N”’ + @database + ”’, @sync_type = N”Automatic”, @subscription_type = N”pull”, @update_mode = N”read only”’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Added subscription to publisher. Additional statements need to run on subscriber to complete setup.’;

— Add windows users to publication
SET @tmp = @startQuery + ‘EXEC sys.sp_grant_publication_access @publication = N”’ + @publicationName + ”’, @login = N”’ + @publisherReplicationServiceAccountUsername + ”’;’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Added ‘ + @publisherReplicationServiceAccountUsername + ‘ to publication access list.’;

— Add sql users to publication
SET @tmp = @startQuery + ‘EXEC sys.sp_grant_publication_access @publication = N”’ + @publicationName + ”’, @login = N”’ + @publisherReplicationSQLAccountUsername + ”’;’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Added ‘ + @publisherReplicationSQLAccountUsername + ‘ to publication access list.’;

— Get the job to start the snapshot
SET @tmp = @startQuery + ‘EXEC sp_startpublication_snapshot @publication=”’ + @publicationName + ”’;’;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Started datbase publication.’;

— Add synonyms
DECLARE @synonyms VARCHAR(MAX) = ”;
WITH cteRaw AS
(
SELECT TOP 123456789
name,
COALESCE(PARSENAME(base_object_name,4),@@servername) AS serverName,
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName,
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
PARSENAME(base_object_name,1) AS objectName
FROM sys.synonyms
ORDER BY serverName, dbName, schemaName, objectName
)
SELECT @synonyms = @synonyms + CHAR(13) + CHAR(10) + ‘IF OBJECT_ID(”’ + x.schemaName + ‘.’ + x.objectName + ”’) IS NULL ‘
+ ‘CREATE SYNONYM ‘ + x.objectName + ‘ FOR [‘ + dbName + ‘].[‘ + x.schemaName + ‘].[‘ + x.objectName + ‘];’
FROM cteRaw x;
SELECT @tmp = @startQuery + @synonyms + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Created any missing synonyms.’;

— Done – print out statements to run on publisher
PRINT ”;
PRINT ‘Row data contains statements to run on ‘ + @subscribingServer + ‘ to complete the subscriber setup.’;

SET @subscriberCommands = ”
+ ‘– Run the following statements on the subscriber to complete the subscription process.’
+ ‘USE [‘ + @database + ‘]’
+ ‘GO’
+ ‘EXEC sp_addpullsubscription @publisher = N”’ + @publishServer + ”’, @publication = N”’ + @publicationName + ”’, @publisher_db = N”’ + @database + ”’, @independent_agent = N”True”, @subscription_type = N”pull”, @description = N””, @update_mode = N”read only”, @immediate_sync = 1′
+ ‘GO’
+ ‘EXEC sp_addpullsubscription_agent @publisher = N”’ + @publishServer + ”’, @publisher_db = N”’ + @database + ”’, @publication = N”’ + @publicationName + ”’, @distributor = N”’ + @publishServer + ”’, @distributor_security_mode = 0, @distributor_login = N”’ + @subscriberReplicationSQLAccountUsername + ”’, @distributor_password = N”’ + @subscriberReplicationSQLAccountPassword + ”’, @enabled_for_syncmgr = N”False”, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20000101, @active_end_date = 99991231, @alt_snapshot_folder = N””, @working_directory = N””, @use_ftp = N”False”, @job_login = N”’ + @subscriberReplicationServiceAccountUsername + ”’, @job_password = N”’ + @subscriberReplicationServiceAccountPassword + ”’, @publication_type = 0′
+ ‘GO’

END ELSE IF (@action = ‘teardown’) BEGIN
PRINT ‘Beginning removal of replication.’;

— Dropping the transactional subscriptions
SET @tmp = @startQuery + ‘exec sp_dropsubscription @publication = N”’ + @publicationName + ”’, @subscriber = N”’ + @subscribingServer + ”’, @destination_db = N”’ + @database + ”’, @article = N”all”’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Dropped subscription.’;

DECLARE c CURSOR FOR SELECT x.T, x.OBJ, x.SCH FROM @objs x;
OPEN c;
FETCH NEXT FROM c INTO @type, @obj, @sch;
WHILE (@@FETCH_STATUS = 0) BEGIN

— Drop subscription
SET @tmp = @startQuery + ‘exec sp_dropsubscription @publication = N”’ + @publicationName + ”’, @article = N”’ + @obj + ”’, @subscriber = N”all”, @destination_db = N”all”’ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘ – Dropped subscription ‘ + @obj + ‘.’;

— Drop article
SET @tmp = @startQuery + ‘exec sp_droparticle @publication = N”’ + @publicationName + ”’, @article = N”’ + @obj + ”’, @force_invalidate_snapshot = 1′ + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘ – Dropped published article ‘ + @obj + ‘.’;

SET @c = @c + 1;
FETCH NEXT FROM c INTO @type, @obj, @sch;
END;

PRINT ‘Processed ‘ + CAST(@c AS VARCHAR(MAX)) + ‘ articles in total.’;

CLOSE c;
DEALLOCATE c;

— Dropping the transactional publication
SET @tmp = @startQuery + ‘exec sp_droppublication @publication = N”’ + @publicationName + ”” + @endQuery;
IF (@execute = 0) SET @outputCommands = @outputCommands + @tmp + CHAR(13) + CHAR(10); ELSE EXEC master..sp_executesql @tmp;
PRINT ‘Dropped subscription.’;

PRINT ”;
PRINT ‘Row data contains statements to run on ‘ + @subscribingServer + ‘ to complete the subscriber setup.’;

END;

IF (@outputCommands != ”) BEGIN
PRINT ”;
PRINT ‘SQL to run on PUBLISHER: ‘;
PRINT ”;
PRINT @outputCommands;
SET @publisherCommands = @outputCommands;
END;

— Cleanup

EXEC master..sp_executesql N’USE tempdb; DROP SYNONYM dbo.ReplSetupTmp_IS_T; DROP SYNONYM dbo.ReplSetupTmp_IS_V; DROP SYNONYM dbo.ReplSetupTmp_IS_S’;

SET NOCOUNT OFF

END

Advertisement

Leave a Reply

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

Gravatar
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 )

Cancel

Connecting to %s