Hi,
After disabling publishing on my server, there were
numerous merge replication related objects.
How do I clean them up. I tried to drop them, but I get a
message saying I am trying to drop system objects, and the
effort fails.
Thanks,
Sang
Sang,
try sp_removedbreplication (assuming the database is no longer contains any
publications/subscriptions).
Hilary Cotter sent me a link to a script he created at http://www.ava.co.uk
(technical resouces section) that you might want to look at, if the above
stored proc doesn't remove all the objects.
HTH,
Paul Ibison
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Sunday, March 25, 2012
Clean up replication data
I have a develop machine for our web site. On this machine I have a copy of a database from the production site. The database on the production site is being replicated. When I copied the database to the develop machine and started testing some new developments I got the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sysmergearticles'
This tells me that some replication data is also copied to the develop database.
Can anyone tell me how I can clean this data, or if I should copy the databse in a different way. (Now I create a new databse and do an import of the tables and stored procedures)You could try to remove replication by going under Tools(in EM)-->Replication-->Disable publishing,Distri...If it allows you to do so then it's fine.Or else you could try to first set the replication on your Test system and then remove ot by using the Tools-->Replication.
Or you may try the script below:
USE [DBNAME]
GO
-- Drop all replication triggers from the database
PRINT 'Drop all replication triggers from the database'
SELECT trigs.name AS TriggerName,
trigs.id AS TriggerID,
tables.name AS TableName
INTO #Triggers
FROM sysobjects trigs
INNER JOIN sysobjects tables
ON trigs.parent_obj=tables.id
where trigs.category=2 and trigs.xtype='TR'
DECLARE @.TriggerName varchar(100), @.TriggerID INT, @.TableName varchar(100)
DECLARE cur CURSOR for SELECT * FROM #Triggers
OPEN cur
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP TRIGGER ' + @.TriggerName)
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication constraints from the database
PRINT 'Drop all replication constraints from the database'
DECLARE @.ConstName varchar(100), @.ConstID INT, @.TableName varchar(100)
SELECT CONST.name AS ConstName,
CONST.id AS ConstID,
tables.name AS TableName
INTO #Constraints
FROM sysobjects CONST
INNER JOIN sysobjects tables
ON CONST.parent_obj=tables.id
where CONST.xtype='C'
DECLARE cur CURSOR for SELECT * FROM #Constraints
OPEN cur
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER Table ' + @.TableName + ' DROP CONSTRAINT ' + @.ConstName)
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User tables
PRINT 'Drop all replication User tables'
DECLARE @.TableName varchar(100), @.TableID INT
SELECT Tables.name AS ConstName,
Tables.id AS ConstID
INTO #Tables
FROM sysobjects Tables
where Tables.xtype='U' AND Status < 0 AND category=2050
DECLARE cur CURSOR for SELECT * FROM #Tables
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.TableID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Table ' + @.TableName)
FETCH NEXT FROM cur INTO @.TableName, @.TableID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User procedures
PRINT 'Drop all replication User procedures'
DECLARE @.ProcName varchar(100), @.ProcID INT
SELECT Procs.name AS ConstName,
Procs.id AS ConstID
INTO #Procedures
FROM sysobjects Procs
where procs.xtype='P' AND Status < 0
DECLARE cur CURSOR for SELECT * FROM #Procedures
OPEN cur
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Procedure ' + @.ProcName)
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User Views
PRINT 'Drop all replication User Views'
DECLARE @.ViewName varchar(100), @.ViewID INT
SELECT MyViews.name AS ConstName,
MyViews.id AS ConstID
INTO #Views
FROM sysobjects MyViews
where MyViews.xtype='V' AND Status < 0 AND Name NOT LIKE 'sys%'
DECLARE cur CURSOR for SELECT * FROM #Views
OPEN cur
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP View ' + @.ViewName)
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication rowguids, defaults and indexes
PRINT 'Drop all replication rowguids, defaults and indexes'
SELECT tables.name as TableName,
defaults.name AS DefaultName,
Indexes.IndexName,
cols.name AS ColumnName
INTO #Defaults
FROM sysobjects defaults
INNER JOIN syscolumns cols
ON defaults.ID=cols.cdefault
INNER JOIN sysobjects tables
ON tables.id=cols.id
INNER JOIN
(select sysindexes.name AS IndexName,
Tables.Name AS TableName,
Tables.id as TableID,
cols.Name AS ColumnName
from sysindexes
INNER JOIN sysobjects Tables
ON sysindexes.id=tables.id
INNER JOIN sysindexkeys k
on sysindexes.id=k.id
AND sysindexes.indid=k.indid
INNER JOIN syscolumns cols
ON k.id=cols.id
AND k.colid=cols.colid
where cols.name='rowguid') Indexes
ON Indexes.TableID=tables.id
where cols.name='rowguid'
DECLARE @.DefaultName varchar(100), @.IndexName varchar(100), @.TableName varchar(100), @.ColName varchar(50)
DECLARE cur CURSOR for SELECT * FROM #Defaults
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.DefaultName)
EXECUTE ('DROP INDEX ' + @.TableName + '.' + @.IndexName)
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP COLUMN rowguid')
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
END
CLOSE cur
DEALLOCATE cur
GO|||I've been trying to delete the conflict tables left over after EXEC sp_removereplication
Your script did it!
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sysmergearticles'
This tells me that some replication data is also copied to the develop database.
Can anyone tell me how I can clean this data, or if I should copy the databse in a different way. (Now I create a new databse and do an import of the tables and stored procedures)You could try to remove replication by going under Tools(in EM)-->Replication-->Disable publishing,Distri...If it allows you to do so then it's fine.Or else you could try to first set the replication on your Test system and then remove ot by using the Tools-->Replication.
Or you may try the script below:
USE [DBNAME]
GO
-- Drop all replication triggers from the database
PRINT 'Drop all replication triggers from the database'
SELECT trigs.name AS TriggerName,
trigs.id AS TriggerID,
tables.name AS TableName
INTO #Triggers
FROM sysobjects trigs
INNER JOIN sysobjects tables
ON trigs.parent_obj=tables.id
where trigs.category=2 and trigs.xtype='TR'
DECLARE @.TriggerName varchar(100), @.TriggerID INT, @.TableName varchar(100)
DECLARE cur CURSOR for SELECT * FROM #Triggers
OPEN cur
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP TRIGGER ' + @.TriggerName)
FETCH NEXT FROM cur INTO @.TriggerName, @.TriggerID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication constraints from the database
PRINT 'Drop all replication constraints from the database'
DECLARE @.ConstName varchar(100), @.ConstID INT, @.TableName varchar(100)
SELECT CONST.name AS ConstName,
CONST.id AS ConstID,
tables.name AS TableName
INTO #Constraints
FROM sysobjects CONST
INNER JOIN sysobjects tables
ON CONST.parent_obj=tables.id
where CONST.xtype='C'
DECLARE cur CURSOR for SELECT * FROM #Constraints
OPEN cur
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER Table ' + @.TableName + ' DROP CONSTRAINT ' + @.ConstName)
FETCH NEXT FROM cur INTO @.ConstName, @.ConstID, @.TableName
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User tables
PRINT 'Drop all replication User tables'
DECLARE @.TableName varchar(100), @.TableID INT
SELECT Tables.name AS ConstName,
Tables.id AS ConstID
INTO #Tables
FROM sysobjects Tables
where Tables.xtype='U' AND Status < 0 AND category=2050
DECLARE cur CURSOR for SELECT * FROM #Tables
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.TableID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Table ' + @.TableName)
FETCH NEXT FROM cur INTO @.TableName, @.TableID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User procedures
PRINT 'Drop all replication User procedures'
DECLARE @.ProcName varchar(100), @.ProcID INT
SELECT Procs.name AS ConstName,
Procs.id AS ConstID
INTO #Procedures
FROM sysobjects Procs
where procs.xtype='P' AND Status < 0
DECLARE cur CURSOR for SELECT * FROM #Procedures
OPEN cur
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP Procedure ' + @.ProcName)
FETCH NEXT FROM cur INTO @.ProcName, @.ProcID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication User Views
PRINT 'Drop all replication User Views'
DECLARE @.ViewName varchar(100), @.ViewID INT
SELECT MyViews.name AS ConstName,
MyViews.id AS ConstID
INTO #Views
FROM sysobjects MyViews
where MyViews.xtype='V' AND Status < 0 AND Name NOT LIKE 'sys%'
DECLARE cur CURSOR for SELECT * FROM #Views
OPEN cur
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('DROP View ' + @.ViewName)
FETCH NEXT FROM cur INTO @.ViewName, @.ViewID
END
CLOSE cur
DEALLOCATE cur
GO
-- Drop all replication rowguids, defaults and indexes
PRINT 'Drop all replication rowguids, defaults and indexes'
SELECT tables.name as TableName,
defaults.name AS DefaultName,
Indexes.IndexName,
cols.name AS ColumnName
INTO #Defaults
FROM sysobjects defaults
INNER JOIN syscolumns cols
ON defaults.ID=cols.cdefault
INNER JOIN sysobjects tables
ON tables.id=cols.id
INNER JOIN
(select sysindexes.name AS IndexName,
Tables.Name AS TableName,
Tables.id as TableID,
cols.Name AS ColumnName
from sysindexes
INNER JOIN sysobjects Tables
ON sysindexes.id=tables.id
INNER JOIN sysindexkeys k
on sysindexes.id=k.id
AND sysindexes.indid=k.indid
INNER JOIN syscolumns cols
ON k.id=cols.id
AND k.colid=cols.colid
where cols.name='rowguid') Indexes
ON Indexes.TableID=tables.id
where cols.name='rowguid'
DECLARE @.DefaultName varchar(100), @.IndexName varchar(100), @.TableName varchar(100), @.ColName varchar(50)
DECLARE cur CURSOR for SELECT * FROM #Defaults
OPEN cur
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
WHILE @.@.FETCH_STATUS=0
BEGIN
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.DefaultName)
EXECUTE ('DROP INDEX ' + @.TableName + '.' + @.IndexName)
EXECUTE ('ALTER TABLE ' + @.TableName + ' DROP COLUMN rowguid')
FETCH NEXT FROM cur INTO @.TableName, @.DefaultName, @.IndexName, @.ColName
END
CLOSE cur
DEALLOCATE cur
GO|||I've been trying to delete the conflict tables left over after EXEC sp_removereplication
Your script did it!
Tuesday, March 20, 2012
Cisco
Is anyone using Cisco ICM and sql server 7.0 ,need some assistance with acount replication on the advanced services web deploymentIm all alone ,nobody here beside me,nobody to deride me...
Is no-one using ICM?
Is no-one using ICM?
Monday, March 19, 2012
Choosing replication type
Hello!
I'm trying to put together replication for a small application that
works like this: The central database contains data that is to be
copied to several laptops. The laptops are going to be used on
inspections of real-estate all over this city. When the user is
finished with his work, he wants his data transferred/updated to the
main database. This updating only concerns a few columns in a few
tables. NOT the whole database.
I figured Snaphot Replication was a good idea, but this only work one
way - to the laptop, with the database structure and all the data.
When data is going back to the central database, snapshot replication
will only try to add rows, as it doesn't know which rows to update.
Merge Replication will update all of the data both ways, and that is
not "allowed" in this context.
Any good suggestions?
Thanx!
Dagfinn Rosnes
Use Merge replication - only replicate the tables and columns that the
laptops will be changing.
"Dagfinn Rosnes" wrote:
> Hello!
> I'm trying to put together replication for a small application that
> works like this: The central database contains data that is to be
> copied to several laptops. The laptops are going to be used on
> inspections of real-estate all over this city. When the user is
> finished with his work, he wants his data transferred/updated to the
> main database. This updating only concerns a few columns in a few
> tables. NOT the whole database.
> I figured Snaphot Replication was a good idea, but this only work one
> way - to the laptop, with the database structure and all the data.
> When data is going back to the central database, snapshot replication
> will only try to add rows, as it doesn't know which rows to update.
> Merge Replication will update all of the data both ways, and that is
> not "allowed" in this context.
> Any good suggestions?
> Thanx!
> Dagfinn Rosnes
>
|||Clarify my previous post:
Use a "one-way" merge replication
http://www.microsoft.com/sql/techinf...t/mergerep.asp
"Dagfinn Rosnes" wrote:
> Hello!
> I'm trying to put together replication for a small application that
> works like this: The central database contains data that is to be
> copied to several laptops. The laptops are going to be used on
> inspections of real-estate all over this city. When the user is
> finished with his work, he wants his data transferred/updated to the
> main database. This updating only concerns a few columns in a few
> tables. NOT the whole database.
> I figured Snaphot Replication was a good idea, but this only work one
> way - to the laptop, with the database structure and all the data.
> When data is going back to the central database, snapshot replication
> will only try to add rows, as it doesn't know which rows to update.
> Merge Replication will update all of the data both ways, and that is
> not "allowed" in this context.
> Any good suggestions?
> Thanx!
> Dagfinn Rosnes
>
I'm trying to put together replication for a small application that
works like this: The central database contains data that is to be
copied to several laptops. The laptops are going to be used on
inspections of real-estate all over this city. When the user is
finished with his work, he wants his data transferred/updated to the
main database. This updating only concerns a few columns in a few
tables. NOT the whole database.
I figured Snaphot Replication was a good idea, but this only work one
way - to the laptop, with the database structure and all the data.
When data is going back to the central database, snapshot replication
will only try to add rows, as it doesn't know which rows to update.
Merge Replication will update all of the data both ways, and that is
not "allowed" in this context.
Any good suggestions?
Thanx!
Dagfinn Rosnes
Use Merge replication - only replicate the tables and columns that the
laptops will be changing.
"Dagfinn Rosnes" wrote:
> Hello!
> I'm trying to put together replication for a small application that
> works like this: The central database contains data that is to be
> copied to several laptops. The laptops are going to be used on
> inspections of real-estate all over this city. When the user is
> finished with his work, he wants his data transferred/updated to the
> main database. This updating only concerns a few columns in a few
> tables. NOT the whole database.
> I figured Snaphot Replication was a good idea, but this only work one
> way - to the laptop, with the database structure and all the data.
> When data is going back to the central database, snapshot replication
> will only try to add rows, as it doesn't know which rows to update.
> Merge Replication will update all of the data both ways, and that is
> not "allowed" in this context.
> Any good suggestions?
> Thanx!
> Dagfinn Rosnes
>
|||Clarify my previous post:
Use a "one-way" merge replication
http://www.microsoft.com/sql/techinf...t/mergerep.asp
"Dagfinn Rosnes" wrote:
> Hello!
> I'm trying to put together replication for a small application that
> works like this: The central database contains data that is to be
> copied to several laptops. The laptops are going to be used on
> inspections of real-estate all over this city. When the user is
> finished with his work, he wants his data transferred/updated to the
> main database. This updating only concerns a few columns in a few
> tables. NOT the whole database.
> I figured Snaphot Replication was a good idea, but this only work one
> way - to the laptop, with the database structure and all the data.
> When data is going back to the central database, snapshot replication
> will only try to add rows, as it doesn't know which rows to update.
> Merge Replication will update all of the data both ways, and that is
> not "allowed" in this context.
> Any good suggestions?
> Thanx!
> Dagfinn Rosnes
>
Thursday, February 16, 2012
Checking for replication when applying schema changes
All
Is there some simple way to check if a database is being replicated that I
can use in a script? I have 2 copies of a production database, one under
replication and one not, and I would like to have any schema change scripts
check for replication to see which action to take. E.g.
<script>
If <database is replicating>
sp_addreplcolumn ...
Else
Alter Table...
</script>
TIA
Ron Lounsbury
sp_dboption 'pubs','published'
GO
sp_dboption 'pubs','merge publish'
GO
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
> All
> Is there some simple way to check if a database is being replicated that I
> can use in a script? I have 2 copies of a production database, one under
> replication and one not, and I would like to have any schema change
> scripts check for replication to see which action to take. E.g.
> <script>
> If <database is replicating>
> sp_addreplcolumn ...
> Else
> Alter Table...
> </script>
> TIA
> Ron Lounsbury
>
|||Hilary
Thanks for the reply. Unfortunately, when I run that against a database
that is participating in replication and one that is not, both give me a
result for the "CurrentSetting" of OFF. This occurs for both "Merge
Publish" and "Publish". Is there something I am missing? I am running SQL
Server 2000, SP 3a (with some hotfixes).
Thanks,
Ron Lounsbury
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
> sp_dboption 'pubs','published'
> GO
> sp_dboption 'pubs','merge publish'
> GO
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
>
|||If it returns off it means these databases are not enabled for replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hilary
> Thanks for the reply. Unfortunately, when I run that against a database
> that is participating in replication and one that is not, both give me a
> result for the "CurrentSetting" of OFF. This occurs for both "Merge
> Publish" and "Publish". Is there something I am missing? I am running
> SQL Server 2000, SP 3a (with some hotfixes).
> Thanks,
> Ron Lounsbury
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
>
|||Hilary
When I run this script in Query Analyzer:
USE NITSS2kDev
exec sp_dboption 'pubs','merge publish'
GO
use NITSS2kdeployed
exec sp_dboption 'pubs','merge publish'
GO
I get these results:
OptionName CurrentSetting
-- --
merge publish off
OptionName CurrentSetting
-- --
merge publish off
NITSS2kDev is actively replicating with 2 other databases, and one of the
replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
The script was run on the Publishing server.
Thanks,
Ron L
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
> If it returns off it means these databases are not enabled for
> replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
>
|||Oh Man, My Stupid. I don't know where my brain was yesterday. When I put
the right database name in the call it works fine.
Thanks,
Ron L
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:OtXBmGqUGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hilary
> When I run this script in Query Analyzer:
> USE NITSS2kDev
> exec sp_dboption 'pubs','merge publish'
> GO
> use NITSS2kdeployed
> exec sp_dboption 'pubs','merge publish'
> GO
> I get these results:
> OptionName CurrentSetting
> -- --
> merge publish off
> OptionName CurrentSetting
> -- --
> merge publish off
> NITSS2kDev is actively replicating with 2 other databases, and one of the
> replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
> The script was run on the Publishing server.
> Thanks,
> Ron L
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
>
Is there some simple way to check if a database is being replicated that I
can use in a script? I have 2 copies of a production database, one under
replication and one not, and I would like to have any schema change scripts
check for replication to see which action to take. E.g.
<script>
If <database is replicating>
sp_addreplcolumn ...
Else
Alter Table...
</script>
TIA
Ron Lounsbury
sp_dboption 'pubs','published'
GO
sp_dboption 'pubs','merge publish'
GO
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
> All
> Is there some simple way to check if a database is being replicated that I
> can use in a script? I have 2 copies of a production database, one under
> replication and one not, and I would like to have any schema change
> scripts check for replication to see which action to take. E.g.
> <script>
> If <database is replicating>
> sp_addreplcolumn ...
> Else
> Alter Table...
> </script>
> TIA
> Ron Lounsbury
>
|||Hilary
Thanks for the reply. Unfortunately, when I run that against a database
that is participating in replication and one that is not, both give me a
result for the "CurrentSetting" of OFF. This occurs for both "Merge
Publish" and "Publish". Is there something I am missing? I am running SQL
Server 2000, SP 3a (with some hotfixes).
Thanks,
Ron Lounsbury
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
> sp_dboption 'pubs','published'
> GO
> sp_dboption 'pubs','merge publish'
> GO
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
>
|||If it returns off it means these databases are not enabled for replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hilary
> Thanks for the reply. Unfortunately, when I run that against a database
> that is participating in replication and one that is not, both give me a
> result for the "CurrentSetting" of OFF. This occurs for both "Merge
> Publish" and "Publish". Is there something I am missing? I am running
> SQL Server 2000, SP 3a (with some hotfixes).
> Thanks,
> Ron Lounsbury
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
>
|||Hilary
When I run this script in Query Analyzer:
USE NITSS2kDev
exec sp_dboption 'pubs','merge publish'
GO
use NITSS2kdeployed
exec sp_dboption 'pubs','merge publish'
GO
I get these results:
OptionName CurrentSetting
-- --
merge publish off
OptionName CurrentSetting
-- --
merge publish off
NITSS2kDev is actively replicating with 2 other databases, and one of the
replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
The script was run on the Publishing server.
Thanks,
Ron L
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
> If it returns off it means these databases are not enabled for
> replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
>
|||Oh Man, My Stupid. I don't know where my brain was yesterday. When I put
the right database name in the call it works fine.
Thanks,
Ron L
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:OtXBmGqUGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hilary
> When I run this script in Query Analyzer:
> USE NITSS2kDev
> exec sp_dboption 'pubs','merge publish'
> GO
> use NITSS2kdeployed
> exec sp_dboption 'pubs','merge publish'
> GO
> I get these results:
> OptionName CurrentSetting
> -- --
> merge publish off
> OptionName CurrentSetting
> -- --
> merge publish off
> NITSS2kDev is actively replicating with 2 other databases, and one of the
> replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
> The script was run on the Publishing server.
> Thanks,
> Ron L
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
>
Tuesday, February 14, 2012
Checking / Opinion ?
I am taking these steps in a test environment;
Publishing database
1. Changed identity columns = Not for Replication (Hilary's script)
2. Scripted all Triggers & changed to "Not for Replication" (Is there a method to do this with a script?)
3. Added Primary Keys where not present as follows;
Alter Table x
add pk_col int identity(1,1) NOT FOR REPLICATION
Alter Table x
add constraint x_tbl_repl
primary key(pk_col)
4. Backed up Publishing Database
5. Restored Publishing Database with a new name = Subscribing Database
6. Setup Transactional continuous replication from Publishing Database to Subscribing Database
The reason I did everything in the publishing database is so that it's all set in the Subscribing database.....Make sense?
I am going to have a production oltp database that replicates all transactions to my reporting database. I also want to think of my reporting database as sort of a fall back position. If anything goes wrong in the oltp publishing database, everything resides in the subscribing db.
It is my understanding that I wanted the Identity columns in the subscribing database to just be written to, not incremented, therefore, the necessity of the Not for Replication.
The same goes for triggers in the subscribing database, I don't want them firing when replication occurs, therefore, the Not for Replication.
I don't have any inserts as follows;
Insert into Table A (Table A may have a newly added pk_col )
Select * from Table B (Table B may have a newly added pk_col )
therefore, I should not have any problems with my stored procedures.
Do I have it all covered? Am I on the right track? I really want to get this implemented, and correctly, to get this monkey off my back.
Any comments will certainly be appreciated!
This is not the standard way of deploying subscribers, but it does work. I just tried it.
The problem is when you go to do a resync. When a no-sync subscription needs to be re-initialized you must drop and recreate your subscription. You have the potential of having data consistency problems with your approach.
Also, it sounds like you might be thinking you are implementing bi-directional transactional replication with your method. You aren't. Its not clear from your post if you are or not. But the "fall back position" could be interpreted as this.
If you fail over to your Subscriber you will have to manually resync your Publisher with your Subscriber when it comes back on line.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I am only doing Transactional Replication in one direction. What I want is
to have a production OLTP database that replicates to a reporting database.
The reason I set everything up in the test production database is so that
these databases mirror one another from the start, and hoping I would not
miss anything.
If the schema needs to change in the production database, I intend to make
the same change in the subscriber. We have change management, where the
scripts are sent to me and I execute them over production. I am hoping that
it will just be a matter of running the scripts to create the objects
(views, sp's) in both databases to keep them in sync object wise.
I don't understand what you mean that I may face problems when doing a
resync, what am I doing wrong, or what is wrong with my approach to setting
up a reporting database, how would you approach setting up a reporting
database that is replicated to from the production database?
Am I out in left field making this project more difficult than it needs to
be?
For fall back I was thinking that since all transactions are being
replicated from production, if something went wrong in the production db, we
could just point the users to the subscribing database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:B8A63257-FE63-4673-AD55-9494C453404B@.microsoft.com...
> This is not the standard way of deploying subscribers, but it does work. I
just tried it.
> The problem is when you go to do a resync. When a no-sync subscription
needs to be re-initialized you must drop and recreate your subscription. You
have the potential of having data consistency problems with your approach.
> Also, it sounds like you might be thinking you are implementing
bi-directional transactional replication with your method. You aren't. Its
not clear from your post if you are or not. But the "fall back position"
could be interpreted as this.
> If you fail over to your Subscriber you will have to manually resync your
Publisher with your Subscriber when it comes back on line.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
Publishing database
1. Changed identity columns = Not for Replication (Hilary's script)
2. Scripted all Triggers & changed to "Not for Replication" (Is there a method to do this with a script?)
3. Added Primary Keys where not present as follows;
Alter Table x
add pk_col int identity(1,1) NOT FOR REPLICATION
Alter Table x
add constraint x_tbl_repl
primary key(pk_col)
4. Backed up Publishing Database
5. Restored Publishing Database with a new name = Subscribing Database
6. Setup Transactional continuous replication from Publishing Database to Subscribing Database
The reason I did everything in the publishing database is so that it's all set in the Subscribing database.....Make sense?
I am going to have a production oltp database that replicates all transactions to my reporting database. I also want to think of my reporting database as sort of a fall back position. If anything goes wrong in the oltp publishing database, everything resides in the subscribing db.
It is my understanding that I wanted the Identity columns in the subscribing database to just be written to, not incremented, therefore, the necessity of the Not for Replication.
The same goes for triggers in the subscribing database, I don't want them firing when replication occurs, therefore, the Not for Replication.
I don't have any inserts as follows;
Insert into Table A (Table A may have a newly added pk_col )
Select * from Table B (Table B may have a newly added pk_col )
therefore, I should not have any problems with my stored procedures.
Do I have it all covered? Am I on the right track? I really want to get this implemented, and correctly, to get this monkey off my back.
Any comments will certainly be appreciated!
This is not the standard way of deploying subscribers, but it does work. I just tried it.
The problem is when you go to do a resync. When a no-sync subscription needs to be re-initialized you must drop and recreate your subscription. You have the potential of having data consistency problems with your approach.
Also, it sounds like you might be thinking you are implementing bi-directional transactional replication with your method. You aren't. Its not clear from your post if you are or not. But the "fall back position" could be interpreted as this.
If you fail over to your Subscriber you will have to manually resync your Publisher with your Subscriber when it comes back on line.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I am only doing Transactional Replication in one direction. What I want is
to have a production OLTP database that replicates to a reporting database.
The reason I set everything up in the test production database is so that
these databases mirror one another from the start, and hoping I would not
miss anything.
If the schema needs to change in the production database, I intend to make
the same change in the subscriber. We have change management, where the
scripts are sent to me and I execute them over production. I am hoping that
it will just be a matter of running the scripts to create the objects
(views, sp's) in both databases to keep them in sync object wise.
I don't understand what you mean that I may face problems when doing a
resync, what am I doing wrong, or what is wrong with my approach to setting
up a reporting database, how would you approach setting up a reporting
database that is replicated to from the production database?
Am I out in left field making this project more difficult than it needs to
be?
For fall back I was thinking that since all transactions are being
replicated from production, if something went wrong in the production db, we
could just point the users to the subscribing database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:B8A63257-FE63-4673-AD55-9494C453404B@.microsoft.com...
> This is not the standard way of deploying subscribers, but it does work. I
just tried it.
> The problem is when you go to do a resync. When a no-sync subscription
needs to be re-initialized you must drop and recreate your subscription. You
have the potential of having data consistency problems with your approach.
> Also, it sounds like you might be thinking you are implementing
bi-directional transactional replication with your method. You aren't. Its
not clear from your post if you are or not. But the "fall back position"
could be interpreted as this.
> If you fail over to your Subscriber you will have to manually resync your
Publisher with your Subscriber when it comes back on line.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
checkign replication percentage
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs
If you are talking about the initial snapshot, then you can keep refreshing
the history of the distribution agent, as it shows the number of batches it
applied to the subscriber, along with the number of rows/batch. If know how
many rows there are on the publishing table, then the distribution agent
history gives you a pretty good idea of how many more rows to go.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:C637A6F1-5060-4247-8F63-FABA4FC5B33D@.microsoft.com...
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs
If you are talking about the initial snapshot, then you can keep refreshing
the history of the distribution agent, as it shows the number of batches it
applied to the subscriber, along with the number of rows/batch. If know how
many rows there are on the publishing table, then the distribution agent
history gives you a pretty good idea of how many more rows to go.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:C637A6F1-5060-4247-8F63-FABA4FC5B33D@.microsoft.com...
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs
checkign replication percentage
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgsIf you are talking about the initial snapshot, then you can keep refreshing
the history of the distribution agent, as it shows the number of batches it
applied to the subscriber, along with the number of rows/batch. If know how
many rows there are on the publishing table, then the distribution agent
history gives you a pretty good idea of how many more rows to go.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:C637A6F1-5060-4247-8F63-FABA4FC5B33D@.microsoft.com...
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgsIf you are talking about the initial snapshot, then you can keep refreshing
the history of the distribution agent, as it shows the number of batches it
applied to the subscriber, along with the number of rows/batch. If know how
many rows there are on the publishing table, then the distribution agent
history gives you a pretty good idea of how many more rows to go.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:C637A6F1-5060-4247-8F63-FABA4FC5B33D@.microsoft.com...
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs
checkign replication percentage
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgsIf you are talking about the initial snapshot, then you can keep refreshing
the history of the distribution agent, as it shows the number of batches it
applied to the subscriber, along with the number of rows/batch. If know how
many rows there are on the publishing table, then the distribution agent
history gives you a pretty good idea of how many more rows to go.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:C637A6F1-5060-4247-8F63-FABA4FC5B33D@.microsoft.com...
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgsIf you are talking about the initial snapshot, then you can keep refreshing
the history of the distribution agent, as it shows the number of batches it
applied to the subscriber, along with the number of rows/batch. If know how
many rows there are on the publishing table, then the distribution agent
history gives you a pretty good idea of how many more rows to go.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:C637A6F1-5060-4247-8F63-FABA4FC5B33D@.microsoft.com...
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs
Subscribe to:
Posts (Atom)