My situation:
I have a DB hosted by a 3rd party ISP. I want to replicate that DB locally
with in my office on our local server, mainly for backup purposes. I then
want to replicate that backup DB to a development DB for testing purposes.
Basically I'll have a LIVE DB hosted at the ISP. A backup version of the
LIVE
DB on our local server (peace of mind really) Then a development DB for
testing
purposes.
The Publisher will be the DB at the ISP.
Question 1. Is this a doable or a reasonable solution. I figured
transaction replication
from LIVE to BACKUP. Then Snapshot from BACKUP to
DEVELOPMENT.
It isn't absolutely necessary to have the most recent
data on the development db
since I will only be using it for testing purposes. But
would be nice to draw from
real data, almost live situations.
Question 2. Can a DB or server be both a Publisher and a Subscriber in this
case the BACKUP DB
would be both?
Thanks,
George
George,
this is possible - it is known as republishing. In your case my preference
would be to use merge between the live system and the Backup system, as it
is ideal for this sort of high latency situation. This would allow you to
have the Backup system as the publisher (with merge, the
publisher/subscriber metaphor doesn't especially hold), as you can then
control the configuration settings locally, and I'd have the live system as
a push subscriber. The other system is used as a testing system and the data
could become incompatible with replication from the Backup system, so I
wouldn't use replication to transfer to this box. Instead, I'd use a backup
and restore of the whole database - Backup to Test.
HTH,
Paul Ibison
|||I'd use transactional as
1) it offers better performance
2) it doesn't add a GUID key to every table.
The con of transactional replication is that you need a PK on every table you wish to replicate, and with republishing transactional is more sensitive to schema changes. By more sensitive I mean you have to drop your publications and subscriptions, make c
hanges and re-build again.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
sql
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Replication problem in SQL Server 2000
Hi all SQL Server experts!
I have created replication on my two servers (production and backup servers). I used wizards to create it and then generated SQL script for deleting and creating replication. For another task I came across the command sp_removedbreplication 'dbname' and I tried to use that for removing replication more easily on the servers. It seemed to not remove everything on the production server and whenever I install replication now, either by using wizards or my generated SQL scripts I get an error icon (red circle with a black X in it) on my Replication Monitor all the way down to Publisher and the publication named with the computer name. Also this icon is on Agents and Log Reader Agents but not on the actual agent.
Something must have happened when using sp_removedbreplication 'dbname' and I can't fix it. The replication works properly but i wish to not have the error icons.
Does anyone know how the error icons can be removed?
Best,
/M
The cleanest way and supported way is to drop the distributor and recreate it from scratch. However you can also look at the following tables in distribution database and delete any rows that reference your deleted publications (but take a backup of the distribution database first, and note that manually deleting these tables is unsupported):
MSpublications
MSsubscriptions
MSarticles
|||I have checked these tables and they don't have anything that don't exist in them.
I have also dropped everything that has to do with replication and also the distribution database to use my scripts and install replication again. The replication works fines but there are still error icons on the folders. Only on the folders though and not on the actual items, e.g. agents. I am thinking of reinstalling the SQL Server. Might it be because SQL Server has replaced the actual icons with error icons and "lost" the orignial ones?
My scripts for installing replication has always worked but it is after I have used sp_removedbreplication on the distributor/publisher that these icons occur when I install replication, including using the wizards.
Best,
/M
When deleting subscription, it's replication job may still remain. You can find it in SQL Server Agent Jobs list (in Enterprise Manager: [DB]->Management->SQL Server Agent->Jobs).
Name of the job consists of Publisher and Subscriber names. When you re-create a subscription, probably same name is generated, and job creation fails.
Use sp_delete_job stored procedure to delete the job.
|||Thank you very much. That solved my problem.
Best,
/M
Replication problem in SQL Server 2000
Hi all SQL Server experts!
I have created replication on my two servers (production and backup servers). I used wizards to create it and then generated SQL script for deleting and creating replication. For another task I came across the command sp_removedbreplication 'dbname' and I tried to use that for removing replication more easily on the servers. It seemed to not remove everything on the production server and whenever I install replication now, either by using wizards or my generated SQL scripts I get an error icon (red circle with a black X in it) on my Replication Monitor all the way down to Publisher and the publication named with the computer name. Also this icon is on Agents and Log Reader Agents but not on the actual agent.
Something must have happened when using sp_removedbreplication 'dbname' and I can't fix it. The replication works properly but i wish to not have the error icons.
Does anyone know how the error icons can be removed?
Best,
/M
The cleanest way and supported way is to drop the distributor and recreate it from scratch. However you can also look at the following tables in distribution database and delete any rows that reference your deleted publications (but take a backup of the distribution database first, and note that manually deleting these tables is unsupported):
MSpublications
MSsubscriptions
MSarticles
|||I have checked these tables and they don't have anything that don't exist in them.
I have also dropped everything that has to do with replication and also the distribution database to use my scripts and install replication again. The replication works fines but there are still error icons on the folders. Only on the folders though and not on the actual items, e.g. agents. I am thinking of reinstalling the SQL Server. Might it be because SQL Server has replaced the actual icons with error icons and "lost" the orignial ones?
My scripts for installing replication has always worked but it is after I have used sp_removedbreplication on the distributor/publisher that these icons occur when I install replication, including using the wizards.
Best,
/M
When deleting subscription, it's replication job may still remain. You can find it in SQL Server Agent Jobs list (in Enterprise Manager: [DB]->Management->SQL Server Agent->Jobs).
Name of the job consists of Publisher and Subscriber names. When you re-create a subscription, probably same name is generated, and job creation fails.
Use sp_delete_job stored procedure to delete the job.
|||Thank you very much. That solved my problem.
Best,
/M
Friday, March 9, 2012
Replication on MSDE2000 and db size problem
Hi,
We have following scenario:
2 PC on WinXP + MSDE2000 + SP4 with production db. Secondary PC works as
failover backup. DBs are connected by merge replication where backup is as
subscriber.
The problem is related to one table with binary column (2K) and its growing
size (over 100MB per day).
sp_spaceused product_map
name rows reserved data index_size unused
Product_map 24238 1056648KB 130448KB 13520KB 912640KB
It seems that on insert rows server allocates more space than need. This
problem occures only when system works on publisher db. It is ok when I
switch it to backup PC.
I have the same systems on W2k + MSDE2000 + SP3a + merge replication and
don't observe the problem like this.
could you help me how to diagnose it more deeply and solve the problem?
Janek
A couple of points 1) are you using text in row?
2) Are you sure the data in this column is the same on the publisher and
subscriber. Depending on how you update your text or image data it may not
be replicated.
Hilary Cotter
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
"Janek" <Janek@.discussions.microsoft.com> wrote in message
news:456DC0D6-FEDC-43BF-8A22-B6CE22069464@.microsoft.com...
> Hi,
> We have following scenario:
> 2 PC on WinXP + MSDE2000 + SP4 with production db. Secondary PC works as
> failover backup. DBs are connected by merge replication where backup is as
> subscriber.
> The problem is related to one table with binary column (2K) and its
> growing
> size (over 100MB per day).
> sp_spaceused product_map
> name rows reserved data index_size
> unused
> Product_map 24238 1056648KB 130448KB 13520KB 912640KB
> It seems that on insert rows server allocates more space than need. This
> problem occures only when system works on publisher db. It is ok when I
> switch it to backup PC.
> I have the same systems on W2k + MSDE2000 + SP3a + merge replication and
> don't observe the problem like this.
> could you help me how to diagnose it more deeply and solve the problem?
> Janek
|||1) data type for table is: int, smallint, int, char(11), smallint, smallint,
binary(2001), bit, datetime, int
2) All data is replicating, I can see it on reports on each machines. From
sp_spaceused report I see almost 1GB of unused space.
I know only one workaround:
dbcc dbreindex (.... + shrink db - a few cycles makes db to similar
backup db size.
At the moment system is working on backup PC without problem and new data is
refreshed to publisher.
Janek
"Hilary Cotter" wrote:
> A couple of points 1) are you using text in row?
> 2) Are you sure the data in this column is the same on the publisher and
> subscriber. Depending on how you update your text or image data it may not
> be replicated.
> --
> Hilary Cotter
> 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
>
> "Janek" <Janek@.discussions.microsoft.com> wrote in message
> news:456DC0D6-FEDC-43BF-8A22-B6CE22069464@.microsoft.com...
>
>
We have following scenario:
2 PC on WinXP + MSDE2000 + SP4 with production db. Secondary PC works as
failover backup. DBs are connected by merge replication where backup is as
subscriber.
The problem is related to one table with binary column (2K) and its growing
size (over 100MB per day).
sp_spaceused product_map
name rows reserved data index_size unused
Product_map 24238 1056648KB 130448KB 13520KB 912640KB
It seems that on insert rows server allocates more space than need. This
problem occures only when system works on publisher db. It is ok when I
switch it to backup PC.
I have the same systems on W2k + MSDE2000 + SP3a + merge replication and
don't observe the problem like this.
could you help me how to diagnose it more deeply and solve the problem?
Janek
A couple of points 1) are you using text in row?
2) Are you sure the data in this column is the same on the publisher and
subscriber. Depending on how you update your text or image data it may not
be replicated.
Hilary Cotter
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
"Janek" <Janek@.discussions.microsoft.com> wrote in message
news:456DC0D6-FEDC-43BF-8A22-B6CE22069464@.microsoft.com...
> Hi,
> We have following scenario:
> 2 PC on WinXP + MSDE2000 + SP4 with production db. Secondary PC works as
> failover backup. DBs are connected by merge replication where backup is as
> subscriber.
> The problem is related to one table with binary column (2K) and its
> growing
> size (over 100MB per day).
> sp_spaceused product_map
> name rows reserved data index_size
> unused
> Product_map 24238 1056648KB 130448KB 13520KB 912640KB
> It seems that on insert rows server allocates more space than need. This
> problem occures only when system works on publisher db. It is ok when I
> switch it to backup PC.
> I have the same systems on W2k + MSDE2000 + SP3a + merge replication and
> don't observe the problem like this.
> could you help me how to diagnose it more deeply and solve the problem?
> Janek
|||1) data type for table is: int, smallint, int, char(11), smallint, smallint,
binary(2001), bit, datetime, int
2) All data is replicating, I can see it on reports on each machines. From
sp_spaceused report I see almost 1GB of unused space.
I know only one workaround:
dbcc dbreindex (.... + shrink db - a few cycles makes db to similar
backup db size.
At the moment system is working on backup PC without problem and new data is
refreshed to publisher.
Janek
"Hilary Cotter" wrote:
> A couple of points 1) are you using text in row?
> 2) Are you sure the data in this column is the same on the publisher and
> subscriber. Depending on how you update your text or image data it may not
> be replicated.
> --
> Hilary Cotter
> 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
>
> "Janek" <Janek@.discussions.microsoft.com> wrote in message
> news:456DC0D6-FEDC-43BF-8A22-B6CE22069464@.microsoft.com...
>
>
Replication on Backup
Hey all...here s my problem, let s see if u can help me please ! :
I have a DB with some tables published in X server, i made a backup of that
database, and restore that backup on another server ( Y ) , but it s still
beeing locked for the replication service, but the Y server doesnt have that
service active... so.. when i want to alter a table, i had the "YOU CANT
DROP THE TABLE BECAUSE IT S BEEING USING FOR REPLICATION.. " ( or something
like that ) .. how can i drop all suscriptions and publications services
from that database ?
tnx!
Try sp_removedbreplication.
If this doesn't remove everything you want, there is a stored procedure to
do this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanx Paul !! .. so, after that, i have to delete rowID fields and i have
the original DB right?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uU6$xoqGFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Try sp_removedbreplication.
> If this doesn't remove everything you want, there is a stored procedure to
> do this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Is that a Enterprise Manager's BUG ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uU6$xoqGFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Try sp_removedbreplication.
> If this doesn't remove everything you want, there is a stored procedure to
> do this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Do you mean the RowGuids? They'll only be there if you're using some sort of
updating subscriber or merge replication. They'll need removing manually,
alson with any associated check constraints if you have automatic range
management set up.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I don't know if I'd call it a bug as such, just a little messy perhaps.
Several things may be left behind - check constraints, rowguids, conflict
tables etc. In some cases there may be user code that depends on these
objects eg the extra replication guid columns, in which case always
automatically removing them would be a disaster. Anyway, there are some
procedures knocking around which'll remove these extra bits (Hilary has
posted up a version on tis newsgroup a few times). Also, you'll like SQL
Server 2005 - there is the option to do a 'hard' version of
sp_removedbreplication which automatically removes all this replication
objects.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have a DB with some tables published in X server, i made a backup of that
database, and restore that backup on another server ( Y ) , but it s still
beeing locked for the replication service, but the Y server doesnt have that
service active... so.. when i want to alter a table, i had the "YOU CANT
DROP THE TABLE BECAUSE IT S BEEING USING FOR REPLICATION.. " ( or something
like that ) .. how can i drop all suscriptions and publications services
from that database ?
tnx!
Try sp_removedbreplication.
If this doesn't remove everything you want, there is a stored procedure to
do this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanx Paul !! .. so, after that, i have to delete rowID fields and i have
the original DB right?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uU6$xoqGFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Try sp_removedbreplication.
> If this doesn't remove everything you want, there is a stored procedure to
> do this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Is that a Enterprise Manager's BUG ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uU6$xoqGFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Try sp_removedbreplication.
> If this doesn't remove everything you want, there is a stored procedure to
> do this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Do you mean the RowGuids? They'll only be there if you're using some sort of
updating subscriber or merge replication. They'll need removing manually,
alson with any associated check constraints if you have automatic range
management set up.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I don't know if I'd call it a bug as such, just a little messy perhaps.
Several things may be left behind - check constraints, rowguids, conflict
tables etc. In some cases there may be user code that depends on these
objects eg the extra replication guid columns, in which case always
automatically removing them would be a disaster. Anyway, there are some
procedures knocking around which'll remove these extra bits (Hilary has
posted up a version on tis newsgroup a few times). Also, you'll like SQL
Server 2005 - there is the option to do a 'hard' version of
sp_removedbreplication which automatically removes all this replication
objects.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Wednesday, March 7, 2012
Replication Monitor error
Hi, There was trans-Rep setup on a server which was swap out with an new server. The database were backup and then restored. Replicaito was not working. I disable replication and then set the server up for a distribution. With out setting up any publi
shers there is a error on the replication monitor. I can't find where this error is. I did not set anything up yet. Is there a table I can check?
run this proc sp_MSload_replication_status
"Russ" <anonymous@.discussions.microsoft.com> wrote in message
news:8CBC3E88-E6FD-4E6B-B267-A3A5E5AC2F22@.microsoft.com...
> Hi, There was trans-Rep setup on a server which was swap out with an new
server. The database were backup and then restored. Replicaito was not
working. I disable replication and then set the server up for a
distribution. With out setting up any publishers there is a error on the
replication monitor. I can't find where this error is. I did not set
anything up yet. Is there a table I can check?
|||I cant find this stored procedure. Is this somthing that comes with SQL?
-- Hilary Cotter wrote: --
run this proc sp_MSload_replication_status
"Russ" <anonymous@.discussions.microsoft.com> wrote in message
news:8CBC3E88-E6FD-4E6B-B267-A3A5E5AC2F22@.microsoft.com...
> Hi, There was trans-Rep setup on a server which was swap out with an new
server. The database were backup and then restored. Replicaito was not
working. I disable replication and then set the server up for a
distribution. With out setting up any publishers there is a error on the
replication monitor. I can't find where this error is. I did not set
anything up yet. Is there a table I can check?
|||yes, it should be in the master db.
what version of SQL Server r u running.
"Russ" <anonymous@.discussions.microsoft.com> wrote in message
news:374639C1-95A1-48E9-90AC-CCA6C44F8D07@.microsoft.com...
> I cant find this stored procedure. Is this somthing that comes with SQL?
> -- Hilary Cotter wrote: --
> run this proc sp_MSload_replication_status
>
> "Russ" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CBC3E88-E6FD-4E6B-B267-A3A5E5AC2F22@.microsoft.com...
an new
> server. The database were backup and then restored. Replicaito was
not
> working. I disable replication and then set the server up for a
> distribution. With out setting up any publishers there is a error on
the
> replication monitor. I can't find where this error is. I did not
set
> anything up yet. Is there a table I can check?
>
>
shers there is a error on the replication monitor. I can't find where this error is. I did not set anything up yet. Is there a table I can check?
run this proc sp_MSload_replication_status
"Russ" <anonymous@.discussions.microsoft.com> wrote in message
news:8CBC3E88-E6FD-4E6B-B267-A3A5E5AC2F22@.microsoft.com...
> Hi, There was trans-Rep setup on a server which was swap out with an new
server. The database were backup and then restored. Replicaito was not
working. I disable replication and then set the server up for a
distribution. With out setting up any publishers there is a error on the
replication monitor. I can't find where this error is. I did not set
anything up yet. Is there a table I can check?
|||I cant find this stored procedure. Is this somthing that comes with SQL?
-- Hilary Cotter wrote: --
run this proc sp_MSload_replication_status
"Russ" <anonymous@.discussions.microsoft.com> wrote in message
news:8CBC3E88-E6FD-4E6B-B267-A3A5E5AC2F22@.microsoft.com...
> Hi, There was trans-Rep setup on a server which was swap out with an new
server. The database were backup and then restored. Replicaito was not
working. I disable replication and then set the server up for a
distribution. With out setting up any publishers there is a error on the
replication monitor. I can't find where this error is. I did not set
anything up yet. Is there a table I can check?
|||yes, it should be in the master db.
what version of SQL Server r u running.
"Russ" <anonymous@.discussions.microsoft.com> wrote in message
news:374639C1-95A1-48E9-90AC-CCA6C44F8D07@.microsoft.com...
> I cant find this stored procedure. Is this somthing that comes with SQL?
> -- Hilary Cotter wrote: --
> run this proc sp_MSload_replication_status
>
> "Russ" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CBC3E88-E6FD-4E6B-B267-A3A5E5AC2F22@.microsoft.com...
an new
> server. The database were backup and then restored. Replicaito was
not
> working. I disable replication and then set the server up for a
> distribution. With out setting up any publishers there is a error on
the
> replication monitor. I can't find where this error is. I did not
set
> anything up yet. Is there a table I can check?
>
>
Subscribe to:
Posts (Atom)