I'm new to replication and maybe I'm missing something.
I set up replication from one server to another (push).
The replication is working fine every hour, but I have to start the
snapshots manually.
If I don't, no replication takes place, because there are no changes in the
snapshot.
I want the snapshots to be taken automatically every hour, and then
replicated to the other server.
Is this not the way it works normally? What am I missing?
Riki
It really depends on what type of replication you have set up. If it is
transactional replication, then the snapshot agent runs once to initialize
the subscriber and hten is not normally scheduled to run again - changes to
the publisher's data are sent to the subscriber as transactions rather than
ODBCBCP snapshots. If you have snapshot replication set up, then the snapshot
agent should indeed be set up and scheduled to run - before the distribution
agent. This is not typically done regularly as it'll lock tables on the
publisher and prevent access to the subscriber tables also until the process
completes.
HTH,
Paul Ibison
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:D9819EC4-E490-4BDB-A1B2-9EA49E6D1708@.microsoft.com...
> It really depends on what type of replication you have set up. If it is
> transactional replication, then the snapshot agent runs once to initialize
> the subscriber and hten is not normally scheduled to run again - changes
> to
> the publisher's data are sent to the subscriber as transactions rather
> than
> ODBCBCP snapshots. If you have snapshot replication set up, then the
> snapshot
> agent should indeed be set up and scheduled to run - before the
> distribution
> agent. This is not typically done regularly as it'll lock tables on the
> publisher and prevent access to the subscriber tables also until the
> process
> completes.
> HTH,
> Paul Ibison
>
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Thursday, March 29, 2012
Automating Replication
Hello everyone,
I need some pointers to do the following:
1. I set up a publisher
2. I Set up a distributer
--To have anonymous merge replication.
3. An installer installs MSDE on the client computer if it doesnt
exist.
I want to run something on a client computer thats not on the same
network (via internet), that will automatically setup a database, pull
a subscription from the above distributers and publishers and
synchronize with a given schedule. I want the schedule to be
configurable later. Moreover, the user of the program which uses this
replicated database should be able to click a button and say
Synchronize at any point of time.
What is the best way to achieve this?
1. I saw the MergeX object which is an activeX interface. It doesnt
allow me any interface to change the schedules on the merge agent. It
lets me synchronize though. Is there a hidden interface to access the
merge agent?
2. The SQL-DMO object seemed to allow me to change the schedule, but
it didnt reflect in the current merge agents schedule. The
SQLDMO.SQLServer.Jobs interface didnt show up the current active jobs
(Im assuming a MergeAgentsSchedule should show up here)
foreach(ReplicationDatabase rdb in
s.Replication.ReplicationDatabases)
foreach(MergePullSubscription mps in rdb.MergePullSubscriptions)
mps.MergeJobID
There is a pull subscription visible. But the job Id is
000000000000000 .
In enterprise manager the id is 8 or something like that.
Is SQL-DMO designed to do what im trying to do? or am i missing
something?
3. Is it better to just write an sql script? and call it in code
through a storedprocedure? How Can it be done?
Thanks in advance,
Hananiel
1) no, there is no way to change the scheduling of the merge agent using the
ActiveX controls. Even with plain old merge replication it still farms out
the scheduling to the SQL Server Agent schedule.
2) SQL DMO an ActiveX controls are very different objects. Think of ActiveX
controls as a lightweight SQL DMO. The jobs created through the ActiveX
controls are different and not designed to be managed by SQL DMO, EM (which
uses SQL DMO), or the replication stored procedures. Use another scheduling
agent to schedule your program/script which uses the ActiveX controls or
schedule the program or script using the SQL Server job scheduler.
3) Well you could, but it depends on what you are trying to do. The ActiveX
controls do not offer the functionality that SQL DMO does, and they are
designed in cases where you don't or can't enable the Subscribers through
EM, ie MDSE subscribers, or anonymous subscribers over the internet. The
ActiveX controls also don't support SQL Authentication (IIRC), so you will
have to enter a password each time they run.
HTH
"Hananiel" <hananiel@.yahoo.com> wrote in message
news:e338e321.0409201104.63408961@.posting.google.c om...
> Hello everyone,
> I need some pointers to do the following:
> 1. I set up a publisher
> 2. I Set up a distributer
> --To have anonymous merge replication.
> 3. An installer installs MSDE on the client computer if it doesnt
> exist.
> I want to run something on a client computer thats not on the same
> network (via internet), that will automatically setup a database, pull
> a subscription from the above distributers and publishers and
> synchronize with a given schedule. I want the schedule to be
> configurable later. Moreover, the user of the program which uses this
> replicated database should be able to click a button and say
> Synchronize at any point of time.
> What is the best way to achieve this?
> 1. I saw the MergeX object which is an activeX interface. It doesnt
> allow me any interface to change the schedules on the merge agent. It
> lets me synchronize though. Is there a hidden interface to access the
> merge agent?
> 2. The SQL-DMO object seemed to allow me to change the schedule, but
> it didnt reflect in the current merge agents schedule. The
> SQLDMO.SQLServer.Jobs interface didnt show up the current active jobs
> (Im assuming a MergeAgentsSchedule should show up here)
> foreach(ReplicationDatabase rdb in
> s.Replication.ReplicationDatabases)
> foreach(MergePullSubscription mps in rdb.MergePullSubscriptions)
> mps.MergeJobID
> There is a pull subscription visible. But the job Id is
> 000000000000000 .
> In enterprise manager the id is 8 or something like that.
> Is SQL-DMO designed to do what im trying to do? or am i missing
> something?
> 3. Is it better to just write an sql script? and call it in code
> through a storedprocedure? How Can it be done?
> Thanks in advance,
> Hananiel
I need some pointers to do the following:
1. I set up a publisher
2. I Set up a distributer
--To have anonymous merge replication.
3. An installer installs MSDE on the client computer if it doesnt
exist.
I want to run something on a client computer thats not on the same
network (via internet), that will automatically setup a database, pull
a subscription from the above distributers and publishers and
synchronize with a given schedule. I want the schedule to be
configurable later. Moreover, the user of the program which uses this
replicated database should be able to click a button and say
Synchronize at any point of time.
What is the best way to achieve this?
1. I saw the MergeX object which is an activeX interface. It doesnt
allow me any interface to change the schedules on the merge agent. It
lets me synchronize though. Is there a hidden interface to access the
merge agent?
2. The SQL-DMO object seemed to allow me to change the schedule, but
it didnt reflect in the current merge agents schedule. The
SQLDMO.SQLServer.Jobs interface didnt show up the current active jobs
(Im assuming a MergeAgentsSchedule should show up here)
foreach(ReplicationDatabase rdb in
s.Replication.ReplicationDatabases)
foreach(MergePullSubscription mps in rdb.MergePullSubscriptions)
mps.MergeJobID
There is a pull subscription visible. But the job Id is
000000000000000 .
In enterprise manager the id is 8 or something like that.
Is SQL-DMO designed to do what im trying to do? or am i missing
something?
3. Is it better to just write an sql script? and call it in code
through a storedprocedure? How Can it be done?
Thanks in advance,
Hananiel
1) no, there is no way to change the scheduling of the merge agent using the
ActiveX controls. Even with plain old merge replication it still farms out
the scheduling to the SQL Server Agent schedule.
2) SQL DMO an ActiveX controls are very different objects. Think of ActiveX
controls as a lightweight SQL DMO. The jobs created through the ActiveX
controls are different and not designed to be managed by SQL DMO, EM (which
uses SQL DMO), or the replication stored procedures. Use another scheduling
agent to schedule your program/script which uses the ActiveX controls or
schedule the program or script using the SQL Server job scheduler.
3) Well you could, but it depends on what you are trying to do. The ActiveX
controls do not offer the functionality that SQL DMO does, and they are
designed in cases where you don't or can't enable the Subscribers through
EM, ie MDSE subscribers, or anonymous subscribers over the internet. The
ActiveX controls also don't support SQL Authentication (IIRC), so you will
have to enter a password each time they run.
HTH
"Hananiel" <hananiel@.yahoo.com> wrote in message
news:e338e321.0409201104.63408961@.posting.google.c om...
> Hello everyone,
> I need some pointers to do the following:
> 1. I set up a publisher
> 2. I Set up a distributer
> --To have anonymous merge replication.
> 3. An installer installs MSDE on the client computer if it doesnt
> exist.
> I want to run something on a client computer thats not on the same
> network (via internet), that will automatically setup a database, pull
> a subscription from the above distributers and publishers and
> synchronize with a given schedule. I want the schedule to be
> configurable later. Moreover, the user of the program which uses this
> replicated database should be able to click a button and say
> Synchronize at any point of time.
> What is the best way to achieve this?
> 1. I saw the MergeX object which is an activeX interface. It doesnt
> allow me any interface to change the schedules on the merge agent. It
> lets me synchronize though. Is there a hidden interface to access the
> merge agent?
> 2. The SQL-DMO object seemed to allow me to change the schedule, but
> it didnt reflect in the current merge agents schedule. The
> SQLDMO.SQLServer.Jobs interface didnt show up the current active jobs
> (Im assuming a MergeAgentsSchedule should show up here)
> foreach(ReplicationDatabase rdb in
> s.Replication.ReplicationDatabases)
> foreach(MergePullSubscription mps in rdb.MergePullSubscriptions)
> mps.MergeJobID
> There is a pull subscription visible. But the job Id is
> 000000000000000 .
> In enterprise manager the id is 8 or something like that.
> Is SQL-DMO designed to do what im trying to do? or am i missing
> something?
> 3. Is it better to just write an sql script? and call it in code
> through a storedprocedure? How Can it be done?
> Thanks in advance,
> Hananiel
Sunday, March 25, 2012
automatically restarting merge replication after time out
Hello,
every few days my merge replication fails due to a network problem and
subsequent time out. How can I trigger a replication restart automatically,
or at a certain time of day?
Bill,
you can change the workflow properties on your merge agent's job so that the
third step on completion goes to the first step.
Alternatively, you could avoid continuous replication and have the merge
agent run on a schedule.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
every few days my merge replication fails due to a network problem and
subsequent time out. How can I trigger a replication restart automatically,
or at a certain time of day?
Bill,
you can change the workflow properties on your merge agent's job so that the
third step on completion goes to the first step.
Alternatively, you could avoid continuous replication and have the merge
agent run on a schedule.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Tuesday, March 20, 2012
Automatic Replication Trigger
HI All,
Using SQL Server 2000 SP3.
Replication is set up on a few databases, on each of the tables that are
replicated the following trigger appears:
create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
update as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [dbo].[Voucher]
set msrepl_tran_version = newid()
from [dbo].[Voucher], inserted
where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
This trigger is causing a significant slowdown when called and I would
like to remove it. However I do not want to break the replication.
I need to understand what has created this trigger and why. I belive
that this has been implemented as a workaround for some problem that was
encountered by the replication process but this is merely speculation on
my behalf.
Your help is very much appreciated.
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
It looks like you are using immediate or queued updating which puts these
triggers in place on the publisher. I can't comment on Microsoft's logic
behind this proc. Basically what it does is assigns a new value to the guid
column if the guid column it self is not modified/supplied.
I would look at whether you really need immediate updating or bi-directional
replication in general as bi-directional transactional replication offers
some of the functionality but will not cause the performance degradation.
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
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:uU1y0aSSGHA.4956@.TK2MSFTNGP09.phx.gbl...
>
> HI All,
> Using SQL Server 2000 SP3.
> Replication is set up on a few databases, on each of the tables that are
> replicated the following trigger appears:
> create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
> update as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [dbo].[Voucher]
> set msrepl_tran_version = newid()
> from [dbo].[Voucher], inserted
> where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
>
> This trigger is causing a significant slowdown when called and I would
> like to remove it. However I do not want to break the replication.
> I need to understand what has created this trigger and why. I belive
> that this has been implemented as a workaround for some problem that was
> encountered by the replication process but this is merely speculation on
> my behalf.
> Your help is very much appreciated.
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks for your response here. What is required is one-way replication
as we have a primary server that needs to replicate information to a
backup server.
I presume that this is a different configuration from immediate/queued
updating. I thought that transactional replication is what was set up
but perhaps this is not the case?
Can the specific kind of replication be altered in situ or would it need
to be dropped and recreated?
Again many thanks for any help!
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
|||Use transactional replication if you have no requirement for
bi-directionality. to fix this drop the subscriber, and then disable the
triggers, then redeploy the subscriber as a plain vanilla subscriber.
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
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:OarBgsZSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>
> Thanks for your response here. What is required is one-way replication
> as we have a primary server that needs to replicate information to a
> backup server.
> I presume that this is a different configuration from immediate/queued
> updating. I thought that transactional replication is what was set up
> but perhaps this is not the case?
> Can the specific kind of replication be altered in situ or would it need
> to be dropped and recreated?
> Again many thanks for any help!
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***
Using SQL Server 2000 SP3.
Replication is set up on a few databases, on each of the tables that are
replicated the following trigger appears:
create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
update as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [dbo].[Voucher]
set msrepl_tran_version = newid()
from [dbo].[Voucher], inserted
where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
This trigger is causing a significant slowdown when called and I would
like to remove it. However I do not want to break the replication.
I need to understand what has created this trigger and why. I belive
that this has been implemented as a workaround for some problem that was
encountered by the replication process but this is merely speculation on
my behalf.
Your help is very much appreciated.
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
It looks like you are using immediate or queued updating which puts these
triggers in place on the publisher. I can't comment on Microsoft's logic
behind this proc. Basically what it does is assigns a new value to the guid
column if the guid column it self is not modified/supplied.
I would look at whether you really need immediate updating or bi-directional
replication in general as bi-directional transactional replication offers
some of the functionality but will not cause the performance degradation.
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
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:uU1y0aSSGHA.4956@.TK2MSFTNGP09.phx.gbl...
>
> HI All,
> Using SQL Server 2000 SP3.
> Replication is set up on a few databases, on each of the tables that are
> replicated the following trigger appears:
> create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
> update as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [dbo].[Voucher]
> set msrepl_tran_version = newid()
> from [dbo].[Voucher], inserted
> where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
>
> This trigger is causing a significant slowdown when called and I would
> like to remove it. However I do not want to break the replication.
> I need to understand what has created this trigger and why. I belive
> that this has been implemented as a workaround for some problem that was
> encountered by the replication process but this is merely speculation on
> my behalf.
> Your help is very much appreciated.
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks for your response here. What is required is one-way replication
as we have a primary server that needs to replicate information to a
backup server.
I presume that this is a different configuration from immediate/queued
updating. I thought that transactional replication is what was set up
but perhaps this is not the case?
Can the specific kind of replication be altered in situ or would it need
to be dropped and recreated?
Again many thanks for any help!
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
|||Use transactional replication if you have no requirement for
bi-directionality. to fix this drop the subscriber, and then disable the
triggers, then redeploy the subscriber as a plain vanilla subscriber.
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
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:OarBgsZSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>
> Thanks for your response here. What is required is one-way replication
> as we have a primary server that needs to replicate information to a
> backup server.
> I presume that this is a different configuration from immediate/queued
> updating. I thought that transactional replication is what was set up
> but perhaps this is not the case?
> Can the specific kind of replication be altered in situ or would it need
> to be dropped and recreated?
> Again many thanks for any help!
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***
Monday, March 19, 2012
Automatic Identity Range Handling warning message.
Ive implemented merge replication with SQL Server and an Access Database.
SQL Server is the publisher and Access database is the subscriber.
I have ONE table in the schema that can be updated in both the subscriber
side as well as the publisher side. I have an IDENTITY column in this table
and I have set the property "Automatic Identity Range Handling" ON for this
table.
Ive also created a script to create this publisher. Now, heres my problem.
When I create the publisher, I get a warning message thats stated below:
"Warning: only Subscribers running SQL Server 2000 can synchronize with
publication '<database>' because automatic identity ranges are being used."
OK, Ive done some resonable testing on this statement and it SEEMS that
synchronization with Automatic Identity Range Handing enabled DOES work for
Access databases as well even though the warning states otherwise! Im using
JET to connect to access and not the desktop engine. Once the threshold is
reached, and after the merge agent kicks in, the indentity seed is reset to
the appropriate values on both the access and the sql server side.
Appropriate check constraints are also placed.
I just want to make sure if this is correct - and this warning is just
bogus.
Thanks,
Girish
Hi Girish,
From your descriptions, I understood that you would like to know whether it
matters when warning is shown for "automatic identity ranges are being
used". Have I understood you? If there is anything I misunderstood, please
feel free to let me know.
Based on my scope, there are some known issues for us by using automatic
identity ranges. You could have a view on them
BUG: Unable to Change Identity Range of Publisher If You Use Auto Identity
Range
http://support.microsoft.com/default...b;en-us;310540
BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs
Continuously
http://support.microsoft.com/default...b;en-us;304706
Additionaly, there is a by design issue for automatic identity range based
on the descriptions in the following documents.
PRB: Automatic Identity Range Handling Is Not Correct If the Merge Agent
Runs an Insert Trigger
http://support.microsoft.com/default...b;en-us;324361
I am afraid the above all are why this warning is shown up.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
SQL Server is the publisher and Access database is the subscriber.
I have ONE table in the schema that can be updated in both the subscriber
side as well as the publisher side. I have an IDENTITY column in this table
and I have set the property "Automatic Identity Range Handling" ON for this
table.
Ive also created a script to create this publisher. Now, heres my problem.
When I create the publisher, I get a warning message thats stated below:
"Warning: only Subscribers running SQL Server 2000 can synchronize with
publication '<database>' because automatic identity ranges are being used."
OK, Ive done some resonable testing on this statement and it SEEMS that
synchronization with Automatic Identity Range Handing enabled DOES work for
Access databases as well even though the warning states otherwise! Im using
JET to connect to access and not the desktop engine. Once the threshold is
reached, and after the merge agent kicks in, the indentity seed is reset to
the appropriate values on both the access and the sql server side.
Appropriate check constraints are also placed.
I just want to make sure if this is correct - and this warning is just
bogus.
Thanks,
Girish
Hi Girish,
From your descriptions, I understood that you would like to know whether it
matters when warning is shown for "automatic identity ranges are being
used". Have I understood you? If there is anything I misunderstood, please
feel free to let me know.
Based on my scope, there are some known issues for us by using automatic
identity ranges. You could have a view on them
BUG: Unable to Change Identity Range of Publisher If You Use Auto Identity
Range
http://support.microsoft.com/default...b;en-us;310540
BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs
Continuously
http://support.microsoft.com/default...b;en-us;304706
Additionaly, there is a by design issue for automatic identity range based
on the descriptions in the following documents.
PRB: Automatic Identity Range Handling Is Not Correct If the Merge Agent
Runs an Insert Trigger
http://support.microsoft.com/default...b;en-us;324361
I am afraid the above all are why this warning is shown up.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Sunday, March 11, 2012
Automatic client redirect in SQL Server 2005
Will this work with replication too. I see it with a mirror database, but could it work if I was keeping two copies of the database and wanted to transparently reroute the connection to a surviving server?We will support such redirection on the publication database, when DB mirroring becomes a supported feature later.|||* If the publication DB is mirrored, client can do automatic redirection between the database on principal server and database on mirror server. BOL has a topic called "Replication and Database Mirroring" on this. As Li said, it will be supported once DB mirroring becomes supported feature.
* If you question is to do automatic redirection between publication DB and subscription DB, the answer is NO for SQL 2005.
Thursday, March 8, 2012
automated replication scripting
We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication? Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach.
You can take a look at RMO programming. For example, Publication object has a method Script() that can be used to create a script (see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.publication.script.aspx). Other replication objects, such as DistributionDatabase, MergePublication, has similar methods.|||At a glance, this appears to apply to SQL Server 2005. I cannot find documentation for SQL Server 2000.|||For SQL 2000, you can use SQL DMO. Check out SQL 2000 BOL and find topic "Script Method (Replication Objects)".
Peng
|||Peng, that was it! Sweet as pie, it worked beautifully using a VBScript in a DTS. Something I have never done, but always wanted to. I have always known that SQLDMO would eventually become a tool I needed and you've put me onto it. Thank you!
automated replication scripting
We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication? Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach.
You can take a look at RMO programming. For example, Publication object has a method Script() that can be used to create a script (see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.publication.script.aspx). Other replication objects, such as DistributionDatabase, MergePublication, has similar methods.|||At a glance, this appears to apply to SQL Server 2005. I cannot find documentation for SQL Server 2000.|||For SQL 2000, you can use SQL DMO. Check out SQL 2000 BOL and find topic "Script Method (Replication Objects)".
Peng
|||Peng, that was it! Sweet as pie, it worked beautifully using a VBScript in a DTS. Something I have never done, but always wanted to. I have always known that SQLDMO would eventually become a tool I needed and you've put me onto it. Thank you!
Wednesday, March 7, 2012
Automate inclusion of new tables?
I'm running snapshot replication, and there will often be new tables (and
other objects) added to the publisher that I'd like to automatically include
in the subscription. Any ideas on how to do this? I've thought about creating
a script that lists all the tables, etc., in a publisher, and then including
each of those in the subscription, but I have no idea if/how it would work.
Any help would be appreciated. Thanks!
You could use sp_addarticle and sp_addsubscription for each new table.
Finding the new tables depends on how you have this set up. If it is all the
tables, you could find tables from information_schema.tables which are not
in sysarticles, and use a cursor to iterate through and run the stored procs
ablve.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
other objects) added to the publisher that I'd like to automatically include
in the subscription. Any ideas on how to do this? I've thought about creating
a script that lists all the tables, etc., in a publisher, and then including
each of those in the subscription, but I have no idea if/how it would work.
Any help would be appreciated. Thanks!
You could use sp_addarticle and sp_addsubscription for each new table.
Finding the new tables depends on how you have this set up. If it is all the
tables, you could find tables from information_schema.tables which are not
in sysarticles, and use a cursor to iterate through and run the stored procs
ablve.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Saturday, February 25, 2012
Autoincrement
Hello
I've a table of Users with an identity key
Some records are inserted by a replication system which sends records with
key like 2-4-6-8 ...
and put them into the table with a INSERT sql
Other records are inserted via web
I need that the records inserted via web takes a key like 1-3-5-7 ...
I've set the identity seed to 1 and identity increment to 2
I've made a test
1. Inserted some record by replication system
2. If I try to insert a new record manually (by enterprise manager) the new
key is a par number instead of an odd
What's wrong?
Can you help me?Why don't you instead of doing that create another field called Origin
make it a bit when it's from the web give it a value of 1 otherwise 0
Your identity will be Old Key + 2 (that's your increment)
http://sqlservercode.blogspot.com/
"Denis" wrote:
> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>|||Denis,
> What's wrong?
Is the property "not for replication" set in this identity column?
When the values are inserted from the replication, sql server takes that
number as the last identity value inserted in the table, so if the las value
was 8 then when you insert from the web using "set identity_insert t1 off"
will increment that value with the identity increment 8+2 and this will be
the next value to be inserted.
Example:
create table t1(
c1 int not null identity(1, 2)
)
go
insert into t1 default values
insert into t1 default values
insert into t1 default values
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 on
go
insert into t1(c1) values(2)
insert into t1(c1) values(4)
insert into t1(c1) values(6)
insert into t1(c1) values(8)
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 off
go
insert into t1 default values
go
select * from t1 order by c1 asc
go
drop table t1
go
AMB
"Denis" wrote:
> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>
I've a table of Users with an identity key
Some records are inserted by a replication system which sends records with
key like 2-4-6-8 ...
and put them into the table with a INSERT sql
Other records are inserted via web
I need that the records inserted via web takes a key like 1-3-5-7 ...
I've set the identity seed to 1 and identity increment to 2
I've made a test
1. Inserted some record by replication system
2. If I try to insert a new record manually (by enterprise manager) the new
key is a par number instead of an odd
What's wrong?
Can you help me?Why don't you instead of doing that create another field called Origin
make it a bit when it's from the web give it a value of 1 otherwise 0
Your identity will be Old Key + 2 (that's your increment)
http://sqlservercode.blogspot.com/
"Denis" wrote:
> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>|||Denis,
> What's wrong?
Is the property "not for replication" set in this identity column?
When the values are inserted from the replication, sql server takes that
number as the last identity value inserted in the table, so if the las value
was 8 then when you insert from the web using "set identity_insert t1 off"
will increment that value with the identity increment 8+2 and this will be
the next value to be inserted.
Example:
create table t1(
c1 int not null identity(1, 2)
)
go
insert into t1 default values
insert into t1 default values
insert into t1 default values
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 on
go
insert into t1(c1) values(2)
insert into t1(c1) values(4)
insert into t1(c1) values(6)
insert into t1(c1) values(8)
go
select
ident_seed('t1'),
ident_incr('t1'),
ident_current('t1')
go
set identity_insert t1 off
go
insert into t1 default values
go
select * from t1 order by c1 asc
go
drop table t1
go
AMB
"Denis" wrote:
> Hello
> I've a table of Users with an identity key
> Some records are inserted by a replication system which sends records with
> key like 2-4-6-8 ...
> and put them into the table with a INSERT sql
> Other records are inserted via web
> I need that the records inserted via web takes a key like 1-3-5-7 ...
> I've set the identity seed to 1 and identity increment to 2
> I've made a test
> 1. Inserted some record by replication system
> 2. If I try to insert a new record manually (by enterprise manager) the ne
w
> key is a par number instead of an odd
> What's wrong?
> Can you help me?
>
>
Subscribe to:
Posts (Atom)