Generate script to add multiple table in replication as article

Generate script to add multiple table in replication as article

I have already explained about “How to add article in replication from GUI” and “How to add article in replication from TSQL”. I am a script buddy and as long as possible I wouldn’t like to go for GUI options.

If there would be two or four objects only, we could create add article script manually but what about creating a script for many tables or objects?

Generally I used to select only one small table while setting up replication and once replication is ready with publisher, distributor and subscription, I add remaining objects (mainly tables) via script. So after setting up replication, I may need to add hundreds of table and creating add article script for all is little hectic. I have created one small script to overcome this.

[sourcecode language=”sql”]

SELECT

DISTINCT ‘DECLARE @tableName’ + Constraints.table_name+’ VARCHAR(255)’

+ ‘ DECLARE @SchemeName’ + Constraints.table_name+’ VARCHAR(255)’

+ ‘ SET @tableName’ + Constraints.table_name+’=’+ ”” + Constraints.table_name + ””

+ ‘ SET @SchemeName’ + Constraints.table_name+’=’+ ”” + Constraints.constraint_schema + ””

+ ‘ exec sp_addarticle’

+ ‘ @publication = N”AdventureWork2012Pub”, ‘

+ ‘ @article =@tableName’ + Constraints.table_name+’,’

+ ‘ @source_owner =@SchemeName’ + Constraints.table_name+’,’

+ ‘ @source_object =@tableName’ + Constraints.table_name+’,’

+ ‘ @type = N”logbased”, ‘

+ ‘ @description = null, ‘

+ ‘ @creation_script = null, ‘

+ ‘ @pre_creation_cmd = N”drop”, ‘

+ ‘ @schema_option = 0x000000000803509F,’

+ ‘ @identityrangemanagementoption = N”manual”, ‘

+ ‘ @destination_table =@tableName’ + Constraints.table_name+’,’

+ ‘ @destination_owner =@SchemeName’ + Constraints.table_name+’,’

+ ‘ @force_invalidate_snapshot=1 ‘

FROM

information_schema.table_constraints AS Constraints

JOIN

information_schema.key_column_usage AS KeyColumn

ON

Constraints.table_name=KeyColumn.table_name

AND

Constraints.constraint_name=KeyColumn.constraint_name

WHERE

Constraints.constraint_type=’Primary Key’

GO

[/sourcecode]

This script find all tables which has primary key and create add article script for them. Transactional Replication needs to have primary key on table to make it part of replication.

My publication name was “AdventureWork2012Pub” so I have used it in the script, you can have your own publication name there.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Insert Tracer Token to see replication latency

Insert Tracer Token to see replication latency

I have written article to find replication latency at “Find latency in transactional replication”. After reading this article, one of the blog reader put comment in article that they have transactional replication setup and if he runs the query given in my blog, he doesn’t get anything.

Well, let me tell you that replication latency is being maintained neither in publisher database nor in subscriber database. Latency is being maintained in “Distribution” database so the query I have provided at “Find latency in transactional replication”, should be run in “Distribution” database only.

Query given in article respond only if “Tracer Token” is inserted for the publication. You can insert “Tracer Token” from “Replication Monitor” but it won’t give you current status. You have to keep inserting tracer token and I used to do it from a SQL Server job which executes at every 5 minutes and insert new tracer token so that I can get latest latency.

Here is the script which inserts tracer token from the script and that needs to call from SQL Server Job.

[sourcecode language=”sql”]

DECLARE @PublicationName VARCHAR(50)

–give your own publication name here.

–my publication name is "AdventureWorks2012"

SET @PublicationName=’AdventureWorks2012′

exec sys.sp_posttracertoken @publication =@PublicationName

GO

[/sourcecode]

Execute this TSQL code in your publication database and enjoy the script given in “Find latency in transactional replication”.

If you are interested to read other replication related articles, have a look at following list:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)
  • Add stored procedure in transactional replication by script in SQL Server (click Here)
  • Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
  • Move replicated database file to new location in SQL Server 2012 (Click Here)
  • Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Find highest latency in transactional replication in SQL Server 2012

Find highest latency in transactional replication in SQL Server 2012

Replication is one of the very handy and useful high availability features SQL Server has provided. It is very easy to setup but maintaining health and keeping eye on performance of replication is very crucial and mandatory.

We can find “MSTracer_History”, “MSDistribution_Agents” and “MSTracer_Tokens” tables into distribution database of replication and hence the query I am going to give you, would be used under distribution database only.

Let me give you brief introduction, from MSDN, of all three system tables of distribution database.

MSTracer_History: The MStracer_history table maintains a record of all tracer tokens that have been received at the Subscriber. This table is stored in the distribution database and is used by replication for performance monitoring.

MSDistribution_Agents: The MSdistribution_agents table has one row for each Distribution Agent running at the local Distributor. This table is stored in the distribution database.

MSTracer_Tokens:  The MStracer_tokens table maintains a record of tracer token records inserted into a publication. This table is stored in the distribution database and is used by replication for performance monitoring.

Now here is the query I use often to check the latency of replication.

[sourcecode language=”sql”]
SELECT
                SysServ.name AS SubscriberName,
                MSDA.subscriber_db AS SubscriberDatabase,
                MSDA.publication AS PublicationName,
                MAX(DATEDIFF(SS,publisher_commit,distributor_commit)) AS LatencyPublisherToDistributor,
MAX(DATEDIFF(SS, distributor_commit,subscriber_commit)) AS LatencyDistributorToSubscriber,
MAX(DATEDIFF(SS,publisher_commit,distributor_commit)
                + DATEDIFF(SS, distributor_commit,subscriber_commit)) AS TotalLatency
FROM
                MStracer_history MSTH
INNER JOIN
                MSdistribution_agents MSDA
ON
                MSTH.agent_id = MSDA.id
INNER JOIN
                sys.servers SysServ
ON
                MSDA.subscriber_id = SysServ.server_id
INNER JOIN
                MStracer_tokens
ON
                MSTH.parent_Tracer_id=MStracer_tokens.tracer_id
WHERE
                subscriber_commit >DATEADD(hh,-1,GETDATE())
GROUP BY
                SysServ.name,
                MSDA.subscriber_db,
                MSDA.publication
GO[/sourcecode]

You can modify above given query for your customize need. I have filtered (in WHERE clause) records for last one hour so it will give you highest latency of last one hour. You can even filter records on TotalLatency field, If you want to see resultset only if it cross certain seconds/minutes while transferring records from publisher to subscriber.

Do let me know what technique do you use to keep your eye on replication health?

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Add new article in transactional replication in SQL Server 2012

Add new article in transactional replication in SQL Server 2012

Creating replication is one thing and maintaining it is a different yet equally important thing. Over the time, you might add or remove some objects in your primary database and hope the same for your replication database and this article comes into the picture when you need to add some more object from primary (Publication) database to replicated (subscriber) database.

I have already written few articles on replications which are listed below:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)

I will use the Publisher and Subscriber of Transaction Replication to demonstrate this article. You can create the same replication from here.

If you already having your replication ready to add article (Table, Stored Procedure, View, Index View, Functions etc.), follow the steps given below.

 

1.)  Connect to your publisher server and right click on publication name and click on “Property” from pop up menu.

 

2.) You will get Publication property dialog box and you have to move to “Article” page from the left hand menu to get a list of articles available to publish in publication database. Select the object you want to replication. Make sure the database table you select is having the primary key. Without primary key, table won’t be able to be a part of transaction replication.

 

 

3.) Once you select object and click on “Ok” button in above given dialog box,  right click on “Replication” option in objection explorer of SSMS and click on “Launch Replication Monitor”.

 

 

4.) You will see “Agent” tab in Replication Monitor dialog box, right click on “Snapshot Agent” and click on “Start Agent” option; it takes little time, based on the size of object and data, to complete the snapshot agent.

As soon as Snapshot Agent builds completely, you can check your subscriber database, you will find the entire selected object in subscriber database.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Setup Transaction Replication in SQL Server 2012

Setup Transaction Replication in SQL Server 2012

Before you move ahead for setting up Transaction Replication, You have to understand few definitions which are heavily used in replication, for example Publisher, Distributor and subscriber etc. You can get an idea of all these terminology from my earlier article “Understanding replication with Implementation of Snapshot Replication in SQL Server 2012”.

Transaction Replication: Transaction Replication is used when DML or DDL schema changes performed on an object of one database on one SQL Server Instance/ server needs to be replicated immediately on the database residing on another SQL Server Instance/server.

Change in Schema as well as in data will be reflected in replicated database immediately but this is depends on the network/internet setup you have between both SQL Server Instance/Server.

You can select any number of articles (object) as a candidate of replication no matter whether it is function, stored procedure or table. Just keep one thing in mind that any table you choose as a candidate of transaction replication, supposed to have Primary Key. Table without primary key will not be a part of transaction replication.

You can select two different SQL Server installation located in two different Server may or may not be in same premises but in same network. I have had two different instance of SQL Server 2012 in one server while writing this article.

Instance 1: “WIN-9H6QATRKY81\SQL2K12DEV”, I will call this instance as ServerA in this article.

Instance 2: “WIN-9H6QATRKY81\SQL2K12DEVTest”, I will call this instance as ServerB in this article.

If you are ready with AdventureWorks2012 database or any other database of your choice in one server and have another server available for subscriber, let us start creating replication.

1.) Create Distributor by login to ServerB (Subscriber) as I want to create Distributor in Subscriber server. If budget permits, it is good to have third dedicated server for distributor.

2.) Select or add the server where you wanted to create distributor database, since we are already logged into to ServerB, we can directly select first radio button from following screen capture.

3.) If your SQL Server Agent not set to start automatically at startup, it will ask you to start it as SQL Server Agent is responsible to run the job which will transmit data.

4.) Give shared network path where replication can create snapshot and use that location as a primary source for data. Note that both or all three server should have proper access to this shared folder as we are going to save all script file along with distributor database in this path.

5.) Give path where SQL Server can create “Distributor” database which is responsible to receive data from publisher and send it to subscriber.

6.) Select Publisher server for distribution database. By default it has selected ServerB but we want ServerA to be the publisher so add ServerA instance here.

7.) login to publisher database and it will be selected.

8.) Now deselect ServerB and Select ServerA where we have just logged in from above screen.

9.) I have provided password for my SA login password so that distributor can use this password while connecting to publisher. You can use any other login with sufficient permission.

10.) Select “Configure Distribution” checkbox along with “Generate Script” check box

11.) Give the path for saving a script file. I have used the same shared path I have used before.

Here is the script I have received after clicking on the “Finish” button from above screen.

[sourcecode language=”sql”]

/****** Scripting replication configuration. Script Date: 11/25/2012 3:49:05 PM ******/

/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 11/25/2012 3:49:05 PM ******/

use master

exec sp_adddistributor @distributor = N’WIN-9H6QATRKY81\SQL2K12DEVTEST’, @password = N”

GO

exec sp_adddistributiondb @database = N’distribution’, @data_folder = N’E:\ReplicationShare’, @log_folder = N’E:\ReplicationShare’, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1

GO

use [distribution]

if (not exists (select * from sysobjects where name = ‘UIProperties’ and type = ‘U ‘))

create table UIProperties(id int)

if (exists (select * from ::fn_listextendedproperty(‘SnapshotFolder’, ‘user’, ‘dbo’, ‘table’, ‘UIProperties’, null, null)))

EXEC sp_updateextendedproperty N’SnapshotFolder’, N’E:\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’

else

EXEC sp_addextendedproperty N’SnapshotFolder’, N’E:\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’

GO

exec sp_adddistpublisher @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distribution_db = N’distribution’, @security_mode = 0, @login = N’sa’, @password = N”, @working_directory = N’E:\ReplicationShare’, @trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’

GO

[/sourcecode]

12.)  Now manually confirm that you have received “Distributor” database under “System Databases” in the ServerB or not.

13.) Go to ServerA, this is the time to create publication now.

14.) Select ServerA as a pubcation.

15.) Give the same shared path to the replication to save and keep snapshot of publication.

16.) Select the database you wanted to replicate. I have selected “AdventureWorks2012” from ServerA.

17.) Select “Transactional Replication” from following screen.

18.) Select Table, SP, Functions etc. which you want to replicate. Confirm the table you select for replication, supposed to have primary key. Select other supported objects of table too. For example you have one computed column populated from UDF in your table so you have to select that function along with table.

19.) Click on next from following screen as I don’t want to specify any criteria for data. I want to transfer all data from my table.

20.) Select checkbox for “Create a snapshot immediately”.

21.)Click on “Security Settings” from following screen shot.

22.) provide sufficient credential here so that replication works smooth without any security issues.

23.) Once you provide security credential and click on “Ok” button, you will get screen something like this:

24.) select both check box from following screen.

25.) give proper path to save script for your publication.

Here is the script generated for publication:

[sourcecode language=”sql”]

/****** Scripting replication configuration. Script Date: 11/25/2012 4:01:52 PM ******/

/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 11/25/2012 4:01:52 PM ******/

use master

exec sp_adddistributor @distributor = N’WIN-9H6QATRKY81\SQL2K12DEV’, @password = N”

GO

exec sp_adddistributiondb @database = N’distribution’, @data_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQL\Data’, @log_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQL\Data’, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1

GO

use [distribution]

if (not exists (select * from sysobjects where name = ‘UIProperties’ and type = ‘U ‘))

create table UIProperties(id int)

if (exists (select * from ::fn_listextendedproperty(‘SnapshotFolder’, ‘user’, ‘dbo’, ‘table’, ‘UIProperties’, null, null)))

EXEC sp_updateextendedproperty N’SnapshotFolder’, N’\\win-9H6QATRKY81\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’

else

EXEC sp_addextendedproperty N’SnapshotFolder’, N’\\win-9H6QATRKY81\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’

GO

exec sp_adddistpublisher @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distribution_db = N’distribution’, @security_mode = 0, @login = N’sa’, @password = N”, @working_directory = N’\\win-9H6QATRKY81\ReplicationShare’, @trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’

GO

use [AdventureWorks2012]

exec sp_replicationdboption @dbname = N’AdventureWorks2012′, @optname = N’publish’, @value = N’true’

GO

use [AdventureWorks2012]

exec [AdventureWorks2012].sys.sp_addlogreader_agent @job_login = N’win-9H6QATRKY81\Administrator’, @job_password = null, @publisher_security_mode = 0, @publisher_login = N’sa’, @publisher_password = N’ ‘, @job_name = null

GO

— Adding the transactional publication

use [AdventureWorks2012]

exec sp_addpublication @publication = N’AdventureWorks2012′, @description = N’Transactional publication of database ”AdventureWorks2012” from Publisher ”WIN-9H6QATRKY81\SQL2K12DEV”.’, @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’

GO

exec sp_addpublication_snapshot @publication = N’AdventureWorks2012′, @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’win-9H6QATRKY81\Administrator’, @job_password = null, @publisher_security_mode = 0, @publisher_login = N’sa’, @publisher_password = N”

use [AdventureWorks2012]

exec sp_addarticle @publication = N’AdventureWorks2012′, @article = N’Customer’, @source_owner = N’Sales’, @source_object = N’Customer’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509F, @identityrangemanagementoption = N’manual’, @destination_table = N’Customer’, @destination_owner = N’Sales’, @vertical_partition = N’false’, @ins_cmd = N’CALL sp_MSins_SalesCustomer’, @del_cmd = N’CALL sp_MSdel_SalesCustomer’, @upd_cmd = N’SCALL sp_MSupd_SalesCustomer’

GO

[/sourcecode]

26.) Provide the name for your publisher. I have provided “AdventureWorks2012”

27.)  now connect to ServerB and add “New Subscription”

28.) Subscriber needs publisher to get data so select out publisher we have created.

29.) our publication name was “AdventureWorks2012” which I have selected from following screen:

30.) select “Pull Subscription” as I want to run all replication related job in ServerB and pull the data from ServerA. In Push method, replication related job runs from my ServerA which is primary server for my application and I doesn’t want to add overhead of replication job.

31.) If you already have Subscription database then select it otherwise create a new one. I have created new database named “AdventureWorks2012Subscriber”

32.) my newly created subscription database “AdventureWorks2012Subscriber” is selected now.

33.) SQL Server Agent would need proper credential to connect with Distrubtor database and subscription database. Give proper credential to keep your replication smooth.

34.) select “Run Continuously” for agent.

35.) select “Immediately” so that subscription gets populated immediately.

36.)  select both the checkbox as usual.

37.)  Give the path for script of subscription.

Here is the script generated for subscription.

[sourcecode language=”sql”]

—————–BEGIN: Script to be run at Publisher ‘WIN-9H6QATRKY81\SQL2K12DEV’—————–

use [AdventureWorks2012]

exec sp_addsubscription @publication = N’AdventureWorks2012′, @subscriber = N’WIN-9H6QATRKY81\SQL2K12DEVTEST’, @destination_db = N’AdventureWork2012Subscriber’, @sync_type = N’Automatic’, @subscription_type = N’pull’, @update_mode = N’read only’

GO

—————–END: Script to be run at Publisher ‘WIN-9H6QATRKY81\SQL2K12DEV’—————–

 

—————–BEGIN: Script to be run at Subscriber ‘WIN-9H6QATRKY81\SQL2K12DEVTEST’—————–

use [AdventureWork2012Subscriber]

exec sp_addpullsubscription @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @publication = N’AdventureWorks2012′, @publisher_db = N’AdventureWorks2012′, @independent_agent = N’True’, @subscription_type = N’pull’, @description = N”, @update_mode = N’read only’, @immediate_sync = 1

 

exec sp_addpullsubscription_agent @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @publisher_db = N’AdventureWorks2012′, @publication = N’AdventureWorks2012′, @distributor = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distributor_security_mode = 0, @distributor_login = N’sa’, @distributor_password = null, @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 = 20121125, @active_end_date = 99991231, @alt_snapshot_folder = N”, @working_directory = N”, @use_ftp = N’False’, @job_login = N’win-9H6QATRKY81\Administrator’, @job_password = null, @publication_type = 0

GO

—————–END: Script to be run at Subscriber ‘WIN-9H6QATRKY81\SQL2K12DEVTEST’—————–

[/sourcecode]

So finally we have implemented Transaction Replication. You can confirm whether replication is working or not by inserting few records in one of the table in ServerA which supposes to take effect immediately in ServerB’s database.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.