Add table in transactional replication from script in SQL Server

Add table in transactional replication from script in SQL Server

I have already written many articles to cover different area of replication. Few days back I have written how to add articles (Table, View, SP etc.) into replication from GUI but today I am going to explain how can we insert table by script in already established transactional replication. I will be using the same transactional replication I have set up earlier.

[sourcecode language=”sql”]
USE AdventureWorks2012
GO

DECLARE @TableName VARCHAR(25)
DECLARE @SchemaName VARCHAR(25)
DECLARE @PublisherName VARCHAR(25)

SET @TableName=’Department’
SET @SchemaName=’HumanResources’
SET @PublisherName=’AdventureWorksPublisher’

exec sp_addarticle
@publication = @PublisherName,
@article = @TableName,
@source_owner = @SchemaName,
@source_object = @TableName,
@type = N’logbased’,
@description = null,
@creation_script = null,
@pre_creation_cmd = N’drop’,
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N’manual’,
@destination_table = @TableName,
@destination_owner = @SchemaName,
@force_invalidate_snapshot=1
GO[/sourcecode]

Some more replication related articles:

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

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.

Find Object participated in replication

Find Object participated in replication

It happens many times that we want to know the object participated in replication from publisher database. We may not have populated all database objects in replication so we might want to compare the object.

Sometime we may have populated all tables, view etc. in replication but over the time we add some more tables or other database objects in primary (Publisher) database and we want to find out what are those objects.

Sometime we want to remove some of the objects from subscriber database as we might have removed it either from replication or from publisher database and wanted to remove it from subscriber database.

Reason may be more then I have discussed above but in any case, whenever, we want to get list of objects participated in replication; we can use script given below:

[sourcecode language=”sql”]
SELECT * FROM
(
SELECT
Name as ObjectName
,CASE WHEN xtype=’P’ THEN ‘Stored Procedure’
WHEN xtype=’V’ THEN ‘View’
WHEN xtype=’FN’ THEN ‘Function’
WHEN xtype=’U’ THEN ‘User Table’
END AS ObjectType
FROM
sys.sysobjects
WHERE
replinfo=1
) AS T
WHERE
ObjectType IS NOT NULL
GO[/sourcecode]

Here is the list of some more articles I have written for replication, have a look if title of the article attracts you.

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

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.

Be familiar with important terms of replication in SQL Server

Be familiar with important terms of  replication in SQL Server

After reading replication articles two of my regular blog reader asked me question about some terms I have used in articles. Here is their asking in question format for all blog reader.

Q.1) What is PULL replication which you have used in almost all your replication article?

Ans.) PULL and PUSH are two different types of replication which SQL Server offers.

PULL: PULL subscription demands the data from publisher as needed.

PUSH: PUSH subscription pushes data from publisher to subscriber via distributor based on configuration eg: on demand, on schedule basis or continuously.

Q.2) What is “Snapshot Agent” which is used in Snapshot Replication and transactional replication in your articles?

Ans.) While working with replication, you have to familiar with following five main agents.

Snapshot Agent: Snapshot Agent plays very important role for any type of replication and runs from distributor database. Snapshot agent keeps information about synchronization; prepare schema and data for initial transfer.

Queue Reader Agent: This agent comes into the picture if you have snapshot/transactional replication configured with queued updating option, again this agent runs in distributor database.  It reads messages stored in a Microsoft SQL Server queue or a Microsoft Message Queue and then applies those messages to the Publisher

Log Reader Agent: – This Agent is used with transactional replication. It moves transactions marked for replication from in transaction log from Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor.

Merge Agent: Merge Agent comes in picture when there is a merge replication setup. It applies the initial snapshot to the Subscriber and merges incremental data changes. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber to update it. Unlike snapshot / transactional replication, merge agent runs either from distributor or from subscriber.

Distribution Agent: Distribution agent runs either from distributor or from subscriber based on your configuration in snapshot / transactional replication. Initial snapshot will be transferred from publisher to subscriber via distribution agent. It also moves all transaction held under distribution database to the subscriber.

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.

Delete Article from Transactional Replication in SQL Server 2012

Delete Article from Transactional Replication in SQL Server 2012

I have already demonstrated how to established snapshot replication as well as transactional replication in SQL Server 2012. Apart from that we have already seen how to remove replication and how to add new article (database objects like table, views, functions, stored procedures etc.) in transactional replication along with few other tips and tricks of replication. Now it is time to remove some of the unwanted articles from transactional replication. I will use the same transactional replication which I have created in my earlier article.

Removing article from replication and removing objects from replicated (subscription) database is two different things. Replication will stop replicating the changes in schema/data from publisher to subscriber once you remove article from replication but it will not remove schema/data from the subscriber database, you have to do it manually after removing object from replication.

Let us now proceed towards removing article from transactional replication.

1.)  open the replication option under your SSMS and move to the publication, right click on publication and click on “Properties” from the popup menu.

2.) Publication Properties dialog box, go to “Articles” option from the menu in left hand side and deselect the checkbox of article you want to remove from replication. I have remove “Sales.Customer” table and click on “Ok” button.

3.) Once you will click on OK button from Publication Properties dialog box above, replication will stop populating new data change to the “Sales.Customer” table but now I want to update my snapshot of replication so right click on “Replication” option and click on “Launch Replication Monitor” option from the popup menu.

4.) From the replication monitor, you can find “Agent” tab under your publication, you have to right click on “Snapshot Agent” and click on “Start Agent” from popup menu. This action will update snapshot which takes time based on the size of database.

All these four steps will successfully remove articles from replication now if you wish; you can manually go to subscriber database and drop objects which you have removed from replication.

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.

Error Fix – Cannot execute as the database principal because the principal dbo does not exist this type of principal cannot be impersonated or you do not have permission

Error Fix – Cannot execute as the database principal because the principal dbo does not exist this type of principal cannot be impersonated or you do not have permission

 

Earlier I have written one article to setup replication in SQL Server 2012 and remove replication from SQL Server 2012. One of the colleagues in my previous company (Yes, I do keep relation with my ex. colleagues :) ) sent me an email to help.

While he was trying to remove publication by following the instruction given in my article “remove replication from SQL Server 2012”,  Error seems interesting and looks big but solution to this error is very easy and short. he comes across the following error:

[sourcecode language=”sql”]TITLE: Microsoft SQL Server Management Studio
——————————
Could not delete publication ‘AdvertureWorks2012AddressObjects’.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.0.2100.60&EvtSc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeletePublication&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. Changed database context to ‘AdventureWorks2012’. (Microsoft SQL Server,Error: 15517)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2218&EvtSrc=MSSQLServer&EvtID=15517&LinkId=20476
[/sourcecode]

Error would looks like this screen capture:

If you are trying removing the publication with the login which has insufficient privileges, you might come across this error.  There is one small command to execute to give proper permission to your login. Execute following command in your SQL Server Instance.

[sourcecode language=”sql”]
ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [LoginName];
[/sourcecode]

As soon as you will have proper permission, you will be able to remove publication.

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.

Remove Replication from SQL Server 2012

Remove Replication from SQL Server 2012 (Part 2)

Removing replication needs some steps to be performed in proper sequence otherwise either replication will not be removed or not completely removed which leads some issues in future especially log file size related issues.

For removing replication, it is mandatory that we have one replication setup to remove. You can consider this as pre-requisite of this article. If you don’t have any replication setup to remove, you can set up one replication by following step given in my earlier article “Understanding replication with Implementation of Snapshot Replication in SQL Server 2012 (Part 1)

If you are ready with replication, let us move ahead. Follow the steps given here to remove the replication.

1.)    Delete the subscriber from Replication -> Local Publication -> “Subscriber Name”. look at following screen capture for more detail

2.)    Delete the publication from Replication -> Local Publications -> “Publication Name”

3.)    Delete the distribution database by right click on Replication and click on “Disable publishing and Distribution …”, follow the screen capture for more detail:

Once you are done with removing of Publication, Distributor and Subscriber, you can confirm whether distributor database is there or not manually. For more confirmation, you can execute the following command in the SQL Server instance where you have had your distribution database.

[sourcecode language=”sql”]exec master..sp_dropdistributor[/sourcecode]

As soon as you execute above given command in the instance where you have had your distributor database, you will come across following error as it can’t find distributor database because we have already removed it.

[sourcecode]
Msg 21043, Level 16, State 1, Procedure sp_dropdistributor, Line 50
The Distributor is not installed.
[/sourcecode]

Now let us execute following SELECT statement in principal database to confirm whether any article still having replication status or not.

[sourcecode language=”sql”]
SELECT
*
FROM
sys.sysobjects
WHERE
replinfo=1
[/sourcecode]

You should get no result from above SELECT statement as we have removed replication completely.

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.

Understanding replication with Implementation of Snapshot Replication in SQL Server 2012 (Part 1)

Understanding of replication with Implementation of Snapshot Replication in SQL Server 2012 (Part 1)

As per MSDN, replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

There are four types of replication, excluding subtype, available in SQL Server, such as:

  • Merge Replication
  • Snapshot Replication
  • Transaction Replication
  • Pee-to-peer replication

We will cover Snapshot Replication in this article; remaining type of replication will be available in follow up blog post.

Before we start configuring replication, it is mandatory to understand some of the terminology we will use in replication.

Publisher: Publisher is a server which hosts the database which acts as a source of data to distribute among other databases.

Distributor: Distributor is a server which hosts the database which acts as a mediator between publisher and subscriber. It receives the data from publisher database and sends it to subscriber database. You can set distributor server same as publisher server but it is HIGHLY recommended that you keep distributer server/database in separate server so that publisher server doesn’t be heavily loaded.

Subscriber: Subscriber is the server which hosts the databases where publisher/distributer sends the data to.

You can set replication between any server(s) which can be accessible to each other via network, VPN, dial up or anything else. In production environment, I would prefer to have three different servers (if budget permits) one host publisher, second host distributor and third host subscriber but here for this article, I have installed two different instance of SQL Server 2012. You can even host two different virtual machines in your computer to test this article.

My first instance is WIN-9H6QATRKY81\SQL2K12DEV and I will refer it as ServerA in this article.

My second instance is WIN-9H6QATRKY81\SQL2K12DEVTest and I will refer it as ServerB in this article.

Snapshot Replication:

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers. This is one of the very simple replication and easy to setup/maintain. In simple words, Snapshot replication creates snapshot of publisher database and send it to subscriber via distributor.

You should select Snapshot replication if your database has infrequent changes, your database is small in size or your database is having very large volume of data change in short duration. If these are not the case for you, you have to choose another replication not snapshot.

Before you actually proceed towards replication, you have to check few things.

  • List table, view, Stored procedure and any other SQL Server objects which you want to replicate
  • You have to have one shared folder which can be accessible from any of the servers which are part of replication in any capacity (Publisher, Distributer, and Subscriber). My shared folder is at \\win-9H6QATRKY81\ReplicationShare.

Now let us implement snapshot replication on AdventureWorks2012 database. If you don’t, already, have this database, kindly download it from here or use your own database.

1.) Connect to you SQL Server 2012 which you want as a publisher and right click “Replication” then click on “Configure Distribution” option. I connected to “ServerA

2.) Select the distributor server. If you want the current SQL Server 2012 instance to be act as distributor server, left the default selection as it is. That is what I did here for this article. If you wish, you can change selection via radio button and click on “Add” button to select another SQL server 2012 instance to work as distributor.

3.)    Give the path of snapshot folder where the snapshot will be created. This path should be accessible from all servers which are part of replication.  In my case, it is \\win-9H6QATRKY81\ReplicationShare.

4.)  You have to create or select distributor database. I have created new database with “Adv2012Distributor” name.

5.)    Select check box “Configure Distributor” and click on Finish.

though screen capture is not showing script check box selected above but if you would have selected it, it would have generated script to add distributor. script would look something like this:

[sourcecode language=”sql”]

/****** Scripting replication configuration. Script Date: 10/21/2012 10:19:49 AM ******/
/****** 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: 10/21/2012 10:19:49 AM ******/
use master
exec sp_adddistributor @distributor = N’WIN-9H6QATRKY81\SQL2K12DEV’, @password = N”
GO
exec sp_adddistributiondb @database = N’Adv2012Distributor’, @data_folder = N’E:\Data’, @log_folder = N’E:\Data’, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

use [Adv2012Distributor]
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’Adv2012Distributor’, @security_mode = 1, @working_directory = N’\\win-9H6QATRKY81\ReplicationShare’, @trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’
GO
[/sourcecode]

6.)Let us confirm whether our distributor database is created or not in ServerA. You can find it under “System Databases” Group. Refer following screen capture

7.)Once you are done with “Distributor” configuration, you should proceed towards “Publication”.  Connect to the server which you want to make as publisher, in my case, it is ServerA. Follow the given screen capture to know the option to click

8.) You will have to select the publisher database, in our case, it is AdventureWorks2012

9.)    Now this is the time to choose which replication you wanted create. At the point, we will create “Snapshot Replication”

10.) Now, you have to select table, view, stored procedure, indexed view and/or user defined functions. You can select all if you want. As of now, I have selected only two tables.

11.) Now if you don’t wish to replicate some data based on filter, you can have filter by click on “Add” button but I didn’t choose any filter and clicked on “Next” button in following screen or wizard.

12.) we are instructing that snapshot of publisher database should be created at the moment by first check box. scheduling is VERY IMPORTANT for replication as whatever change you will make in data in your publisher database, it will take effect in subscriber database(s) once the schedule time reach. Schedule should be very from case to case, it shouldn’t be too low otherwise before your snapshot applies to subscriber, your second run of snapshot will be reached. As against, it shouldn’t be too high that subscriber doesn’t have point-in time data. You can click on “Change” button and run it at every 10 minutes for now.

13.) this step is very important. You have to give proper login credential so that replication doesn’t meet with any permission issue. I would suggest you give windows authentication there in form of domainName\Login and Password.

14.) select both the check box given in following screen.

15.) give the proper path so that you will have .SQL file of publication script.

16.) Now give publication name here. one publisher database may have few different publication.

Once you will click on “Finish” button in above given screen, your publisher will be created and you will have publication script as follow:

[sourcecode language=”sql”]
use [AdventureWorks2012]
exec sp_replicationdboption @dbname = N’AdventureWorks2012′, @optname = N’publish’, @value = N’true’
GO
— Adding the snapshot publication
use [AdventureWorks2012]
exec sp_addpublication @publication = N’AdvertureWorks2012AddressObjects’, @description = N’Snapshot publication of database ”AdventureWorks2012” from Publisher ”WIN-9H6QATRKY81\SQL2K12DEV”.’, @sync_method = N’native’, @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’snapshot’, @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
GO

exec sp_addpublication_snapshot @publication = N’AdvertureWorks2012AddressObjects’, @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [AdventureWorks2012]
exec sp_addarticle @publication = N’AdvertureWorks2012AddressObjects’, @article = N’Address’, @source_owner = N’Person’, @source_object = N’Address’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509D, @identityrangemanagementoption = N’manual’, @destination_table = N’Address’, @destination_owner = N’Person’, @vertical_partition = N’false’
GO

use [AdventureWorks2012]
exec sp_addarticle @publication = N’AdvertureWorks2012AddressObjects’, @article = N’AddressType’, @source_owner = N’Person’, @source_object = N’AddressType’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509D, @identityrangemanagementoption = N’manual’, @destination_table = N’AddressType’, @destination_owner = N’Person’, @vertical_partition = N’false’
GO
[/sourcecode]

17.) Now, we will create subscription. please connect with the SQL Server 2012 which you want to be subscription. In my case, it is ServerB. follow the steps given in following screen capture.

18.) For creating subscriber, you have to connect with publisher server.

19.) after connecting to publisher server, select the publication we have created.

20.) I don’t want to run SQL Server Agent job of replication at Publisher server because publisher server suppose to have load from various sources so I would go for PULL subscription so that replication job will run from ServerB and fetch the data from ServerA.

21.) Select or Create subscription database. since this is first time we are setting this subcription, we have no chance to have already created database so we will create new DB with name “AdventureWorks2012Subs”.

22.) This steps is VERY IMPORTANT, You must give proper login credential here. I would suggest to make sure that credential you are giving here, is having proper access of distributor and subscriber server. You can give two separate login credential here. I would like to go for windows authentication here too. Highly recommend, don’t go with “Impersonate Process account” as shown in following screen capture.

23.) select “Run Continuously” for “Agent Schedule” so that it keeps running at the interval we have selected.

24.) now select “Immediately” for “Initialize When” drop down list so that your configuration of snapshot agent takes effect immediately. if your credential provided in above screen is not proper, replication will not be able to move data between server.

25.) select both the check box same as we did two times before.

26.) give the proper path to save .SQL file for your Subscription script for future use.

Subscription scrip would look something like this:

[sourcecode language=”sql”]—————–BEGIN: Script to be run at Publisher ‘WIN-9H6QATRKY81\SQL2K12DEV’—————–
use [AdventureWorks2012]
exec sp_addsubscription @publication = N’AdvertureWorks2012AddressObjects’, @subscriber = N’WIN-9H6QATRKY81\SQL2K12DEVTEST’, @destination_db = N’AdventureWorks2012Subs’, @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 [AdventureWorks2012Subs]
exec sp_addpullsubscription @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @publication = N’AdvertureWorks2012AddressObjects’, @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’AdvertureWorks2012AddressObjects’, @distributor = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distributor_security_mode = 1, @distributor_login = N”, @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 = 20121021, @active_end_date = 99991231, @alt_snapshot_folder = N”, @working_directory = N”, @use_ftp = N’False’, @job_login = null, @job_password = null, @publication_type = 0
GO
—————–END: Script to be run at Subscriber ‘WIN-9H6QATRKY81\SQL2K12DEVTEST’—————–
[/sourcecode]

Finally we have successfully implemented Snapshot replication. You can confirm whether replication is working or not by insert/update few records in “Person.Address” table which was one of our two articles (table) we have added in this replication.

Please not that insert/update you have made, will not be immediately replicated. It will be replicated when you

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.