Category Archives: replication

Error Fix – Replication subscriber does not exist on the server anymore

Error Fix – Replication subscriber does not exist on the server anymore

Recently one of my client has faced weird situation related to SQL Server Replication. Their main database goes down so they started running mirroring database as a primary database, removed subscriber and distributor. Once they have setup primary server back properly, they have restored mirrored database backup to primary server.

Now situation is complicated as subscriber was removed, distributor was removed but in primary server, they are seeing publication as well as subscriber. They have already tried the way I have explained in one of my earlier article “Forcefully remove replication publisher, subscriber and distributor in SQL Server” but no luck.

When they tried to remove publication by pressing “Del” key after selecting “Publication” under “Replication” in SSMS, they faced following message.

TITLE: Microsoft SQL Server Management Studio
------------------------------
Could not delete publication 'SMXPPublisher'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=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 drop the publication because at least one subscription exists for this publication. Drop all subscriptions to the publication before attempting to drop the publication. If the problem persists, replication metadata might be incorrect; consult Books Online for troubleshooting information.
Changed database context to 'SMXP'. (Microsoft SQL Server, Error: 14005)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=14005&LinkId=20476
------------------------------
BUTTONS:

OK
------------------------------

Here is the screen capture of the error:

RemovePublicationError

This is obvious message that they can’t delete Publisher until and unless they remove subscriber under publication but the twist is they have already deleted subscriber from the secondary server and primary server were down at that time so effect doesn’t came there so while deleting subscriber from the primary server, they were greeted with following message.

TITLE: Microsoft SQL Server Management Studio
------------------------------
"MARS:SMXPRepl" does not exist on the server anymore. Please refresh its parent tree node.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=ObjectNoLongerExists&LinkId=20476
------------------------------

BUTTONS:

OK
------------------------------

Here is the capture for this error:

RemoveSubscriberError

Now, there is a big question, how to remove this replication?

After getting in this clumsy situation, they have called me up to help. They needed very fast solution so I provided undocumented and dirty but yet workable way. Here is what I have asked them to perform and update me with the result.

Here is the small TSQL script I told them to run on primary server in replicated database.

USE master
EXEC sp_removedbreplication @dbname='smxp'
GO

sp_dropsubscription @subscriber='smxprepl'

sp_droppublication 'smxppublisher'

After executing above given commands, they were facing following error:

--Cannot drop the table 'dbo.MSpeer_lsns' because it is being used for replication.

Well, my suggestion now is to unmark “MSPeer_lsns” object from replication with following command and then try to execute above script to remove and drop replication.

sp_MSunmarkreplinfo 'MSpeer_lsns'

Again there wasn’t luck with them so finally I send them one more TSQL which did the magic.

DROP TABLE sysarticleupdates
DROP TABLE sysarticles
sp_removedbreplication 'smxp'
GO
DROP TABLE syspublications
DROP TABLE sysschemaarticles
DROP TABLE systranschemas
DROP TABLE syssubscriptions
DROP TABLE sysarticlecolumns
DROP TABLE MSpub_identity_range
DROP TABLE MSpeer_response
DROP TABLE MSpeer_request
DROP TABLE MSpeer_lsns
DROP VIEW sysextendedarticlesview

After executing above given TSQL script, it has removed everything related to replication. However, I don’t recommend this script for normal situation.

Here is the list of my other replication related articles, if you are interested!!!!

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)
  • 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)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (Click Here)
  • Find undistributed replication command in SQL Server (Click Here)
  • Keep watch on replication undistributed command in SQL Server via SSIS (Click Here)
  • Keep an eye on replication error 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.

Keep an eye on replication error in SQL Server

Keep an eye on replication error in SQL Server

Setting up replication is may be little easy but to keep a constant watch on the performance of the replication is one of the tough task to do. If setup of replication is proper and if you are having a proper hardware and infrastructure, you won’t face much issue. Since we are not facing much issue, we may feel hectic to keep constant watch on replication health and this is the time you may face some trouble in replication and you are not alert to handle that uncertain situation.

DBA can’t afford to be in this situation. Every DBA has to prepare their self ready to handle as much uncertain situation as possible. I have created one small query to run in “Distributor” database of replication which show me error message with publisher, subscriber name and exact date time if anything goes wrong with my replication.

I used to call following SELECT query in my monitoring application so that if any error comes, I get immediate update from the person who is manually monitoring my application in the company. “Prevention is always better then cure” but nobody is sure enough that after having prevention, there won’t be any situation arise which force us to have cure. This is the reason I keep adding things in my monitoring application though I used to take as much prevention as possible while setting up things.

SELECT
ma.publisher_db,
ma.publication,
ma.subscriber_db,
msre.time,
msre.error_text
FROM
MSrepl_errors msre
INNER JOIN
MSdistribution_history msh
ON
(msre.id = msh.error_id)
INNER JOIN
MSdistribution_agents ma
ON
(ma.id = msh.agent_id)
ORDER BY
msre.time DESC

Above SELECT query will return the result only, if we have any error in replication at the moment, otherwise you will get blank result set.

Over the time, I have written quite a few articles on the subject of “Replication”, If you wish to refer any of them, have a look at list given 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)
  • 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)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (Click Here)
  • Find undistributed replication command in SQL Server (Click Here)
  • Keep watch on replication undistributed command in SQL Server via SSIS (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.

Keep watch on replication undistributed command in SQL Server via SSIS

Keep watch on replication undistributed command in SQL Server via SSIS

In my earlier blog “Find undistributed replication command in SQL Server” I have exaplained the importance and usage of “sp_replmonitorsubscriptionpendingcmds” system stored procedure to find out undistributed command.

If we execute “sp_replmonitorsubscriptionpendingcmds”, you may get undistributed command one time manually but I always try to capture important information in my monitoring application so that If there is any issue, it comes to notice of our support people and they can inform DBA team immediately.

This is the requirement which triggered this article.

Here is my requirement and logic I want to implement.

I have one of the main production database (let us call this ProdDB) which is replicated to three different instance with same publication so we have three subscriber to ProdDB database. Out of these three databases, one database (let us call ProdDBSub1) is being used to display the report in our application which is being used by approx 12000 concurrent users.

Higher latency or huge undistributed command will provide wrong information to the client which is not at all acceptable and this is why I have captured latency of replication in my monitoring application (Find the script here) and also captured total undistributed command in my monitoring application, if undistributed command number keeps increasing for long time and doesn’t decrease, our support team member immediately call DBA to look at replication.

I have created one table in “DBAdb” database which has row for each publication/subscriber combination along with other important details. Each time I query “sp_replmonitorsubscriptionpendingcmds”, I used to store result in that table (update the current row only). I have this process in one SSIS package which is scheduled to run every 15 seconds .

Let us create some foundation in SQL Server before we start creating SSIS package.

--create new database, if it does not exists already
CREATE Database DBAdb
GO

--create one table which stored information for undistributed command
USE [DBAdb]
GO

CREATE TABLE [UndistributedCommands](
[Seq] [int] IDENTITY(1,1) NOT NULL,
[PublicationName] [varchar](100) NULL,
[SubscriberName] [varchar](100) NULL,
[PendingCommands] [int] NULL,
[PendingDuration] [int] NULL,
[LastRefreshed] [datetime] NULL,
[LastNonEmpty] [datetime] NULL
) ON [PRIMARY]
GO

--insert one record for your publication/subscriber.
--this record will be updated everytime for same publication/subscriber
INSERT INTO UndistributedCommands
SELECT 'ProdDBPub','ProdDBSub',0,0,GETDATE(),GETDATE()
GO

Now create one SSIS package and have three package level variable as shown in following screen capture.

1Variable

After having all three variables, take one “Execute SQL” Task in your package and use following query inside the task.

--system stored procedure to run in distribution database
execute sp_replmonitorsubscriptionpendingcmds
--replication publisher server
@publisher ='PubServer',
--replication publisher database
@publisher_db = 'ProdDBPub',
--replication publication name
@publication ='ProdDBSub',
--replication subscriber server
@subscriber ='SubServer',
--replication subscriber database
@subscriber_db ='ProdDBSub1',
--choose type of subscription you have
@subscription_type ='1' --0 for push and 1 for pull
GO

Here is the screen capture of “Execute SQL” Task:

2ExecuteSQL

System Stored Procedure “sp_replmonitorsubscriptionpendingcmds” will return result set and that should be mapped with one of the variable we have created above. Follow the screen capture.

Please note that, I have had database connection of distribution database of replication for above given screen capture.

3ExecuteSQL

Now, take one foreach loop container and iterate it with “Obj_Result” variable.

4ForEachLoop

Map two variables with the result set coming from “sp_replmonitorsubscriptionpendingcmds” in the Foreach Loop container itself:

5ForeachLoop

Now take one more execute SQL Task and kept it inside the foreach loop container. That “Execute SQL” task will have following query to update data in table.

DECLARE @PendingCMDCount BIGINT
DECLARE @ExstimatedProcessTime BIGINT

SET @PendingCMDCount = ?
SET @ExstimatedProcessTime =?

UPDATE
UndistributedCommands
SET
PendingDuration = @ExstimatedProcessTime ,
PendingCommands = @PendingCMDCount,
LastRefreshed = getdate()
WHERE
SubscriberName = 'ProdDBSub' AND PublicationName='ProdDBPub'

IF @PendingCMDCount <> 0
BEGIN
UPDATE
UndistributedCommands SET LastNonEmpty = getdate()
WHERE
SubscriberName = 'ProdDBSub' AND PublicationName='ProdDBPub'
END

6ExecuteSQL

Please note that this Execute SQL task is having connection with “DBAdb” database and my “UndistributedCommand” table is there and data from this table will come to my monitoring application.

We have collected two valued in two different variable in Foreach Loop Container which we will pass to “Execute SQL Task” inside the loop.

7ExecuteSQL

Now, you are having package ready to run.

8FinalPackage

Execute this package from SQL Server Job or from Windows Schedule task via .BAT file as per the frequency needed for your business logic, I used to keep it at 15 seconds. Display the rows of “UndistributedCommands” in monitoring application, if you have, if undistributed command is >0.

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 undistributed replication command in SQL Server

Find undistributed replication command in SQL Server

I have written one article to find replication latency sometime back. If I find latency of 10 seconds or more for longer time then I always try to find the bottleneck and fix it because in the environment I am working, won’t afford big latency.

In finding the bottleneck, my first step would be to find how many commands are pending to apply in replicated database. To find undistributed command, you can use replication monitor which comes with SQL Server itself or use sp_replmonitorsubscriptionpendingcmds” system stored procedure which you can find in distributor database of replication.

 

--system stored procedure to run in distribution database
execute sp_replmonitorsubscriptionpendingcmds
--replication publisher server
@publisher ='PubServer',
--replication publisher database
@publisher_db = 'AdventureWorks2012',
--replication publication name
@publication ='AdventureWorks2012Pub',
--replication subscriber server
@subscriber ='SubServer',
--replication subscriber database
@subscriber_db ='AdventureWorks2012ReplDB',
--choose type of subscription you have
@subscription_type ='1' --0 for push and 1 for pull
GO

 

Here is the screen capture of output.

ReplicationPendingCommand

This screen capture shows that total number of 64 commands are waiting to apply from “AdventureWorks2012” database to “AdventureWorks2012ReplDB” and that would take 0 (<1) seconds.

I have written few more replication related articles, have a look if you are interested.

  • 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)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (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.

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.


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

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.


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

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.

Script backup of replication setup of SQL Server by SSIS and SMO

Script backup of replication setup of SQL Server by SSIS and SMO

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO”, “Automatic script backup of email operator in SQL Server by SSIS and SMO”, “Backup Linked Server script in SQL Server by SSIS” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for replication publisher, subscriber, article etc. in SQL Server instance by SSIS and SMO.

Replication is one of the important High Availability and disaster recovery option for DBA which is being used by application load balancing too. When I failover (manually or automatically) the server, I suppose to have same replication and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package which uses SMO script to generate the script of replication along with its article of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of replication every week.

Let us now create new SSIS project and start creating package.

Have one “Script Task” in your package and double click on the “Script Task” so that you can get “Script Task Editor”. Click on “Edit Script” button from the “Script Task Editor” and you will get script windows where you can write down C# script (by default you get C# script editor).

ReplicationBackupScriptTask

You have to add reference for following name spaces.

Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

Microsoft.SQLServer.Replication .NET Programming Interface

Apart from that, have following extra namespaces in “NameSpaces” region in your script window.


using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using Microsoft.SqlServer.Replication;

using System.IO;

using System.Collections.Specialized;

Now here is the code you have to place in your “Main” method of script window.


// TODO: Add your code here

string ServerName;

string UserName;

string Password;

string FolderDate;

string NetworkPath;

ServerName = &quot;ServerName&quot;;

UserName = &quot;Login&quot;;

Password = &quot;Password&quot;;

FolderDate = DateTime.Now.ToShortDateString().Replace('\\','_').Replace('/','_');

NetworkPath = @&quot;D:\&quot;+ ServerName.Replace('\\', ' ').ToString() + @&quot;\Replicaiton\&quot;+FolderDate+&quot;\\&quot;;

ServerConnection conn = new ServerConnection();

conn.LoginSecure = false;

conn.Login = UserName;

conn.Password = Password;

conn.ServerInstance = ServerName;

Server srv = new Server(conn);

System.IO.Directory.CreateDirectory(NetworkPath);

ReplicationServer RS = new ReplicationServer(conn);

try

{

foreach (ReplicationDatabase RD in RS.ReplicationDatabases)

{

if (RD.HasPublications)

{

foreach (TransPublication TP in RD.TransPublications)

{

TextWriter tw = new StreamWriter(NetworkPath + &quot;\\&quot; + TP.Name.ToString() + &quot;.sql&quot;);

tw.Write(TP.Script(ScriptOptions.Creation | ScriptOptions.IncludeAll ^ ScriptOptions.IncludeReplicationJobs));

tw.Close();

}

}

}

}

catch (Exception eh)

{

//MessageBox.Show(eh.ToString());

}

Dts.TaskResult = (int)ScriptResults.Success;

Once you setup proper path as well as credential of your SQL Server Instance, you are ready to go. Run package manually to check whether it works or not and then schedule it in SQL Server Job or in Windows Task to run weekly or as per your requirement.

Here is the screen capture I have received after I ran it manually.

ScriptBackupReplicationPublisherSSISPackageRun

We have many different server/instance and need script for all email operator in from all server and hence I have had loop for all our database server and script task under the loop so that one package connect to each server, take a script of all operator and save it to two different network location.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

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.

Move replicated database file to new location in SQL Server 2012

Move replicated database file to new location in SQL Server 2012

Setting up replication is somehow little easy when we compare it with maintaining replication. We have already discussed some of the topics related to replication like set up replication, keep watch on replication latency, add & remove article from GUI and from Script, remove replication to name a few.

Moving replicated database file from one location to another location is little tricky and not performing proper steps in sequence would be resulted in replication failure.

There are many cases when we need to move file of database from one location to another. We may have purchased new improved IO subsystem or we are running low disk space is current drive and need to move all or some data/log file to another drive.

I will be using the same AdventureWorks2012 database which I have used in transactional replication I had in my earlier article.

Well, before we do anything, we have to confirm that there is no pending (Undistributed) transaction there in replication from “Replication Monitor”. If you don’t know how to open “Replication Monitor”, kindly click here to know.

After having replication monitor on the screen, click on “Publisher Name” then click on “Subscriber Name” from the tree view in left hand side. Once “Subscriber” is selected, you find, “All Subscriber” tab in right hand side, double click on the subscriber where your database is participated in. You have to confirm undistributed command is zero.

Once you confirm undistributed commands, close the popup window of Subscription and click on “Publisher” name in the tree view of “Replication Monitor”. Let this window open and start SSMS to confirm where data/log file are located at the moment.

I have used following TSQL query to check current location of my database “AdventureWorks2012”.

 SELECT * FROM sys.sysaltfiles
 WHERE DB_NAME(dbid) = 'AdventureWorks2012'
 GO
 

I have my database files in “C” drive.

Once, you keep this list of data/log file location, let us again move back to “Replication Monitor” and stop “Log Reader Agent”.

Now, stop the “Queue Reader Agent”, if it is setup in your environment.

Snapshot Agent should be completed and stop as well. Generally it supposed to be stopped only so I have not included its steps here.

Anyway, let us move on and take database “AdventureWorks2012” offline with following commands:

 USE master
 GO

-- Making database with restricted user and offline
 ALTER DATABASE AdventureWorks2012 SET restricted_user with rollback immediate;
 ALTER DATABASE AdventureWorks2012 SET OFFLINE;

I have my data/log file at “C:\AdventureWorks” folder and I want to move it to “D:\AdventureWorks” folder so I execute following command.

 USE master
 GO

-- Mapping database with new file path
 ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_data , FILENAME = 'D:\AdventureWorks\AdventureWorks2012_data.mdf');
 ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_log , FILENAME = 'D:\AdventureWorks\AdventureWorks2012_log.ldf');
 

Now, we have changed the path in system catalog but physically my files are still there in “C:\AdventureWorks” folder so I will manually copy it from current location and paste it in new location in “D” drive.

Once I finish manual copy of database files, I will execute following command to start database back online.

 USE master
 GO

-- Making database online and make available with multiuser
 ALTER DATABASE AdventureWorks2012 SET ONLINE;
 ALTER DATABASE  AdventureWorks2012 SET multi_user;
 

Now, let us confirm that whether our files are being read from new location or not by executing following command:

 SELECT * FROM sys.sysaltfiles
 WHERE DB_NAME(dbid) = 'AdventureWorks2012'
 GO
 

Yes, finally I have moved database file:

Now, start your all agents which you have stopped from replication monitor and you are done.

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.

Forcefully remove replication publisher, subscriber and distributor in SQL Server

Forcefully remove replication publisher, subscriber and distributor in SQL Server

I still remember one of my earlier post where I have mentioned how to remove replication and how to remove objects from replication. This is perfect solution for general use but sometime it happens that distributor database/server went crashed or distributor become unavailable or suspected and there is no chance to have that server/database back, we have to forcefully remove replication as regular or general practice wouldn’t work in that case.

When we remove publisher and/or subscriber, it updates distributor database with the latest information but in case we don’t have access of distributor database even we want to remove publisher/subscriber, we have to use “@ignore_distributor” option.

Here is the script to remove subscriber forcefully.


--Select your publication database

USE Adventureworks2012

GO

DECLARE @publication AS sysname;

DECLARE @subscriber AS sysname;

--enter your publication name

SET @publication = N'AdventureWorksPub';

--enter subscriber name

SET @subscriber = N'AdventureWorksSub';

USE [AdventureWorks2012]

EXEC sp_dropsubscription

@publication = @publication,

@article = N'all',

@subscriber = @subscriber

,@ignore_distributor=1;

GO

Here is the script to remove publisher forcefully.


DECLARE @publicationDB AS sysname;

DECLARE @publication AS sysname;

--set your publication database here

SET @publicationDB = N'AdventureWorks2012';

--set your publication name here

SET @publication = N'AdventureWorksPub';

-- Remove a transactional publication.

USE [AdventureWorks2012]

EXEC sp_droppublication

@publication = @publication

,@ignore_distributor=1;

-- Remove replication objects from the database.

USE [master]

EXEC sp_replicationdboption

@dbname = @publicationDB,

@optname = N'publish',

@value = N'false';

GO

If Distributor database available and you wanted to remove it forcefully, have a look at following script:


--execute following command on distributor server

USE master

GO

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

GO

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.

Add stored procedure in transactional replication by script in SQL Server

Add stored procedure in transactional replication by 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 and Add Table in transactional Replication by script but today I am going to explain how can we insert stored procedure by script in already established transactional replication. I will be using the same transactional replication I have set up earlier.

 USE AdventureWorks2012
 GO

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

SET @ProcName='uspUpdateEmployeeHireInfo'
 SET @SchemaName='HumanResources'
 SET @PublisherName='AdventureWorksPublisher'

EXEC sp_addarticle
 @publication = @PublisherName,
 @article = @ProcName,
 @source_owner = @SchemaName,
 @source_object = @ProcName,
 @type = N'proc schema only',
 @description = N'',
 @creation_script = N'',
 @pre_creation_cmd = N'drop',
 @schema_option = 0x0000000008000001,
 @destination_table = @ProcName,
 @destination_owner = @SchemaName,
 @status = 16,
 @force_invalidate_snapshot=1
 GO

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)
  • Add table in transactional replication from script 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.