Hello,
I'm replicating from one server to another. The issue is that the objects
on the second server have a different owner than the first server. Is this
possible? If so, how do I set it up?
Jligda,
have a look at the article properties - there is the option to set a
different object owner there. Obviously the user must pre-exist on the
subscriber, but this can be done by selecting to apply a script before the
snapshot is run which uses sp_grantdbaccess.
HTH,
Paul Ibison
Showing posts with label replicating. Show all posts
Showing posts with label replicating. Show all posts
Friday, March 30, 2012
Replication to a Different Owner
Friday, March 23, 2012
Replication question
Dear Sir/Madam,
I am replicating 5 tables from Server A to Server B. On my first setting, I
included those 5 tables on a single publisher. After that, I recognize when a
table schema changes, I needed to drop and re-create the publishication,
means all 5 tables needed to re-synchronize again, is that true? Will it be
better to create 5 different publishication with each for one table only, so
during recreate publishication, only one table needed to synchronize, more
flexible? What the disadvantage for that?
Another question is, during recreate of one publishication, what is the
sequence of action? Is that stop log reader first, then recreate the one of
the publishication, generate snapshot, distribute the snapshot and finially
restart the log reader? Would this action sequence can make sure the
synchonize of those 5 tables?
Waiting to hearing from you soon. Many thanks!
Yours sincerely,
Henry
I would use sp_repladdcolumn or sp_repldropcolumn for this. SQL 20005 has
better options to replication schema changes seemelessly, otherwise if you
have a lot of changes to our schema you might want to think about log
shipping.
Splitting each article out into a seperate publication is a good idea when
sp_repladdcolumn and sp_repldropcolumn can't do the changes you are
looking for - for instance modifiying pk's. It is also a good idea when
your tables are large as if your snapshot fails on one table you will have
to replicate all tables again.
In general you shoudl group your tables into their own publications by DRI.
The way replication works is that the publication tables are locked and
then the log reader agent starts. Users can't make changes to tables until
the data is bcp'd out. Then when the data is bcp'd out the lock is
released and users changes are written to the tlog (as always) and the log
reader will read the transactions in the log and build commands to
replicate these transactions to the subscriber.
This way all the transactions are replicated and nothing is missed.
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
I am replicating 5 tables from Server A to Server B. On my first setting, I
included those 5 tables on a single publisher. After that, I recognize when a
table schema changes, I needed to drop and re-create the publishication,
means all 5 tables needed to re-synchronize again, is that true? Will it be
better to create 5 different publishication with each for one table only, so
during recreate publishication, only one table needed to synchronize, more
flexible? What the disadvantage for that?
Another question is, during recreate of one publishication, what is the
sequence of action? Is that stop log reader first, then recreate the one of
the publishication, generate snapshot, distribute the snapshot and finially
restart the log reader? Would this action sequence can make sure the
synchonize of those 5 tables?
Waiting to hearing from you soon. Many thanks!
Yours sincerely,
Henry
I would use sp_repladdcolumn or sp_repldropcolumn for this. SQL 20005 has
better options to replication schema changes seemelessly, otherwise if you
have a lot of changes to our schema you might want to think about log
shipping.
Splitting each article out into a seperate publication is a good idea when
sp_repladdcolumn and sp_repldropcolumn can't do the changes you are
looking for - for instance modifiying pk's. It is also a good idea when
your tables are large as if your snapshot fails on one table you will have
to replicate all tables again.
In general you shoudl group your tables into their own publications by DRI.
The way replication works is that the publication tables are locked and
then the log reader agent starts. Users can't make changes to tables until
the data is bcp'd out. Then when the data is bcp'd out the lock is
released and users changes are written to the tlog (as always) and the log
reader will read the transactions in the log and build commands to
replicate these transactions to the subscriber.
This way all the transactions are replicated and nothing is missed.
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
replication question
What is the thought on replicating over WAN links.
I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
like a local copy of the DB. So I would like to set up one-way replication.
The ISP says that replication is resource intensive. We have a 768K
Fractional T-1 they tell me that replication would suck up all that
bandwidth? There aren't a lot of updates that go on the DB in a day over
time there are a lot of updates but on a typical day there aren't a terrible
lot.
So I'm trying to gauge whether the ISP is right or if they aren't really
sure in what they are telling me. I would just
like to have a local copy of our DB on-site in-case the ISP's site goes down
for whatever reason.
Thanks,
Geo
Hi Marc
If you are applying normal transactional replication and don't have a lot of
transactions then your line would be more than adequate. The only bandwidth
intensive process would be when you initialize and apply a 1.2 GB snapshot
accross the line. Then SQL will use as much bandwidth as possible.
We've run transactional replication successfully on 256k lines without it
having a huge impact on the line. Again depending on the transaction volumes.
Regards
Cube
http://www.sqlserver.co.za
"marc" wrote:
> What is the thought on replicating over WAN links.
> I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
> like a local copy of the DB. So I would like to set up one-way replication.
> The ISP says that replication is resource intensive. We have a 768K
> Fractional T-1 they tell me that replication would suck up all that
> bandwidth? There aren't a lot of updates that go on the DB in a day over
> time there are a lot of updates but on a typical day there aren't a terrible
> lot.
> So I'm trying to gauge whether the ISP is right or if they aren't really
> sure in what they are telling me. I would just
> like to have a local copy of our DB on-site in-case the ISP's site goes down
> for whatever reason.
> Thanks,
> Geo
>
>
I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
like a local copy of the DB. So I would like to set up one-way replication.
The ISP says that replication is resource intensive. We have a 768K
Fractional T-1 they tell me that replication would suck up all that
bandwidth? There aren't a lot of updates that go on the DB in a day over
time there are a lot of updates but on a typical day there aren't a terrible
lot.
So I'm trying to gauge whether the ISP is right or if they aren't really
sure in what they are telling me. I would just
like to have a local copy of our DB on-site in-case the ISP's site goes down
for whatever reason.
Thanks,
Geo
Hi Marc
If you are applying normal transactional replication and don't have a lot of
transactions then your line would be more than adequate. The only bandwidth
intensive process would be when you initialize and apply a 1.2 GB snapshot
accross the line. Then SQL will use as much bandwidth as possible.
We've run transactional replication successfully on 256k lines without it
having a huge impact on the line. Again depending on the transaction volumes.
Regards
Cube
http://www.sqlserver.co.za
"marc" wrote:
> What is the thought on replicating over WAN links.
> I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
> like a local copy of the DB. So I would like to set up one-way replication.
> The ISP says that replication is resource intensive. We have a 768K
> Fractional T-1 they tell me that replication would suck up all that
> bandwidth? There aren't a lot of updates that go on the DB in a day over
> time there are a lot of updates but on a typical day there aren't a terrible
> lot.
> So I'm trying to gauge whether the ISP is right or if they aren't really
> sure in what they are telling me. I would just
> like to have a local copy of our DB on-site in-case the ISP's site goes down
> for whatever reason.
> Thanks,
> Geo
>
>
replication problems sql server to db2
I am experiencing problems replicating (transactional) from SQL Server
2000 sp3 to DB2 v8.2 (running on Red Hat Linux).
2000 sp3 to DB2 v8.2 (running on Red Hat Linux).
I have created an OLE-DB linked server to DB2. The connection connects
successfully in EM and I can query the DB2 tables using the OPENQUERY
function.
I have successfully added the linked server connection as a Subscriber.
I have now set up a Push Subscription to DB2 from one of my
Publications.
My Distribution Agent errors with:
"The process could not connect to Subscriber xxx "
Any suggestions will help, thank youI have no idea myself, but you might get a better response in
microsoft.public.sqlserver.replication.
Simon|||I have no idea myself, but you might get a better response in
microsoft.public.sqlserver.replication.
Simon
Labels:
created,
database,
db2,
experiencing,
hat,
linked,
linux,
microsoft,
mysql,
ole-db,
oracle,
red,
replicating,
replication,
running,
server,
server2000,
sp3,
sql,
transactional
Tuesday, March 20, 2012
Replication performance Problem with SQL Server -> Oracle9i
We are replicating from SQL server 2000 DB to Oracle 9i database using a 2 Mbps line. When there is a mass update on our DB, the same is replicated to the Oracle 9i side, but apparently the SQl Server sends as many update/delete requests to Oracle as the
number of rows affected on SQL side (17000). The update therefore takes enormous time on the Oracle side and the whole system on Oracle side freezes for hours till all the updates happen and then the single row updates take place in about 30 seconds time.
when we sneak into the execution on the Oracle side, it seems it commits for each of the rows it updates. we use OLEDB provider for Oracle. does this have any link with the Auto-commit settings or due to some other?..
This is normal. Replication breaks down set based operations to singletons
to ensure that data is consistent on both ends - the Publisher and
Subscriber.
You will get better performance using the Microsoft ODBC driver for Oracle
and the Microsoft OLE DB provider for Oracle
If you are using the Oracle OLE DB provider you should be selecting the
Allow InProcess option of your linked server.
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:BEC3FC8A-34CB-4760-B145-08C94801FD39@.microsoft.com...
> We are replicating from SQL server 2000 DB to Oracle 9i database using a 2
Mbps line. When there is a mass update on our DB, the same is replicated to
the Oracle 9i side, but apparently the SQl Server sends as many
update/delete requests to Oracle as the number of rows affected on SQL side
(17000). The update therefore takes enormous time on the Oracle side and the
whole system on Oracle side freezes for hours till all the updates happen
and then the single row updates take place in about 30 seconds time. when we
sneak into the execution on the Oracle side, it seems it commits for each of
the rows it updates. we use OLEDB provider for Oracle. does this have any
link with the Auto-commit settings or due to some other?..
number of rows affected on SQL side (17000). The update therefore takes enormous time on the Oracle side and the whole system on Oracle side freezes for hours till all the updates happen and then the single row updates take place in about 30 seconds time.
when we sneak into the execution on the Oracle side, it seems it commits for each of the rows it updates. we use OLEDB provider for Oracle. does this have any link with the Auto-commit settings or due to some other?..
This is normal. Replication breaks down set based operations to singletons
to ensure that data is consistent on both ends - the Publisher and
Subscriber.
You will get better performance using the Microsoft ODBC driver for Oracle
and the Microsoft OLE DB provider for Oracle
If you are using the Oracle OLE DB provider you should be selecting the
Allow InProcess option of your linked server.
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:BEC3FC8A-34CB-4760-B145-08C94801FD39@.microsoft.com...
> We are replicating from SQL server 2000 DB to Oracle 9i database using a 2
Mbps line. When there is a mass update on our DB, the same is replicated to
the Oracle 9i side, but apparently the SQl Server sends as many
update/delete requests to Oracle as the number of rows affected on SQL side
(17000). The update therefore takes enormous time on the Oracle side and the
whole system on Oracle side freezes for hours till all the updates happen
and then the single row updates take place in about 30 seconds time. when we
sneak into the execution on the Oracle side, it seems it commits for each of
the rows it updates. we use OLEDB provider for Oracle. does this have any
link with the Auto-commit settings or due to some other?..
Labels:
database,
line,
mass,
mbps,
microsoft,
mysql,
oracle,
oracle9i,
performance,
replicated,
replicating,
replication,
server,
sql,
update
Monday, March 12, 2012
Replication one record to many ..... Can you do This?
I am replicating a table between 2 sql servers.The table has a nasty
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
You could customize the replication stored procedures on the subscriber
(sp_msins*) to do this.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:%23l%23fX2SLEHA.3472@.TK2MSFTNGP09.phx.gbl...
I am replicating a table between 2 sql servers.The table has a nasty
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||David,
you could use a straightforward trigger.
Regards,
Paul Ibison
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
You could customize the replication stored procedures on the subscriber
(sp_msins*) to do this.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:%23l%23fX2SLEHA.3472@.TK2MSFTNGP09.phx.gbl...
I am replicating a table between 2 sql servers.The table has a nasty
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||David,
you could use a straightforward trigger.
Regards,
Paul Ibison
Labels:
byspaces,
concatinated,
database,
microsoft,
mysql,
nastyfield,
numbers,
oracle,
record,
replicating,
replication,
seperated,
server,
servers,
sql,
string,
table
Replication one record to many ..... Can you do This?
I am replicating a table between 2 sql servers.The table has a nasty
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!You could customize the replication stored procedures on the subscriber
(sp_msins*) to do this.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:%23l%23fX2SLEHA.3472@.TK2MSFTNGP09.phx.gbl...
I am replicating a table between 2 sql servers.The table has a nasty
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||David,
you could use a straightforward trigger.
Regards,
Paul Ibison
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!You could customize the replication stored procedures on the subscriber
(sp_msins*) to do this.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:%23l%23fX2SLEHA.3472@.TK2MSFTNGP09.phx.gbl...
I am replicating a table between 2 sql servers.The table has a nasty
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||David,
you could use a straightforward trigger.
Regards,
Paul Ibison
Labels:
byspaces,
concatinated,
database,
microsoft,
mysql,
nastyfield,
numbers,
oracle,
record,
replicating,
replication,
seperated,
server,
servers,
sql,
string,
table
Replication one record to many ..... Can you do This?
I am replicating a table between 2 sql servers.The table has a nasty
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You could customize the replication stored procedures on the subscriber
(sp_msins*) to do this.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:%23l%23fX2SLEHA.3472@.TK2MSFTNGP09.phx.gbl...
I am replicating a table between 2 sql servers.The table has a nasty
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||David,
you could use a straightforward trigger.
Regards,
Paul Ibison
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You could customize the replication stored procedures on the subscriber
(sp_msins*) to do this.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:%23l%23fX2SLEHA.3472@.TK2MSFTNGP09.phx.gbl...
I am replicating a table between 2 sql servers.The table has a nasty
field in it, which is a concatinated string of numbers seperated by
spaces eg ("#(14104 14944 15784)"
What I would like to do is create a new record for each number in the
string. Ideally I would like to do this on the fly in the replication
process. Is such a thing possible and if so any idears about how to get
started
Many Thanks
David Hills
"
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||David,
you could use a straightforward trigger.
Regards,
Paul Ibison
Friday, March 9, 2012
Replication on 130GB db
Is there anyone with some expirience in replicating such large database?
Maybe some good link or something like that. I would need trans replication
instructions.
regards,
DaliborIf you have a fast link between your publisher/distributor/subscriber,
replicating 130GB is still managble, especially when the amount of data
changes is relatively small in comparison with the total database size. We
are replicating bigger database than that with transactional replication.
With 130GB of data, it could be a major headache to re-snapshot your
publication, which may take a while in most environments. It can become a
major problem when you don't have a large maintenance window. For us, the
most common reason for requiring a new snapshot is new releases of the
application that introduce database schema changes. Once we have reined in
the developers and cut down the frequency of releases, we have been able to
support replicating 200~300GB databases.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Dalibor Cvijetinovic" <dalibor@.ice.si> wrote in message
news:OC8DqnatDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Is there anyone with some expirience in replicating such large database?
> Maybe some good link or something like that. I would need trans
replication
> instructions.
> regards,
> Dalibor
>|||Our amount of data that needs to be replicated is around 20-30GB. That is
the reason why I would like to change replication type.Currently I am using
snapshot/push replication. Our maintenance window is around 12 hours. But in
that time i must import some data which takes about 2 hours and then i do
snapshot/push replication of 130GB. That replication is lasting forever!!
After that i must replicate another db with 25GB.
I need to make trans replication or I will simply get ...
Furthere more this large scaled replication are not stable.
Can you give me some pointers on how to resolve this problem.
Dalibor
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:Ol$yrMbtDHA.424@.TK2MSFTNGP11.phx.gbl...
> If you have a fast link between your publisher/distributor/subscriber,
> replicating 130GB is still managble, especially when the amount of data
> changes is relatively small in comparison with the total database size. We
> are replicating bigger database than that with transactional replication.
> With 130GB of data, it could be a major headache to re-snapshot your
> publication, which may take a while in most environments. It can become a
> major problem when you don't have a large maintenance window. For us, the
> most common reason for requiring a new snapshot is new releases of the
> application that introduce database schema changes. Once we have reined in
> the developers and cut down the frequency of releases, we have been able
to
> support replicating 200~300GB databases.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Dalibor Cvijetinovic" <dalibor@.ice.si> wrote in message
> news:OC8DqnatDHA.2340@.TK2MSFTNGP12.phx.gbl...
> > Is there anyone with some expirience in replicating such large database?
> > Maybe some good link or something like that. I would need trans
> replication
> > instructions.
> >
> > regards,
> > Dalibor
> >
> >
>
Maybe some good link or something like that. I would need trans replication
instructions.
regards,
DaliborIf you have a fast link between your publisher/distributor/subscriber,
replicating 130GB is still managble, especially when the amount of data
changes is relatively small in comparison with the total database size. We
are replicating bigger database than that with transactional replication.
With 130GB of data, it could be a major headache to re-snapshot your
publication, which may take a while in most environments. It can become a
major problem when you don't have a large maintenance window. For us, the
most common reason for requiring a new snapshot is new releases of the
application that introduce database schema changes. Once we have reined in
the developers and cut down the frequency of releases, we have been able to
support replicating 200~300GB databases.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Dalibor Cvijetinovic" <dalibor@.ice.si> wrote in message
news:OC8DqnatDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Is there anyone with some expirience in replicating such large database?
> Maybe some good link or something like that. I would need trans
replication
> instructions.
> regards,
> Dalibor
>|||Our amount of data that needs to be replicated is around 20-30GB. That is
the reason why I would like to change replication type.Currently I am using
snapshot/push replication. Our maintenance window is around 12 hours. But in
that time i must import some data which takes about 2 hours and then i do
snapshot/push replication of 130GB. That replication is lasting forever!!
After that i must replicate another db with 25GB.
I need to make trans replication or I will simply get ...
Furthere more this large scaled replication are not stable.
Can you give me some pointers on how to resolve this problem.
Dalibor
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:Ol$yrMbtDHA.424@.TK2MSFTNGP11.phx.gbl...
> If you have a fast link between your publisher/distributor/subscriber,
> replicating 130GB is still managble, especially when the amount of data
> changes is relatively small in comparison with the total database size. We
> are replicating bigger database than that with transactional replication.
> With 130GB of data, it could be a major headache to re-snapshot your
> publication, which may take a while in most environments. It can become a
> major problem when you don't have a large maintenance window. For us, the
> most common reason for requiring a new snapshot is new releases of the
> application that introduce database schema changes. Once we have reined in
> the developers and cut down the frequency of releases, we have been able
to
> support replicating 200~300GB databases.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Dalibor Cvijetinovic" <dalibor@.ice.si> wrote in message
> news:OC8DqnatDHA.2340@.TK2MSFTNGP12.phx.gbl...
> > Is there anyone with some expirience in replicating such large database?
> > Maybe some good link or something like that. I would need trans
> replication
> > instructions.
> >
> > regards,
> > Dalibor
> >
> >
>
Labels:
130gb,
database,
expirience,
link,
microsoft,
mysql,
oracle,
replicating,
replication,
server,
sql,
trans
Replication of indexes, triggers, constraints in MS SQL Server 200
Hi,
Is there any better means of replicating table indexes, triggers, and
constraints than simply reinitializing the article everytime their
definitions change or a new one is added? This is in Microsoft SQL Server
2000 SP3.
Many thanks,
Oskar
sp_addscriptexec
It only works for subscribers deployed via a UNC.
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
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:469DF5DA-6394-477E-B8DD-915E0E48072A@.microsoft.com...
> Hi,
> Is there any better means of replicating table indexes, triggers, and
> constraints than simply reinitializing the article everytime their
> definitions change or a new one is added? This is in Microsoft SQL Server
> 2000 SP3.
> --
> Many thanks,
> Oskar
>
Is there any better means of replicating table indexes, triggers, and
constraints than simply reinitializing the article everytime their
definitions change or a new one is added? This is in Microsoft SQL Server
2000 SP3.
Many thanks,
Oskar
sp_addscriptexec
It only works for subscribers deployed via a UNC.
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
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:469DF5DA-6394-477E-B8DD-915E0E48072A@.microsoft.com...
> Hi,
> Is there any better means of replicating table indexes, triggers, and
> constraints than simply reinitializing the article everytime their
> definitions change or a new one is added? This is in Microsoft SQL Server
> 2000 SP3.
> --
> Many thanks,
> Oskar
>
Labels:
andconstraints,
article,
constraints,
database,
everytime,
indexes,
means,
microsoft,
mysql,
oracle,
reinitializing,
replicating,
replication,
server,
simply,
sql,
table,
theirdefinitions,
triggers
Saturday, February 25, 2012
Replication Issues
Hello,
I am replicating databases from a sql2000 server with a
seperate distributer running sql2000 server and a MSDE subscriber. I
am using merge replication. The subscribers pull a subscription.
We want to have this setup across the internet for multiple
subscribers
each of whom might be updating as frequently as 1 hour. There are a
couple of issues I would like to find out about.
1.Security: I understood that the initial snapshots can be
published via ftp ( or any other means ofcourse). Can the merge
process be accomplished via ftp too? i.e The merge agent running on
the subscriber, writes to the ftp space reserved for it and the
publisher uses these files to bring the publisher up to speed and put
the updates back to all the subscribers. (sounds very slow). What are
our options as far as channel security if we are not using ftp ( and
is it possible in the first place across the internet).If we use a
secure ftp site, can the agents manage that?
2.Load: Our publisher has about 20 databases right now, with a
total of 1Gig file space occupied by mdf and log files. One database
is being replicated. Its consuming 1Gig of RAM Memory. Is that normal?
Is it because of the replication. How can I find out? Can anybody with
experience in this area, tell how much more load we can expect to
see? How much processing is needed for 40 users each updating about
500KB of data every one hour?
Thanks a lot,
Hananiel
1) I'm confused by what you are referring to by ftp. Are you talking about
the ftp port (21) or the ftp service. IE can the merge agent download the
commands using FTP? The answer is no, only the snapshot. If you are talking
about using port 21 the answer is yes. The merge agent will connect to the
publisher/distributor/subscriber using port 1433 or whatever port your SQL
Server is configured to run on, which could be port 21.
2) SQL Server will consume as much memory as is accessible on the machine or
whatever you configure SQL Server to consume using sp_configure 'max server
memory (MB)'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hananiel" <hananiel@.yahoo.com> wrote in message
news:e338e321.0409170710.68893ce9@.posting.google.c om...
> Hello,
> I am replicating databases from a sql2000 server with a
> seperate distributer running sql2000 server and a MSDE subscriber. I
> am using merge replication. The subscribers pull a subscription.
> We want to have this setup across the internet for multiple
> subscribers
> each of whom might be updating as frequently as 1 hour. There are a
> couple of issues I would like to find out about.
> 1.Security: I understood that the initial snapshots can be
> published via ftp ( or any other means ofcourse). Can the merge
> process be accomplished via ftp too? i.e The merge agent running on
> the subscriber, writes to the ftp space reserved for it and the
> publisher uses these files to bring the publisher up to speed and put
> the updates back to all the subscribers. (sounds very slow). What are
> our options as far as channel security if we are not using ftp ( and
> is it possible in the first place across the internet).If we use a
> secure ftp site, can the agents manage that?
> 2.Load: Our publisher has about 20 databases right now, with a
> total of 1Gig file space occupied by mdf and log files. One database
> is being replicated. Its consuming 1Gig of RAM Memory. Is that normal?
> Is it because of the replication. How can I find out? Can anybody with
> experience in this area, tell how much more load we can expect to
> see? How much processing is needed for 40 users each updating about
> 500KB of data every one hour?
> Thanks a lot,
> Hananiel
|||Hilary Thanks!,
Does SqlServer natively support encryption of
communication between its remote merge agents and itself? Or should we use a
tool like secure shell to make an encrypted connection?
Thanks,
Hananiel
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eXjhIAOnEHA.644@.tk2msftngp13.phx.gbl...
> 1) I'm confused by what you are referring to by ftp. Are you talking about
> the ftp port (21) or the ftp service. IE can the merge agent download the
> commands using FTP? The answer is no, only the snapshot. If you are
talking
> about using port 21 the answer is yes. The merge agent will connect to the
> publisher/distributor/subscriber using port 1433 or whatever port your SQL
> Server is configured to run on, which could be port 21.
> 2) SQL Server will consume as much memory as is accessible on the machine
or
> whatever you configure SQL Server to consume using sp_configure 'max
server
> memory (MB)'
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Hananiel" <hananiel@.yahoo.com> wrote in message
> news:e338e321.0409170710.68893ce9@.posting.google.c om...
>
I am replicating databases from a sql2000 server with a
seperate distributer running sql2000 server and a MSDE subscriber. I
am using merge replication. The subscribers pull a subscription.
We want to have this setup across the internet for multiple
subscribers
each of whom might be updating as frequently as 1 hour. There are a
couple of issues I would like to find out about.
1.Security: I understood that the initial snapshots can be
published via ftp ( or any other means ofcourse). Can the merge
process be accomplished via ftp too? i.e The merge agent running on
the subscriber, writes to the ftp space reserved for it and the
publisher uses these files to bring the publisher up to speed and put
the updates back to all the subscribers. (sounds very slow). What are
our options as far as channel security if we are not using ftp ( and
is it possible in the first place across the internet).If we use a
secure ftp site, can the agents manage that?
2.Load: Our publisher has about 20 databases right now, with a
total of 1Gig file space occupied by mdf and log files. One database
is being replicated. Its consuming 1Gig of RAM Memory. Is that normal?
Is it because of the replication. How can I find out? Can anybody with
experience in this area, tell how much more load we can expect to
see? How much processing is needed for 40 users each updating about
500KB of data every one hour?
Thanks a lot,
Hananiel
1) I'm confused by what you are referring to by ftp. Are you talking about
the ftp port (21) or the ftp service. IE can the merge agent download the
commands using FTP? The answer is no, only the snapshot. If you are talking
about using port 21 the answer is yes. The merge agent will connect to the
publisher/distributor/subscriber using port 1433 or whatever port your SQL
Server is configured to run on, which could be port 21.
2) SQL Server will consume as much memory as is accessible on the machine or
whatever you configure SQL Server to consume using sp_configure 'max server
memory (MB)'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hananiel" <hananiel@.yahoo.com> wrote in message
news:e338e321.0409170710.68893ce9@.posting.google.c om...
> Hello,
> I am replicating databases from a sql2000 server with a
> seperate distributer running sql2000 server and a MSDE subscriber. I
> am using merge replication. The subscribers pull a subscription.
> We want to have this setup across the internet for multiple
> subscribers
> each of whom might be updating as frequently as 1 hour. There are a
> couple of issues I would like to find out about.
> 1.Security: I understood that the initial snapshots can be
> published via ftp ( or any other means ofcourse). Can the merge
> process be accomplished via ftp too? i.e The merge agent running on
> the subscriber, writes to the ftp space reserved for it and the
> publisher uses these files to bring the publisher up to speed and put
> the updates back to all the subscribers. (sounds very slow). What are
> our options as far as channel security if we are not using ftp ( and
> is it possible in the first place across the internet).If we use a
> secure ftp site, can the agents manage that?
> 2.Load: Our publisher has about 20 databases right now, with a
> total of 1Gig file space occupied by mdf and log files. One database
> is being replicated. Its consuming 1Gig of RAM Memory. Is that normal?
> Is it because of the replication. How can I find out? Can anybody with
> experience in this area, tell how much more load we can expect to
> see? How much processing is needed for 40 users each updating about
> 500KB of data every one hour?
> Thanks a lot,
> Hananiel
|||Hilary Thanks!,
Does SqlServer natively support encryption of
communication between its remote merge agents and itself? Or should we use a
tool like secure shell to make an encrypted connection?
Thanks,
Hananiel
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eXjhIAOnEHA.644@.tk2msftngp13.phx.gbl...
> 1) I'm confused by what you are referring to by ftp. Are you talking about
> the ftp port (21) or the ftp service. IE can the merge agent download the
> commands using FTP? The answer is no, only the snapshot. If you are
talking
> about using port 21 the answer is yes. The merge agent will connect to the
> publisher/distributor/subscriber using port 1433 or whatever port your SQL
> Server is configured to run on, which could be port 21.
> 2) SQL Server will consume as much memory as is accessible on the machine
or
> whatever you configure SQL Server to consume using sp_configure 'max
server
> memory (MB)'
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Hananiel" <hananiel@.yahoo.com> wrote in message
> news:e338e321.0409170710.68893ce9@.posting.google.c om...
>
Labels:
aseperate,
database,
databases,
distributer,
iam,
merge,
microsoft,
msde,
mysql,
oracle,
replicating,
replication,
running,
server,
sql,
sql2000,
subscriber
Tuesday, February 21, 2012
replication is completed
We have a server that is replicating information back and forth to 10
different location. My question is how do i know if a location is done
replicating to us. my scenario is that we have 10 stores, each stores as it
makes a sales, the information is replicated back to corporate. Corporate
in turn replicate that information back to the other nine stores. How can i
tell if a store is done downloading information to corporate verse corporate
still trying to push data to the store.
tx in advance.
That depends. Is there ever a point when no transactions are occuring on the
stores? If so you can schedule the agents and after the final job has
completed the central location will have all the data.
Another option is to run sp_browsereplcmds on the publisher and subscriber
until no rows are returned.
You could also query select delivered_transactions from
MSdistribution_history in the distribution database and wait for a value of
delivered_transactions to be 0.
For merge you might want to query select delivery_rate from
MSmerge_history
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"tlee" <tlee@.lang.com> wrote in message
news:OVTyEXXsEHA.904@.TK2MSFTNGP11.phx.gbl...
> We have a server that is replicating information back and forth to 10
> different location. My question is how do i know if a location is done
> replicating to us. my scenario is that we have 10 stores, each stores as
it
> makes a sales, the information is replicated back to corporate. Corporate
> in turn replicate that information back to the other nine stores. How can
i
> tell if a store is done downloading information to corporate verse
corporate
> still trying to push data to the store.
> tx in advance.
>
|||Thanks, i will give it a try.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23C$IATesEHA.3320@.TK2MSFTNGP15.phx.gbl...
> That depends. Is there ever a point when no transactions are occuring on
> the
> stores? If so you can schedule the agents and after the final job has
> completed the central location will have all the data.
> Another option is to run sp_browsereplcmds on the publisher and subscriber
> until no rows are returned.
> You could also query select delivered_transactions from
> MSdistribution_history in the distribution database and wait for a value
> of
> delivered_transactions to be 0.
> For merge you might want to query select delivery_rate from
> MSmerge_history
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "tlee" <tlee@.lang.com> wrote in message
> news:OVTyEXXsEHA.904@.TK2MSFTNGP11.phx.gbl...
> it
> i
> corporate
>
different location. My question is how do i know if a location is done
replicating to us. my scenario is that we have 10 stores, each stores as it
makes a sales, the information is replicated back to corporate. Corporate
in turn replicate that information back to the other nine stores. How can i
tell if a store is done downloading information to corporate verse corporate
still trying to push data to the store.
tx in advance.
That depends. Is there ever a point when no transactions are occuring on the
stores? If so you can schedule the agents and after the final job has
completed the central location will have all the data.
Another option is to run sp_browsereplcmds on the publisher and subscriber
until no rows are returned.
You could also query select delivered_transactions from
MSdistribution_history in the distribution database and wait for a value of
delivered_transactions to be 0.
For merge you might want to query select delivery_rate from
MSmerge_history
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"tlee" <tlee@.lang.com> wrote in message
news:OVTyEXXsEHA.904@.TK2MSFTNGP11.phx.gbl...
> We have a server that is replicating information back and forth to 10
> different location. My question is how do i know if a location is done
> replicating to us. my scenario is that we have 10 stores, each stores as
it
> makes a sales, the information is replicated back to corporate. Corporate
> in turn replicate that information back to the other nine stores. How can
i
> tell if a store is done downloading information to corporate verse
corporate
> still trying to push data to the store.
> tx in advance.
>
|||Thanks, i will give it a try.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23C$IATesEHA.3320@.TK2MSFTNGP15.phx.gbl...
> That depends. Is there ever a point when no transactions are occuring on
> the
> stores? If so you can schedule the agents and after the final job has
> completed the central location will have all the data.
> Another option is to run sp_browsereplcmds on the publisher and subscriber
> until no rows are returned.
> You could also query select delivered_transactions from
> MSdistribution_history in the distribution database and wait for a value
> of
> delivered_transactions to be 0.
> For merge you might want to query select delivery_rate from
> MSmerge_history
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "tlee" <tlee@.lang.com> wrote in message
> news:OVTyEXXsEHA.904@.TK2MSFTNGP11.phx.gbl...
> it
> i
> corporate
>
Labels:
10different,
back,
completed,
database,
forth,
location,
microsoft,
mysql,
oracle,
replicating,
replication,
server,
sql
Subscribe to:
Posts (Atom)