Hi
I have replication set up and working correctly. However we now have a new
SQL Server and want to set up replication to there(instead of the existing
server). Do we have to go through setting up the whole replication process
again or is there an easier way?
Thanks
I would start from scratch... Server names, etc are stored in the
registry... It is often better to be clean...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
> Hi
> I have replication set up and working correctly. However we now have a new
> SQL Server and want to set up replication to there(instead of the existing
> server). Do we have to go through setting up the whole replication process
> again or is there an easier way?
> Thanks
|||Ok Thanks for the advice
"Wayne Snyder" wrote:
> I would start from scratch... Server names, etc are stored in the
> registry... It is often better to be clean...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
>
>
|||Jonjo,
when you say 'set up replication to there' do you mean add a subscription?
If so then adding the subscription is much like any other subscription, and
EM can generate the scripts of an existing subscription to be used as a
template if you like - just change the servername using find and replace and
then run the script on the subscriber. If you mean you want to set up the
second server as a publisher/distributor and have the same publications,
then it depends on the complexity. If it's a few simple publications then
I'd start from scratch. If it's more complex, and you haven't documented
changes to custom profiles, post-snapshot filepaths etc then I'd script out
and edit the script before running it on the new publisher to be sure of
having a replica, and it'll also be much faster.
HTH,
Paul Ibison (SQL Server MVP)
Showing posts with label working. Show all posts
Showing posts with label working. Show all posts
Friday, March 30, 2012
replication to new server
Hi
I have replication set up and working correctly. However we now have a new
SQL Server and want to set up replication to there(instead of the existing
server). Do we have to go through setting up the whole replication process
again or is there an easier way?
ThanksI would start from scratch... Server names, etc are stored in the
registry... It is often better to be clean...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
> Hi
> I have replication set up and working correctly. However we now have a new
> SQL Server and want to set up replication to there(instead of the existing
> server). Do we have to go through setting up the whole replication process
> again or is there an easier way?
> Thanks|||Ok Thanks for the advice
"Wayne Snyder" wrote:
> I would start from scratch... Server names, etc are stored in the
> registry... It is often better to be clean...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
> > Hi
> >
> > I have replication set up and working correctly. However we now have a new
> > SQL Server and want to set up replication to there(instead of the existing
> > server). Do we have to go through setting up the whole replication process
> > again or is there an easier way?
> >
> > Thanks
>
>|||Jonjo,
when you say 'set up replication to there' do you mean add a subscription?
If so then adding the subscription is much like any other subscription, and
EM can generate the scripts of an existing subscription to be used as a
template if you like - just change the servername using find and replace and
then run the script on the subscriber. If you mean you want to set up the
second server as a publisher/distributor and have the same publications,
then it depends on the complexity. If it's a few simple publications then
I'd start from scratch. If it's more complex, and you haven't documented
changes to custom profiles, post-snapshot filepaths etc then I'd script out
and edit the script before running it on the new publisher to be sure of
having a replica, and it'll also be much faster.
HTH,
Paul Ibison (SQL Server MVP)sql
I have replication set up and working correctly. However we now have a new
SQL Server and want to set up replication to there(instead of the existing
server). Do we have to go through setting up the whole replication process
again or is there an easier way?
ThanksI would start from scratch... Server names, etc are stored in the
registry... It is often better to be clean...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
> Hi
> I have replication set up and working correctly. However we now have a new
> SQL Server and want to set up replication to there(instead of the existing
> server). Do we have to go through setting up the whole replication process
> again or is there an easier way?
> Thanks|||Ok Thanks for the advice
"Wayne Snyder" wrote:
> I would start from scratch... Server names, etc are stored in the
> registry... It is often better to be clean...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
> > Hi
> >
> > I have replication set up and working correctly. However we now have a new
> > SQL Server and want to set up replication to there(instead of the existing
> > server). Do we have to go through setting up the whole replication process
> > again or is there an easier way?
> >
> > Thanks
>
>|||Jonjo,
when you say 'set up replication to there' do you mean add a subscription?
If so then adding the subscription is much like any other subscription, and
EM can generate the scripts of an existing subscription to be used as a
template if you like - just change the servername using find and replace and
then run the script on the subscriber. If you mean you want to set up the
second server as a publisher/distributor and have the same publications,
then it depends on the complexity. If it's a few simple publications then
I'd start from scratch. If it's more complex, and you haven't documented
changes to custom profiles, post-snapshot filepaths etc then I'd script out
and edit the script before running it on the new publisher to be sure of
having a replica, and it'll also be much faster.
HTH,
Paul Ibison (SQL Server MVP)sql
Wednesday, March 28, 2012
Replication taking longer
I've got once a day snapshot replication working between 2 servers. I
take a copy of most of a database for MI and adhoc query processing
purposes. This has been working okay for several months now but I've
noticed that the time to load the database is gradually taking longer.
I don't think this is a product of the source database growing, because
whilst it has grown by 2-3%, the time taken to load the target database
is taking 80-90% longer than it did a month ago. I thought this may be
due to some physical file structure problem and I reindexed all tables
with no effect. I've also noticed that a DTS package that I'm using to
copy a much smaller database across to the same MI server has also
increased in duration over the same time period.
Any ideas to solve this would be welcome.
TIA
Laurence Breeze
Laurence,
indexing the tables wouldn't speed up the snapshot application at the
subscriber - actually quite the opposite
. Perhaps you can monitor which
part of the process is taking more time than expected. You could just
monitor it using dbcc inputbuffer at the subscriber while it processes to
get a feel for it. If the DTS task has increased in time then it could be
network issues or the hardware on the subscriber. Loads of possibilities
here. You could copy a file to the server to test the network transfer
speed. I'd also monitor the disk writes/sec as maybe the problem lies there.
Disk fragmentation could be worth looking at, but I wouldn't expect this to
be such a major factor. Finally, check the general performance of the server
outside of data transfers - just adding data to a dummy table - and compare
to another (similar) server.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
take a copy of most of a database for MI and adhoc query processing
purposes. This has been working okay for several months now but I've
noticed that the time to load the database is gradually taking longer.
I don't think this is a product of the source database growing, because
whilst it has grown by 2-3%, the time taken to load the target database
is taking 80-90% longer than it did a month ago. I thought this may be
due to some physical file structure problem and I reindexed all tables
with no effect. I've also noticed that a DTS package that I'm using to
copy a much smaller database across to the same MI server has also
increased in duration over the same time period.
Any ideas to solve this would be welcome.
TIA
Laurence Breeze
Laurence,
indexing the tables wouldn't speed up the snapshot application at the
subscriber - actually quite the opposite
. Perhaps you can monitor whichpart of the process is taking more time than expected. You could just
monitor it using dbcc inputbuffer at the subscriber while it processes to
get a feel for it. If the DTS task has increased in time then it could be
network issues or the hardware on the subscriber. Loads of possibilities
here. You could copy a file to the server to test the network transfer
speed. I'd also monitor the disk writes/sec as maybe the problem lies there.
Disk fragmentation could be worth looking at, but I wouldn't expect this to
be such a major factor. Finally, check the general performance of the server
outside of data transfers - just adding data to a dummy table - and compare
to another (similar) server.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Replication success but Replication monitor fails.....
Hi All,
My replication process working well , meaning it replicates the
data properly but it is not reflected in my 'Replication Monitor' , it always
shows failure (Red shadow on the 'Publishers' and 'Agents-->Distribution
Agents' .Note that the subsriber using 'push' method to get the data ,
distribution data base sitting on Publisher's server.We delete and re-create
the whole thong again but not useful.Any idea?...
The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this should also remove the problem.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
My replication process working well , meaning it replicates the
data properly but it is not reflected in my 'Replication Monitor' , it always
shows failure (Red shadow on the 'Publishers' and 'Agents-->Distribution
Agents' .Note that the subsriber using 'push' method to get the data ,
distribution data base sitting on Publisher's server.We delete and re-create
the whole thong again but not useful.Any idea?...
The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this should also remove the problem.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Replication Subsription using SQL DMO
does any body have the complete working sample of VB6 for pull replication
synchronisation on subscribor side. Server allows anonymous subscription.
Regards,
-Imran
here is one using FTP. This is vbscript.
Option explicit
Const SQLDMOPubAttrib_AllowAnonymous = 4
Const SQLDMOPubAttrib_AllowPull = 2
Const SQLDMOPubAttrib_ImmediateSync = 16
Const SQLDMOPubAttrib_IndependentAgent = 32
Const SQLDMOPubAttrib_InternetEnabled = 8
Const SQLDMOCreationScript_PrimaryObject =1
Const SQLDMOCreationScript_DRI_Defaults = 2048
Const SQLDMOCreationScript_DRI_ForeignKeys = 512
Const SQLDMOCreationScript_ClusteredIndexes = 16
Const SQLDMOCreationScript_NonClusteredIndexes = 64
Const SQLDMOCreationScript_DRI_Checks = 1024
Const SQLDMOCreationScript_PKUKAsConstraints = 32768
Const SQLDMOCreationScript_UDDTsToBaseTypes = 32
Const SQLDMOTranSubscriber_Default = 0
Const SQLDMOSubscription_Pull = 1
Const SQLDMOSubscription_Anonymous = 2
Const SQLDMOReplSecurity_Normal = 0
Const SQLDMOReplSecurity_Integrated = 1
Const SQLDMOSubscription_All = 3
Dim objServer, objReplication, objSubscriber, _ objReplicationDatabases,
objReplicationDatabase, _
objTransPublications,objTransPublication,objTransA rticle, objTransArticles,
objTransSusbcriptions,_ objTransPullSubscription,objTransPullSubscriptions ,_
objReplicationSecurity, objJobServer, objJobs, objJob,_ property
set objServer = CreateObject("SQLDMO.SQLServer")
objServer.Connect "hilary2kp", "sa","se1cure#"
set objReplication=objServer.Replication
set objReplicationDatabases=objReplication._
ReplicationDatabases
set objReplicationDatabase=objReplicationDatabases("Pu bs")
set objTransPublications=objReplicationDatabase._
TransPublications
set objTransPublication=_
CreateObject("SQLDMO.TransPublication2")
set objTransArticle=CreateObject("SQLDMO.TransArticle2 ")
with objTransArticle
.Name="authors"
.SourceObjectName="authors"
.SourceObjectOwner="dbo"
.CreationScriptOptions=_
SQLDMOCreationScript_PrimaryObject+_
SQLDMOCreationScript_DRI_Defaults + _
SQLDMOCreationScript_DRI_ForeignKeys + _
SQLDMOCreationScript_ClusteredIndexes + _
SQLDMOCreationScript_DRI_Checks +_
SQLDMOCreationScript_PKUKAsConstraints +_
SQLDMOCreationScript_UDDTsToBaseTypes +_
SQLDMOCreationScript_NonClusteredIndexes
end with
with objTransPublication
.Name="test"
.AltSnapShotFolder="C:\inetpub\ftproot"
.Enabled=TRUE
.FTPAddress="hilary2kp"
.FTPLogin="Anonymous"
.FTPSubdirectory="/ftp"
.PublicationAttributes=SQLDMOPubAttrib_AllowAnonym ous+_
SQLDMOPubAttrib_AllowPull+_
SQLDMOPubAttrib_ImmediateSync+_
SQLDMOPubAttrib_IndependentAgent+_
SQLDMOPubAttrib_InternetEnabled
.RetentionPeriod=14
end with
objTransPublications.Add objTransPublication
objTransPublication.TransArticles.Add objTransArticle
'Starting the snapshot job
set objJobServer=objServer.JobServer
set objJobs=objJobServer.Jobs
for each objJob in objJobs
if objJob.JobID=objTranspublication.SnapshotJobID then
objJob.Start
end if
next
set objJob=Nothing
set objJobs=Nothing
set objJobServer=Nothing
set objTransArticle=Nothing
set objTransPublications=Nothing
set objTransPublication=Nothing
set objReplication=Nothing
objServer.Disconnect
objServer.Connect "Hilary2kp\test","sa","se1cure#"
set objReplication=objServer.Replication
set objReplicationDatabases=objReplication._
ReplicationDatabases
set objReplicationDatabase=objReplicationDatabases("su b")
set objTransPullSubscription=_
CreateObject("SQLDMO.TransPullSubscription2")
set objReplicationSecurity=objTransPullSubscription._
DistributorSecurity
objReplicationSecurity.SecurityMode=_
SQLDMOReplSecurity_Normal
objReplicationSecurity.StandardLogin="sa"
objReplicationSecurity.StandardPassword="se1cure#"
with objTransPullSubscription
.FTPAddress="hilary2kp"
.FTPLogin="Anonymous"
.Publisher= "hilary2kp"
.Distributor="hilary2kp"
.DistributorSecurity.SecurityMode=_
SQLDMOReplSecurity_Normal
.DistributorSecurity.StandardLogin="sa"
.DistributorSecurity.StandardPassword="se1cure#"
.PublicationDB="pubs"
.Publication="test"
.PublisherSecurity.SecurityMode=_
SQLDMOReplSecurity_Normal
.PublisherSecurity.StandardLogin="sa"
.PublisherSecurity.StandardPassword="se1cure#"
.SubscriberType = SQLDMOTranSubscriber_Default
.SubscriptionType = SQLDMOSubscription_Anonymous
.PublicationAttributes=SQLDMOPubAttrib_AllowAnonym ous+_
SQLDMOPubAttrib_AllowPull+_
SQLDMOPubAttrib_ImmediateSync+_
SQLDMOPubAttrib_IndependentAgent+_
SQLDMOPubAttrib_InternetEnabled
.SubscriberSecurityMode=SQLDMOReplSecurity_Normal
.SubscriberLogin="sa"
.SubscriberPassword="se1cure#"
.UseFTP=True
end with
set objTransPullSubscriptions=objReplicationDatabase._
TransPullSubscriptions
objTransPullSubscriptions.Add objTransPullSubscription
set objTransPullSubscription=Nothing
set objTransPullSubscriptions=Nothing
set objReplicationDatabases=Nothing
set objReplication=Nothing
set objServer=Nothing
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Imran Iqbal" <imraniqbalsgd@.hotmail.com> wrote in message
news:uH0bVMIdEHA.3392@.tk2msftngp13.phx.gbl...
> does any body have the complete working sample of VB6 for pull replication
> synchronisation on subscribor side. Server allows anonymous subscription.
> Regards,
> -Imran
>
|||Imran,
I don't have it in vbscript but here is some .NET code to do a publication
and a pull:
Dim objServer As New SQLDMO.SQLServer
Dim objTransPublications As SQLDMO.TransPublications
Dim objTransArticle As New SQLDMO.TransArticle2
Dim objTransPullSubscription As New SQLDMO.TransPullSubscription2
Dim objReplication As SQLDMO.Replication
Dim objSubscriber As SQLDMO.Subscriber2
Dim objReplicationDatabases As SQLDMO.ReplicationDatabases
Dim objReplicationSubDatabase As SQLDMO.ReplicationDatabase2
Dim objReplicationPubDatabase As SQLDMO.ReplicationDatabase2
Dim objTransPublication As New SQLDMO.TransPublication2
Dim objTransArticles As SQLDMO.TransArticles
Dim objTransSusbcriptions As SQLDMO.TransSubscriptions
Dim objTransPullSubscriptions As SQLDMO.TransPullSubscriptions
Dim objTransSubscription As New SQLDMO.TransSubscription2
Dim objReplicationSecurity As SQLDMO.ReplicationSecurity
Dim objJobServer As SQLDMO.JobServer2
Dim objJobs As SQLDMO.Jobs
Dim objJob As New SQLDMO.Job
objServer.Connect("PLL-DT-16", "sa", "sa")
objReplication = objServer.Replication
objReplicationDatabases = objReplication.ReplicationDatabases
objReplicationPubDatabase = objReplicationDatabases.Item("Northwind")
objTransPublications = objReplicationPubDatabase.TransPublications
With objTransArticle
..Name = "Region"
..SourceObjectName = "Region"
..SourceObjectOwner = "dbo"
..CreationScriptOptions =
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_Pr imaryObject + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_Defaults + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_ForeignKeys + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_Cl usteredIndexes + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_Checks + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_PK UKAsConstraints + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_UD DTsToBaseTypes + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_No nClusteredIndexes
End With
With objTransPublication
..Name = "test"
..Enabled = True
..PublicationAttributes =
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowAnonymo us + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowPull + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowPush + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_ImmediateSyn c + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_IndependentA gent
..RetentionPeriod = 14
End With
objTransPublications.Add(objTransPublication)
objTransPublication.TransArticles.Add(objTransArti cle)
objTransPublications.Refresh()
objReplicationPubDatabase.RefreshChildren()
'Starting the snapshot job
objJobServer = objServer.JobServer
objJobs = objJobServer.Jobs
For Each objJob In objJobs
If objJob.JobID = objTransPublication.SnapshotJobID Then
objJob.Start()
End If
Next
objReplicationSubDatabase = objReplicationDatabases.Item("testrep")
With objTransPullSubscription
..Publisher = "PLL-DT-16"
..Distributor = "PLL-DT-16"
..DistributorSecurity.SecurityMode =
SQLDMO_REPLSECURITY_TYPE.SQLDMOReplSecurity_Integr ated
'SQLDMOReplSecurity_Normal
..PublicationDB = "Northwind"
..Publication = "test"
..SubscriberType = SQLDMO_TRANSUBSCRIBER_TYPE.SQLDMOTranSubscriber_Re adOnly
..PublisherSecurity.SecurityMode =
SQLDMO_REPLSECURITY_TYPE.SQLDMOReplSecurity_Integr ated
'SQLDMOReplSecurity_Normal
..SubscriptionType = SQLDMO_SUBSCRIPTION_TYPE.SQLDMOSubscription_Pull
..SubscriberSecurityMode = SQLDMO_SECURITY_TYPE.SQLDMOSecurity_Integrated
..PublicationAttributes = SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_ImmediateSyn c
+ SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_IndependentA gent
End With
objTransPullSubscriptions = objReplicationSubDatabase.TransPullSubscriptions
objTransPullSubscriptions.Add(objTransPullSubscrip tion)
objTransPullSubscriptions.Refresh()
objReplicationPubDatabase.EnableTransSubscription( "PLL-DT-16", "testrep",
"test", SQLDMO_SUBSCRIPTION_TYPE.SQLDMOSubscription_Pull)
objReplicationPubDatabase.RefreshChildren()
objJob = Nothing
objJobs = Nothing
objJobServer = Nothing
objTransArticle = Nothing
objTransPublications = Nothing
objTransPublication = Nothing
objTransPullSubscription = Nothing
objTransPullSubscriptions = Nothing
objReplicationDatabases = Nothing
objReplication = Nothing
objServer.DisConnect()
objServer = Nothing
End Sub
HTH,
Paul Ibison
synchronisation on subscribor side. Server allows anonymous subscription.
Regards,
-Imran
here is one using FTP. This is vbscript.
Option explicit
Const SQLDMOPubAttrib_AllowAnonymous = 4
Const SQLDMOPubAttrib_AllowPull = 2
Const SQLDMOPubAttrib_ImmediateSync = 16
Const SQLDMOPubAttrib_IndependentAgent = 32
Const SQLDMOPubAttrib_InternetEnabled = 8
Const SQLDMOCreationScript_PrimaryObject =1
Const SQLDMOCreationScript_DRI_Defaults = 2048
Const SQLDMOCreationScript_DRI_ForeignKeys = 512
Const SQLDMOCreationScript_ClusteredIndexes = 16
Const SQLDMOCreationScript_NonClusteredIndexes = 64
Const SQLDMOCreationScript_DRI_Checks = 1024
Const SQLDMOCreationScript_PKUKAsConstraints = 32768
Const SQLDMOCreationScript_UDDTsToBaseTypes = 32
Const SQLDMOTranSubscriber_Default = 0
Const SQLDMOSubscription_Pull = 1
Const SQLDMOSubscription_Anonymous = 2
Const SQLDMOReplSecurity_Normal = 0
Const SQLDMOReplSecurity_Integrated = 1
Const SQLDMOSubscription_All = 3
Dim objServer, objReplication, objSubscriber, _ objReplicationDatabases,
objReplicationDatabase, _
objTransPublications,objTransPublication,objTransA rticle, objTransArticles,
objTransSusbcriptions,_ objTransPullSubscription,objTransPullSubscriptions ,_
objReplicationSecurity, objJobServer, objJobs, objJob,_ property
set objServer = CreateObject("SQLDMO.SQLServer")
objServer.Connect "hilary2kp", "sa","se1cure#"
set objReplication=objServer.Replication
set objReplicationDatabases=objReplication._
ReplicationDatabases
set objReplicationDatabase=objReplicationDatabases("Pu bs")
set objTransPublications=objReplicationDatabase._
TransPublications
set objTransPublication=_
CreateObject("SQLDMO.TransPublication2")
set objTransArticle=CreateObject("SQLDMO.TransArticle2 ")
with objTransArticle
.Name="authors"
.SourceObjectName="authors"
.SourceObjectOwner="dbo"
.CreationScriptOptions=_
SQLDMOCreationScript_PrimaryObject+_
SQLDMOCreationScript_DRI_Defaults + _
SQLDMOCreationScript_DRI_ForeignKeys + _
SQLDMOCreationScript_ClusteredIndexes + _
SQLDMOCreationScript_DRI_Checks +_
SQLDMOCreationScript_PKUKAsConstraints +_
SQLDMOCreationScript_UDDTsToBaseTypes +_
SQLDMOCreationScript_NonClusteredIndexes
end with
with objTransPublication
.Name="test"
.AltSnapShotFolder="C:\inetpub\ftproot"
.Enabled=TRUE
.FTPAddress="hilary2kp"
.FTPLogin="Anonymous"
.FTPSubdirectory="/ftp"
.PublicationAttributes=SQLDMOPubAttrib_AllowAnonym ous+_
SQLDMOPubAttrib_AllowPull+_
SQLDMOPubAttrib_ImmediateSync+_
SQLDMOPubAttrib_IndependentAgent+_
SQLDMOPubAttrib_InternetEnabled
.RetentionPeriod=14
end with
objTransPublications.Add objTransPublication
objTransPublication.TransArticles.Add objTransArticle
'Starting the snapshot job
set objJobServer=objServer.JobServer
set objJobs=objJobServer.Jobs
for each objJob in objJobs
if objJob.JobID=objTranspublication.SnapshotJobID then
objJob.Start
end if
next
set objJob=Nothing
set objJobs=Nothing
set objJobServer=Nothing
set objTransArticle=Nothing
set objTransPublications=Nothing
set objTransPublication=Nothing
set objReplication=Nothing
objServer.Disconnect
objServer.Connect "Hilary2kp\test","sa","se1cure#"
set objReplication=objServer.Replication
set objReplicationDatabases=objReplication._
ReplicationDatabases
set objReplicationDatabase=objReplicationDatabases("su b")
set objTransPullSubscription=_
CreateObject("SQLDMO.TransPullSubscription2")
set objReplicationSecurity=objTransPullSubscription._
DistributorSecurity
objReplicationSecurity.SecurityMode=_
SQLDMOReplSecurity_Normal
objReplicationSecurity.StandardLogin="sa"
objReplicationSecurity.StandardPassword="se1cure#"
with objTransPullSubscription
.FTPAddress="hilary2kp"
.FTPLogin="Anonymous"
.Publisher= "hilary2kp"
.Distributor="hilary2kp"
.DistributorSecurity.SecurityMode=_
SQLDMOReplSecurity_Normal
.DistributorSecurity.StandardLogin="sa"
.DistributorSecurity.StandardPassword="se1cure#"
.PublicationDB="pubs"
.Publication="test"
.PublisherSecurity.SecurityMode=_
SQLDMOReplSecurity_Normal
.PublisherSecurity.StandardLogin="sa"
.PublisherSecurity.StandardPassword="se1cure#"
.SubscriberType = SQLDMOTranSubscriber_Default
.SubscriptionType = SQLDMOSubscription_Anonymous
.PublicationAttributes=SQLDMOPubAttrib_AllowAnonym ous+_
SQLDMOPubAttrib_AllowPull+_
SQLDMOPubAttrib_ImmediateSync+_
SQLDMOPubAttrib_IndependentAgent+_
SQLDMOPubAttrib_InternetEnabled
.SubscriberSecurityMode=SQLDMOReplSecurity_Normal
.SubscriberLogin="sa"
.SubscriberPassword="se1cure#"
.UseFTP=True
end with
set objTransPullSubscriptions=objReplicationDatabase._
TransPullSubscriptions
objTransPullSubscriptions.Add objTransPullSubscription
set objTransPullSubscription=Nothing
set objTransPullSubscriptions=Nothing
set objReplicationDatabases=Nothing
set objReplication=Nothing
set objServer=Nothing
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Imran Iqbal" <imraniqbalsgd@.hotmail.com> wrote in message
news:uH0bVMIdEHA.3392@.tk2msftngp13.phx.gbl...
> does any body have the complete working sample of VB6 for pull replication
> synchronisation on subscribor side. Server allows anonymous subscription.
> Regards,
> -Imran
>
|||Imran,
I don't have it in vbscript but here is some .NET code to do a publication
and a pull:
Dim objServer As New SQLDMO.SQLServer
Dim objTransPublications As SQLDMO.TransPublications
Dim objTransArticle As New SQLDMO.TransArticle2
Dim objTransPullSubscription As New SQLDMO.TransPullSubscription2
Dim objReplication As SQLDMO.Replication
Dim objSubscriber As SQLDMO.Subscriber2
Dim objReplicationDatabases As SQLDMO.ReplicationDatabases
Dim objReplicationSubDatabase As SQLDMO.ReplicationDatabase2
Dim objReplicationPubDatabase As SQLDMO.ReplicationDatabase2
Dim objTransPublication As New SQLDMO.TransPublication2
Dim objTransArticles As SQLDMO.TransArticles
Dim objTransSusbcriptions As SQLDMO.TransSubscriptions
Dim objTransPullSubscriptions As SQLDMO.TransPullSubscriptions
Dim objTransSubscription As New SQLDMO.TransSubscription2
Dim objReplicationSecurity As SQLDMO.ReplicationSecurity
Dim objJobServer As SQLDMO.JobServer2
Dim objJobs As SQLDMO.Jobs
Dim objJob As New SQLDMO.Job
objServer.Connect("PLL-DT-16", "sa", "sa")
objReplication = objServer.Replication
objReplicationDatabases = objReplication.ReplicationDatabases
objReplicationPubDatabase = objReplicationDatabases.Item("Northwind")
objTransPublications = objReplicationPubDatabase.TransPublications
With objTransArticle
..Name = "Region"
..SourceObjectName = "Region"
..SourceObjectOwner = "dbo"
..CreationScriptOptions =
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_Pr imaryObject + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_Defaults + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_ForeignKeys + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_Cl usteredIndexes + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_Checks + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_PK UKAsConstraints + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_UD DTsToBaseTypes + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_No nClusteredIndexes
End With
With objTransPublication
..Name = "test"
..Enabled = True
..PublicationAttributes =
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowAnonymo us + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowPull + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowPush + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_ImmediateSyn c + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_IndependentA gent
..RetentionPeriod = 14
End With
objTransPublications.Add(objTransPublication)
objTransPublication.TransArticles.Add(objTransArti cle)
objTransPublications.Refresh()
objReplicationPubDatabase.RefreshChildren()
'Starting the snapshot job
objJobServer = objServer.JobServer
objJobs = objJobServer.Jobs
For Each objJob In objJobs
If objJob.JobID = objTransPublication.SnapshotJobID Then
objJob.Start()
End If
Next
objReplicationSubDatabase = objReplicationDatabases.Item("testrep")
With objTransPullSubscription
..Publisher = "PLL-DT-16"
..Distributor = "PLL-DT-16"
..DistributorSecurity.SecurityMode =
SQLDMO_REPLSECURITY_TYPE.SQLDMOReplSecurity_Integr ated
'SQLDMOReplSecurity_Normal
..PublicationDB = "Northwind"
..Publication = "test"
..SubscriberType = SQLDMO_TRANSUBSCRIBER_TYPE.SQLDMOTranSubscriber_Re adOnly
..PublisherSecurity.SecurityMode =
SQLDMO_REPLSECURITY_TYPE.SQLDMOReplSecurity_Integr ated
'SQLDMOReplSecurity_Normal
..SubscriptionType = SQLDMO_SUBSCRIPTION_TYPE.SQLDMOSubscription_Pull
..SubscriberSecurityMode = SQLDMO_SECURITY_TYPE.SQLDMOSecurity_Integrated
..PublicationAttributes = SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_ImmediateSyn c
+ SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_IndependentA gent
End With
objTransPullSubscriptions = objReplicationSubDatabase.TransPullSubscriptions
objTransPullSubscriptions.Add(objTransPullSubscrip tion)
objTransPullSubscriptions.Refresh()
objReplicationPubDatabase.EnableTransSubscription( "PLL-DT-16", "testrep",
"test", SQLDMO_SUBSCRIPTION_TYPE.SQLDMOSubscription_Pull)
objReplicationPubDatabase.RefreshChildren()
objJob = Nothing
objJobs = Nothing
objJobServer = Nothing
objTransArticle = Nothing
objTransPublications = Nothing
objTransPublication = Nothing
objTransPullSubscription = Nothing
objTransPullSubscriptions = Nothing
objReplicationDatabases = Nothing
objReplication = Nothing
objServer.DisConnect()
objServer = Nothing
End Sub
HTH,
Paul Ibison
Monday, March 26, 2012
Replication Setup Over a Firewall
In trying to get replication working through a firewall, we followed the instructions f
ound in at: http://support.microsoft.com/defaul...b;en-us;Q164667
However, this doesn't seem to work. Of course, this article applies to SQL
6.5, so not sure if there are updated instructions for SQL 2000. Can anyon
e give me guidance on this?
If someone can tell me that this SHOULD work, then I can post more informati
on about what we're seeing. Thanks for any help.Hi
Here is a new article that discusses this topic.
321822 HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted
http://support.microsoft.com/?id=321822
HTH
Nick Schueler
--
| Thread-Topic: Replication Setup Over a Firewall
| thread-index: AcP6UkHmtqF4bCovR/yQr6n2IZhciA==
| X-Tomcat-NG: microsoft.public.sqlserver.connect
| From: "examnotes" <anonymous@.discussions.microsoft.com>
| Subject: Replication Setup Over a Firewall
| Date: Mon, 23 Feb 2004 13:16:09 -0800
| Lines: 7
| Message-ID: <154B63D7-63E9-40E3-AFC5-253EDEBD2650@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.connect:40244
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| In trying to get replication working through a firewall, we followed the
instructions found in at:
http://support.microsoft.com/defaul...b;en-us;Q164667
However, this doesn't seem to work. Of course, this article applies to SQL
6.5, so not sure if there are updated instructions for SQL 2000. Can
anyone give me guidance on this?
If someone can tell me that this SHOULD work, then I can post more
information about what we're seeing. Thanks for any help.
|
ound in at: http://support.microsoft.com/defaul...b;en-us;Q164667
However, this doesn't seem to work. Of course, this article applies to SQL
6.5, so not sure if there are updated instructions for SQL 2000. Can anyon
e give me guidance on this?
If someone can tell me that this SHOULD work, then I can post more informati
on about what we're seeing. Thanks for any help.Hi
Here is a new article that discusses this topic.
321822 HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted
http://support.microsoft.com/?id=321822
HTH
Nick Schueler
--
| Thread-Topic: Replication Setup Over a Firewall
| thread-index: AcP6UkHmtqF4bCovR/yQr6n2IZhciA==
| X-Tomcat-NG: microsoft.public.sqlserver.connect
| From: "examnotes" <anonymous@.discussions.microsoft.com>
| Subject: Replication Setup Over a Firewall
| Date: Mon, 23 Feb 2004 13:16:09 -0800
| Lines: 7
| Message-ID: <154B63D7-63E9-40E3-AFC5-253EDEBD2650@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.connect:40244
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| In trying to get replication working through a firewall, we followed the
instructions found in at:
http://support.microsoft.com/defaul...b;en-us;Q164667
However, this doesn't seem to work. Of course, this article applies to SQL
6.5, so not sure if there are updated instructions for SQL 2000. Can
anyone give me guidance on this?
If someone can tell me that this SHOULD work, then I can post more
information about what we're seeing. Thanks for any help.
|
Replication Scripts Stopped Working..
I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.
The actual command i am executing is a simple
sp_addpublication_snapshot
but it fails with
Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!
I am using SQL 2005 SP2 if that makes any difference.
Thanks
Andy
It looks like you have an uncommitted transaction hanging around. Open a query window and type:
Commit Tran
GO
It will either tell you that there are no transactions to commit or commit the uncomplted transaction. At that point, re-run your script.
Hope this helps...
Scott
Replication Scripts Stopped Working..
I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.
The actual command i am executing is a simple
sp_addpublication_snapshot
but it fails with
Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!
I am using SQL 2005 SP2 if that makes any difference.
Thanks
Andy
It looks like you have an uncommitted transaction hanging around. Open a query window and type:
Commit Tran
GO
It will either tell you that there are no transactions to commit or commit the uncomplted transaction. At that point, re-run your script.
Hope this helps...
Scott
Friday, March 23, 2012
Replication Question
I am trying to find a way to easily merge data from one development laptop to another.
Me and another developer are working on a .NET application that uses SQL 2k. Since we are the only two developers, and we both work from home, we spend a lot of time working remotely, so we each have a copy of SQL on our laptops. While this plan works fine for the most part, we are finding that keeping our systems synchronized to be more painful than one would think it should be. Initially, we tried just using DTS packages to copy tables and data from one system to the other, but this would cause one person's data they had to be lost (not a bad thing, but a real pain if you are testing a specific scenario and have to have the data setup a certain way).
We then tried using SQL merge replication, but since the tables are still in active development, modifying, adding, or changing tables is next to impossible (I did find the way to disable the replication temporarily and then re-enable it, but it takes too long just to add a column or something else simple). I would be fine with the replication scenario if someone could suggest a way to easily modify the table schemas with the replication enabled.
I am pretty sure there has to a simple way to do what we are doing, its just that neither of us are familiar with SQL enough to really see how to do it. Is there a way to syncronize each row's data before pushing new schema elements to other servers using DTS, or is there an easy way to enable the replication and still be able to have access to the table's schema?It sounds to me like you are describing Merge replication for existing tables, and a bit of DTS (then establishing a new merge publication for ongoing changes) for new tables.
-PatP|||Exactly. For the normal, day-to-day stuff that we do the merge replication is fine because we are connected over a network (VPN or local LAN), so that works beautifully, I would like to keep it if possible. The pain starts when I have to make an update to the tables or add a new table (which at this point in development is VERY frequent, at least one new table a week and slight modifications to all as time and testing goes along). Unfortunately, we do not have the luxury of working off a plan, so the tables tend to be rather "from the hip", which compounds this problem even more.
I suppose I am more accustomed to LDAP replication (I am learning SQL the hard way), which replicates the schema objects in addition to the data. I am looking for something along those lines, where the master (or in this case Publisher) not only defines the data objects to the subscribers, it defines the schema to be used. I don't see why it would be that much different in SQL.
If I could just get a nod in the right direction, or even a 'it would be possible to do it, but watch for this and that' I would be eternally grateful, this is driving me nuts.sql
Me and another developer are working on a .NET application that uses SQL 2k. Since we are the only two developers, and we both work from home, we spend a lot of time working remotely, so we each have a copy of SQL on our laptops. While this plan works fine for the most part, we are finding that keeping our systems synchronized to be more painful than one would think it should be. Initially, we tried just using DTS packages to copy tables and data from one system to the other, but this would cause one person's data they had to be lost (not a bad thing, but a real pain if you are testing a specific scenario and have to have the data setup a certain way).
We then tried using SQL merge replication, but since the tables are still in active development, modifying, adding, or changing tables is next to impossible (I did find the way to disable the replication temporarily and then re-enable it, but it takes too long just to add a column or something else simple). I would be fine with the replication scenario if someone could suggest a way to easily modify the table schemas with the replication enabled.
I am pretty sure there has to a simple way to do what we are doing, its just that neither of us are familiar with SQL enough to really see how to do it. Is there a way to syncronize each row's data before pushing new schema elements to other servers using DTS, or is there an easy way to enable the replication and still be able to have access to the table's schema?It sounds to me like you are describing Merge replication for existing tables, and a bit of DTS (then establishing a new merge publication for ongoing changes) for new tables.
-PatP|||Exactly. For the normal, day-to-day stuff that we do the merge replication is fine because we are connected over a network (VPN or local LAN), so that works beautifully, I would like to keep it if possible. The pain starts when I have to make an update to the tables or add a new table (which at this point in development is VERY frequent, at least one new table a week and slight modifications to all as time and testing goes along). Unfortunately, we do not have the luxury of working off a plan, so the tables tend to be rather "from the hip", which compounds this problem even more.
I suppose I am more accustomed to LDAP replication (I am learning SQL the hard way), which replicates the schema objects in addition to the data. I am looking for something along those lines, where the master (or in this case Publisher) not only defines the data objects to the subscribers, it defines the schema to be used. I don't see why it would be that much different in SQL.
If I could just get a nod in the right direction, or even a 'it would be possible to do it, but watch for this and that' I would be eternally grateful, this is driving me nuts.sql
Replication question
Dear Friends,
i'm facing a strange thing with merge replication , the
merge agent is running and working successfully
displaying the message 'no data needed to be merged'
although alot of modificatins is been made to the
subscriber. and i stoped the agent and run it again , same
message.
any idea might cause this problem
Thanks
Scroll to the right. There are numbers out there for the volume of
inserts/updates/deletes being moved. What you are seeing is completely
normal since you are seeing the final state after it has run and already
moved data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
i'm facing a strange thing with merge replication , the
merge agent is running and working successfully
displaying the message 'no data needed to be merged'
although alot of modificatins is been made to the
subscriber. and i stoped the agent and run it again , same
message.
any idea might cause this problem
Thanks
Scroll to the right. There are numbers out there for the volume of
inserts/updates/deletes being moved. What you are seeing is completely
normal since you are seeing the final state after it has run and already
moved data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
replication problems with structure/trigger/identity
Hello alltogether,
i'm working on a database-plattform which should work with
fail-over-technics.
Therefor 2 identic machines with internal RAID are available, which work
in single-host-netloadbalance on win2003-server.
Because of the internal RAID and caused by costs, a solution without
external SCSI-RAID and win2003-clustering-mechanism is looked for.
First i tried to use merge-replication but the rowguid-column, which is
added, makes the application unable to use the database (sql-statements
without column-specification). The application is bought and not easy
adaptable.
Second i tried transaction-replication, which seamed to work, but no
key- and identity-attributes are replicated. When the first machine now
stops and second gets master, there are problems caused by the missing
identities (auto-increment). I got errors caused by triggers too.
Manual copy of the structure with key- and identity-attributes and
triggers causes new problems when the replication is active (i.e. a
replication insert datas in a table with a trigger, the trigger on the
master takes effect, the replication tries to copy the effected datas,
but the trigger on the slave already work with the datas).
Actual way is a differential copy periodical from master to slave, but
is not nice and just a periodical actual slave.
Any ideas for more solutions?
PS: Sorry for X-Post, but i'm not sure, if there are "active" reader of
"m.p.s.r"
Thanks & Greets
Marko Damaschke
trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
student of applied computer science at Chemnitz University of Technology
Hello,
i got an answer from Paul Ibison via a website-based newsforum, but
don't want to get a profile there - so the answer here.
Thanks for the idea via Log Shipping but i see there a problem with the
state of standby-database. The database has to be in "NORECOVERY-Mode",
when i understood it correct, while the whole Log-Shipping-process.
But how to "trigger" it, when suddenly the primary machine crashes?
Somehow the recover-mode has to be started.
Write-access is necessary and without manual actions the db is read-only.
We need automated failover without manual administrator actions.
Thanks & Greets
Marko Damaschke
Trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
Student of applied computer science at Chemnitz University of Technology
|||Marko,
the only automatic failover is from clustering. Log-
shipping, replication each have a manual failover. SQL
Server 2005 has database mirroring, but this is a few
months away from release yet. There are 3rd party tools -
DataMirror, but I don't know offhand if these offer
automatic failover.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
i'm working on a database-plattform which should work with
fail-over-technics.
Therefor 2 identic machines with internal RAID are available, which work
in single-host-netloadbalance on win2003-server.
Because of the internal RAID and caused by costs, a solution without
external SCSI-RAID and win2003-clustering-mechanism is looked for.
First i tried to use merge-replication but the rowguid-column, which is
added, makes the application unable to use the database (sql-statements
without column-specification). The application is bought and not easy
adaptable.
Second i tried transaction-replication, which seamed to work, but no
key- and identity-attributes are replicated. When the first machine now
stops and second gets master, there are problems caused by the missing
identities (auto-increment). I got errors caused by triggers too.
Manual copy of the structure with key- and identity-attributes and
triggers causes new problems when the replication is active (i.e. a
replication insert datas in a table with a trigger, the trigger on the
master takes effect, the replication tries to copy the effected datas,
but the trigger on the slave already work with the datas).
Actual way is a differential copy periodical from master to slave, but
is not nice and just a periodical actual slave.
Any ideas for more solutions?
PS: Sorry for X-Post, but i'm not sure, if there are "active" reader of
"m.p.s.r"
Thanks & Greets
Marko Damaschke
trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
student of applied computer science at Chemnitz University of Technology
Hello,
i got an answer from Paul Ibison via a website-based newsforum, but
don't want to get a profile there - so the answer here.
Thanks for the idea via Log Shipping but i see there a problem with the
state of standby-database. The database has to be in "NORECOVERY-Mode",
when i understood it correct, while the whole Log-Shipping-process.
But how to "trigger" it, when suddenly the primary machine crashes?
Somehow the recover-mode has to be started.
Write-access is necessary and without manual actions the db is read-only.
We need automated failover without manual administrator actions.
Thanks & Greets
Marko Damaschke
Trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
Student of applied computer science at Chemnitz University of Technology
|||Marko,
the only automatic failover is from clustering. Log-
shipping, replication each have a manual failover. SQL
Server 2005 has database mirroring, but this is a few
months away from release yet. There are 3rd party tools -
DataMirror, but I don't know offhand if these offer
automatic failover.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Wednesday, March 21, 2012
Replication Problem? Very high undistributed commands --transaction replication
Can anybody help me
we have a transactional replication going on from server A to Server B.
(sql server 2005)
The replication working properly till yesterday..but now i dont find
the recent data at subscriber .. synchronization status shows
"Delivering replicated transactions" When i checked replication monitor
there are so many undistributed commands . replicated commands are not
getting applied at the subscriber..
how to resolve this issue?
thanksJust because you have a high number of undistributed commands doesn't
necessarily mean they are not getting distributed. What it means is that
they are waiting to be distributed. This could be a performance problem, or
it could be that there are a lot of text commands slowing the overall
throughput, or it could mean that you have subscribers which are offline.
The most significant thing you can do to improve throughput is to set
subscriptionstreams to something like 8.
--
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> Can anybody help me
> we have a transactional replication going on from server A to Server B.
> (sql server 2005)
> The replication working properly till yesterday..but now i dont find
> the recent data at subscriber .. synchronization status shows
> "Delivering replicated transactions" When i checked replication monitor
> there are so many undistributed commands . replicated commands are not
> getting applied at the subscriber..
> how to resolve this issue?
> thanks
>|||i dont find the subscriptionstreams option in the distribution agent
profile..is there any other way? should i stop the distribution agent
and start again' or should i reinitialize the subscription? or
should take the snapshot by starting snapshot agent?'
there are around 2207484 undistributed commands
plzz help
thanks
Hilary Cotter wrote:[vbcol=seagreen]
> Just because you have a high number of undistributed commands doesn't
> necessarily mean they are not getting distributed. What it means is that
> they are waiting to be distributed. This could be a performance problem, o
r
> it could be that there are a lot of text commands slowing the overall
> throughput, or it could mean that you have subscribers which are offline.
> The most significant thing you can do to improve throughput is to set
> subscriptionstreams to something like 8.
> --
> 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
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...|||You have to add it manually to the distribution agent like
this -SubscriptionStreams 8
You don't have to create another snapshot, you have to figure out why it is
so slow. For example are their triggers hanging off the subscriber tables?
Are their subscribers who have not connected in some time? Are you
replicating text - run profiler on the subscriber to determine what commands
are flowing right now.
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
>i dont find the subscriptionstreams option in the distribution agent
> profile..is there any other way? should i stop the distribution agent
> and start again' or should i reinitialize the subscription? or
> should take the snapshot by starting snapshot agent?'
> there are around 2207484 undistributed commands
> plzz help
> thanks
>
> Hilary Cotter wrote:
>|||thanks for ur reply..
Could you plzz tell me how do i add the SubscriptionStreams manually'
Yes there are 3 triggers on the tables at the subscriber side.which get
fired when there is an insert/update from the publisher to those
tables..
However, as per replication monitor the status is excellent..latency is
0..only thing is replicated transactions are not gettting applied at
the subscriber..the undistributed transaction count keeps on
growing..i dont see new records aded at the subscriber in last 20-22
hrs'
Earlier the synchronization status used to show the message something
like this 'Delivered 18 transactions with 18 commands"..however now it
always shows this message "Delivering replicated transaction"..
could u plzz tell me wat should i do'
thanks
Hilary Cotter wrote:[vbcol=seagreen]
> You have to add it manually to the distribution agent like
> this -SubscriptionStreams 8
> You don't have to create another snapshot, you have to figure out why it i
s
> so slow. For example are their triggers hanging off the subscriber tables?
> Are their subscribers who have not connected in some time? Are you
> replicating text - run profiler on the subscriber to determine what comman
ds
> are flowing right now.
> --
> 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
>
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
we have a transactional replication going on from server A to Server B.
(sql server 2005)
The replication working properly till yesterday..but now i dont find
the recent data at subscriber .. synchronization status shows
"Delivering replicated transactions" When i checked replication monitor
there are so many undistributed commands . replicated commands are not
getting applied at the subscriber..
how to resolve this issue?
thanksJust because you have a high number of undistributed commands doesn't
necessarily mean they are not getting distributed. What it means is that
they are waiting to be distributed. This could be a performance problem, or
it could be that there are a lot of text commands slowing the overall
throughput, or it could mean that you have subscribers which are offline.
The most significant thing you can do to improve throughput is to set
subscriptionstreams to something like 8.
--
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> Can anybody help me
> we have a transactional replication going on from server A to Server B.
> (sql server 2005)
> The replication working properly till yesterday..but now i dont find
> the recent data at subscriber .. synchronization status shows
> "Delivering replicated transactions" When i checked replication monitor
> there are so many undistributed commands . replicated commands are not
> getting applied at the subscriber..
> how to resolve this issue?
> thanks
>|||i dont find the subscriptionstreams option in the distribution agent
profile..is there any other way? should i stop the distribution agent
and start again' or should i reinitialize the subscription? or
should take the snapshot by starting snapshot agent?'
there are around 2207484 undistributed commands
plzz help
thanks
Hilary Cotter wrote:[vbcol=seagreen]
> Just because you have a high number of undistributed commands doesn't
> necessarily mean they are not getting distributed. What it means is that
> they are waiting to be distributed. This could be a performance problem, o
r
> it could be that there are a lot of text commands slowing the overall
> throughput, or it could mean that you have subscribers which are offline.
> The most significant thing you can do to improve throughput is to set
> subscriptionstreams to something like 8.
> --
> 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
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...|||You have to add it manually to the distribution agent like
this -SubscriptionStreams 8
You don't have to create another snapshot, you have to figure out why it is
so slow. For example are their triggers hanging off the subscriber tables?
Are their subscribers who have not connected in some time? Are you
replicating text - run profiler on the subscriber to determine what commands
are flowing right now.
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
>i dont find the subscriptionstreams option in the distribution agent
> profile..is there any other way? should i stop the distribution agent
> and start again' or should i reinitialize the subscription? or
> should take the snapshot by starting snapshot agent?'
> there are around 2207484 undistributed commands
> plzz help
> thanks
>
> Hilary Cotter wrote:
>|||thanks for ur reply..
Could you plzz tell me how do i add the SubscriptionStreams manually'
Yes there are 3 triggers on the tables at the subscriber side.which get
fired when there is an insert/update from the publisher to those
tables..
However, as per replication monitor the status is excellent..latency is
0..only thing is replicated transactions are not gettting applied at
the subscriber..the undistributed transaction count keeps on
growing..i dont see new records aded at the subscriber in last 20-22
hrs'
Earlier the synchronization status used to show the message something
like this 'Delivered 18 transactions with 18 commands"..however now it
always shows this message "Delivering replicated transaction"..
could u plzz tell me wat should i do'
thanks
Hilary Cotter wrote:[vbcol=seagreen]
> You have to add it manually to the distribution agent like
> this -SubscriptionStreams 8
> You don't have to create another snapshot, you have to figure out why it i
s
> so slow. For example are their triggers hanging off the subscriber tables?
> Are their subscribers who have not connected in some time? Are you
> replicating text - run profiler on the subscriber to determine what comman
ds
> are flowing right now.
> --
> 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
>
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
Replication Problem? Very high undistributed commands --transaction replication
Can anybody help me
we have a transactional replication going on from server A to Server B.
(sql server 2005)
The replication working properly till yesterday..but now i dont find
the recent data at subscriber .. synchronization status shows
"Delivering replicated transactions" When i checked replication monitor
there are so many undistributed commands . replicated commands are not
getting applied at the subscriber..
how to resolve this issue?
thanksJust because you have a high number of undistributed commands doesn't
necessarily mean they are not getting distributed. What it means is that
they are waiting to be distributed. This could be a performance problem, or
it could be that there are a lot of text commands slowing the overall
throughput, or it could mean that you have subscribers which are offline.
The most significant thing you can do to improve throughput is to set
subscriptionstreams to something like 8.
--
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> Can anybody help me
> we have a transactional replication going on from server A to Server B.
> (sql server 2005)
> The replication working properly till yesterday..but now i dont find
> the recent data at subscriber .. synchronization status shows
> "Delivering replicated transactions" When i checked replication monitor
> there are so many undistributed commands . replicated commands are not
> getting applied at the subscriber..
> how to resolve this issue?
> thanks
>|||i dont find the subscriptionstreams option in the distribution agent
profile..is there any other way? should i stop the distribution agent
and start again' or should i reinitialize the subscription? or
should take the snapshot by starting snapshot agent?'
there are around 2207484 undistributed commands
plzz help
thanks
Hilary Cotter wrote:
> Just because you have a high number of undistributed commands doesn't
> necessarily mean they are not getting distributed. What it means is that
> they are waiting to be distributed. This could be a performance problem, or
> it could be that there are a lot of text commands slowing the overall
> throughput, or it could mean that you have subscribers which are offline.
> The most significant thing you can do to improve throughput is to set
> subscriptionstreams to something like 8.
> --
> 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
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> > Can anybody help me
> >
> > we have a transactional replication going on from server A to Server B.
> > (sql server 2005)
> >
> > The replication working properly till yesterday..but now i dont find
> > the recent data at subscriber .. synchronization status shows
> > "Delivering replicated transactions" When i checked replication monitor
> > there are so many undistributed commands . replicated commands are not
> > getting applied at the subscriber..
> >
> > how to resolve this issue?
> >
> > thanks
> >|||You have to add it manually to the distribution agent like
this -SubscriptionStreams 8
You don't have to create another snapshot, you have to figure out why it is
so slow. For example are their triggers hanging off the subscriber tables?
Are their subscribers who have not connected in some time? Are you
replicating text - run profiler on the subscriber to determine what commands
are flowing right now.
--
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
>i dont find the subscriptionstreams option in the distribution agent
> profile..is there any other way? should i stop the distribution agent
> and start again' or should i reinitialize the subscription? or
> should take the snapshot by starting snapshot agent?'
> there are around 2207484 undistributed commands
> plzz help
> thanks
>
> Hilary Cotter wrote:
>> Just because you have a high number of undistributed commands doesn't
>> necessarily mean they are not getting distributed. What it means is that
>> they are waiting to be distributed. This could be a performance problem,
>> or
>> it could be that there are a lot of text commands slowing the overall
>> throughput, or it could mean that you have subscribers which are offline.
>> The most significant thing you can do to improve throughput is to set
>> subscriptionstreams to something like 8.
>> --
>> 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
>> "Vishal" <rvishal1305@.gmail.com> wrote in message
>> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
>> > Can anybody help me
>> >
>> > we have a transactional replication going on from server A to Server B.
>> > (sql server 2005)
>> >
>> > The replication working properly till yesterday..but now i dont find
>> > the recent data at subscriber .. synchronization status shows
>> > "Delivering replicated transactions" When i checked replication monitor
>> > there are so many undistributed commands . replicated commands are not
>> > getting applied at the subscriber..
>> >
>> > how to resolve this issue?
>> >
>> > thanks
>> >
>|||thanks for ur reply..
Could you plzz tell me how do i add the SubscriptionStreams manually'
Yes there are 3 triggers on the tables at the subscriber side.which get
fired when there is an insert/update from the publisher to those
tables..
However, as per replication monitor the status is excellent..latency is
0..only thing is replicated transactions are not gettting applied at
the subscriber..the undistributed transaction count keeps on
growing..i dont see new records aded at the subscriber in last 20-22
hrs'
Earlier the synchronization status used to show the message something
like this 'Delivered 18 transactions with 18 commands"..however now it
always shows this message "Delivering replicated transaction"..
could u plzz tell me wat should i do'
thanks
Hilary Cotter wrote:
> You have to add it manually to the distribution agent like
> this -SubscriptionStreams 8
> You don't have to create another snapshot, you have to figure out why it is
> so slow. For example are their triggers hanging off the subscriber tables?
> Are their subscribers who have not connected in some time? Are you
> replicating text - run profiler on the subscriber to determine what commands
> are flowing right now.
> --
> 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
>
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
> >i dont find the subscriptionstreams option in the distribution agent
> > profile..is there any other way? should i stop the distribution agent
> > and start again' or should i reinitialize the subscription? or
> > should take the snapshot by starting snapshot agent?'
> > there are around 2207484 undistributed commands
> >
> > plzz help
> >
> > thanks
> >
> >
> > Hilary Cotter wrote:
> >> Just because you have a high number of undistributed commands doesn't
> >> necessarily mean they are not getting distributed. What it means is that
> >> they are waiting to be distributed. This could be a performance problem,
> >> or
> >> it could be that there are a lot of text commands slowing the overall
> >> throughput, or it could mean that you have subscribers which are offline.
> >> The most significant thing you can do to improve throughput is to set
> >> subscriptionstreams to something like 8.
> >> --
> >> 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
> >>
> >> "Vishal" <rvishal1305@.gmail.com> wrote in message
> >> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> >> > Can anybody help me
> >> >
> >> > we have a transactional replication going on from server A to Server B.
> >> > (sql server 2005)
> >> >
> >> > The replication working properly till yesterday..but now i dont find
> >> > the recent data at subscriber .. synchronization status shows
> >> > "Delivering replicated transactions" When i checked replication monitor
> >> > there are so many undistributed commands . replicated commands are not
> >> > getting applied at the subscriber..
> >> >
> >> > how to resolve this issue?
> >> >
> >> > thanks
> >> >
> >sql
we have a transactional replication going on from server A to Server B.
(sql server 2005)
The replication working properly till yesterday..but now i dont find
the recent data at subscriber .. synchronization status shows
"Delivering replicated transactions" When i checked replication monitor
there are so many undistributed commands . replicated commands are not
getting applied at the subscriber..
how to resolve this issue?
thanksJust because you have a high number of undistributed commands doesn't
necessarily mean they are not getting distributed. What it means is that
they are waiting to be distributed. This could be a performance problem, or
it could be that there are a lot of text commands slowing the overall
throughput, or it could mean that you have subscribers which are offline.
The most significant thing you can do to improve throughput is to set
subscriptionstreams to something like 8.
--
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> Can anybody help me
> we have a transactional replication going on from server A to Server B.
> (sql server 2005)
> The replication working properly till yesterday..but now i dont find
> the recent data at subscriber .. synchronization status shows
> "Delivering replicated transactions" When i checked replication monitor
> there are so many undistributed commands . replicated commands are not
> getting applied at the subscriber..
> how to resolve this issue?
> thanks
>|||i dont find the subscriptionstreams option in the distribution agent
profile..is there any other way? should i stop the distribution agent
and start again' or should i reinitialize the subscription? or
should take the snapshot by starting snapshot agent?'
there are around 2207484 undistributed commands
plzz help
thanks
Hilary Cotter wrote:
> Just because you have a high number of undistributed commands doesn't
> necessarily mean they are not getting distributed. What it means is that
> they are waiting to be distributed. This could be a performance problem, or
> it could be that there are a lot of text commands slowing the overall
> throughput, or it could mean that you have subscribers which are offline.
> The most significant thing you can do to improve throughput is to set
> subscriptionstreams to something like 8.
> --
> 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
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> > Can anybody help me
> >
> > we have a transactional replication going on from server A to Server B.
> > (sql server 2005)
> >
> > The replication working properly till yesterday..but now i dont find
> > the recent data at subscriber .. synchronization status shows
> > "Delivering replicated transactions" When i checked replication monitor
> > there are so many undistributed commands . replicated commands are not
> > getting applied at the subscriber..
> >
> > how to resolve this issue?
> >
> > thanks
> >|||You have to add it manually to the distribution agent like
this -SubscriptionStreams 8
You don't have to create another snapshot, you have to figure out why it is
so slow. For example are their triggers hanging off the subscriber tables?
Are their subscribers who have not connected in some time? Are you
replicating text - run profiler on the subscriber to determine what commands
are flowing right now.
--
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
>i dont find the subscriptionstreams option in the distribution agent
> profile..is there any other way? should i stop the distribution agent
> and start again' or should i reinitialize the subscription? or
> should take the snapshot by starting snapshot agent?'
> there are around 2207484 undistributed commands
> plzz help
> thanks
>
> Hilary Cotter wrote:
>> Just because you have a high number of undistributed commands doesn't
>> necessarily mean they are not getting distributed. What it means is that
>> they are waiting to be distributed. This could be a performance problem,
>> or
>> it could be that there are a lot of text commands slowing the overall
>> throughput, or it could mean that you have subscribers which are offline.
>> The most significant thing you can do to improve throughput is to set
>> subscriptionstreams to something like 8.
>> --
>> 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
>> "Vishal" <rvishal1305@.gmail.com> wrote in message
>> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
>> > Can anybody help me
>> >
>> > we have a transactional replication going on from server A to Server B.
>> > (sql server 2005)
>> >
>> > The replication working properly till yesterday..but now i dont find
>> > the recent data at subscriber .. synchronization status shows
>> > "Delivering replicated transactions" When i checked replication monitor
>> > there are so many undistributed commands . replicated commands are not
>> > getting applied at the subscriber..
>> >
>> > how to resolve this issue?
>> >
>> > thanks
>> >
>|||thanks for ur reply..
Could you plzz tell me how do i add the SubscriptionStreams manually'
Yes there are 3 triggers on the tables at the subscriber side.which get
fired when there is an insert/update from the publisher to those
tables..
However, as per replication monitor the status is excellent..latency is
0..only thing is replicated transactions are not gettting applied at
the subscriber..the undistributed transaction count keeps on
growing..i dont see new records aded at the subscriber in last 20-22
hrs'
Earlier the synchronization status used to show the message something
like this 'Delivered 18 transactions with 18 commands"..however now it
always shows this message "Delivering replicated transaction"..
could u plzz tell me wat should i do'
thanks
Hilary Cotter wrote:
> You have to add it manually to the distribution agent like
> this -SubscriptionStreams 8
> You don't have to create another snapshot, you have to figure out why it is
> so slow. For example are their triggers hanging off the subscriber tables?
> Are their subscribers who have not connected in some time? Are you
> replicating text - run profiler on the subscriber to determine what commands
> are flowing right now.
> --
> 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
>
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
> >i dont find the subscriptionstreams option in the distribution agent
> > profile..is there any other way? should i stop the distribution agent
> > and start again' or should i reinitialize the subscription? or
> > should take the snapshot by starting snapshot agent?'
> > there are around 2207484 undistributed commands
> >
> > plzz help
> >
> > thanks
> >
> >
> > Hilary Cotter wrote:
> >> Just because you have a high number of undistributed commands doesn't
> >> necessarily mean they are not getting distributed. What it means is that
> >> they are waiting to be distributed. This could be a performance problem,
> >> or
> >> it could be that there are a lot of text commands slowing the overall
> >> throughput, or it could mean that you have subscribers which are offline.
> >> The most significant thing you can do to improve throughput is to set
> >> subscriptionstreams to something like 8.
> >> --
> >> 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
> >>
> >> "Vishal" <rvishal1305@.gmail.com> wrote in message
> >> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> >> > Can anybody help me
> >> >
> >> > we have a transactional replication going on from server A to Server B.
> >> > (sql server 2005)
> >> >
> >> > The replication working properly till yesterday..but now i dont find
> >> > the recent data at subscriber .. synchronization status shows
> >> > "Delivering replicated transactions" When i checked replication monitor
> >> > there are so many undistributed commands . replicated commands are not
> >> > getting applied at the subscriber..
> >> >
> >> > how to resolve this issue?
> >> >
> >> > thanks
> >> >
> >sql
Replication Problem!
Hello Group,
I'm working on two SQL server databases on two different remote
servers. What client needs is that his database on one remote server
should be mirrored to the other one. The changed should be propogated at
regular intervals at the other server.
I'm working on two SQL server databases on two different remote
servers. What client needs is that his database on one remote server
should be mirrored to the other one. The changed should be propogated at
regular intervals at the other server.
For now i'm quite sure that i'll have to use replication to resolve
this issue.
But which type of replication should i use? Transactional, Merge or
Snapshot?
Please note that the client is running his website from the main
server, which is using the source SQL Server database - the one i'll
have to replicate.
Last time when i tried to register the destination server, at my
source server Enterprise Manager, it gave me an error failing to
register the destination server. When i asked the client, all he could
tell me was that both the servers are firewalled and that might have
been the problem.
So just tell me how should i go for it?
Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***If you go the replicated route then your choice (of merge, snapshot or
transactional) is probably straight forward:
- Will they ever want to make changes on the remote server? If yes then you
must use merge replication.
- Otherwise got for Transactional.
Unless the database is small don't go for snapshot. Snapshot is fine for
just that, taking the odd snapshot, but isn't really suited if you want to
regularly propagate changes.
Regarding your problem connecting to the remove server. It is quite likely
that firewalls could be causing you a problem. I've not tried it, but
presumably the most secure way forward would be to create a vpn connection
between the two servers (firewalls may still be an issue) and then replicate
across the vpn link.
Hope this helps,
Brian.
www.cryer.co.uk/brian
"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:d5Sue.8$_r5.2081@.news.uswest.net...
> Hello Group,
> I'm working on two SQL server databases on two different remote
> servers. What client needs is that his database on one remote server
> should be mirrored to the other one. The changed should be propogated at
> regular intervals at the other server.
> For now i'm quite sure that i'll have to use replication to resolve
> this issue.
> But which type of replication should i use? Transactional, Merge or
> Snapshot?
> Please note that the client is running his website from the main
> server, which is using the source SQL Server database - the one i'll
> have to replicate.
> Last time when i tried to register the destination server, at my
> source server Enterprise Manager, it gave me an error failing to
> register the destination server. When i asked the client, all he could
> tell me was that both the servers are firewalled and that might have
> been the problem.
> So just tell me how should i go for it?
> Thanks in advance
> Debian
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hi again!
The problem is that all the source tables dont have a pri key... and
i have learnt that Trans Rep doesnt support replicating tables without
Pri key. Besides there's no question of adding Pri Keys to the database
which is already live!
Second thing is that, the database size is in between 1 and 2GBs. That
means even Snapshot Rep is not suitable!
Shall i go for Merge Rep? I heard that there are conflicts in that?
So then what is the way out?
Thanks Brian, for replying!
Regards
Debian
*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> The problem is that all the source tables dont have a pri key... and
> i have learnt that Trans Rep doesnt support replicating tables without
> Pri key. Besides there's no question of adding Pri Keys to the database
> which is already live!
Well, it want take long before the database is dead. Not having
primary keys is asking for serious problems.
> Second thing is that, the database size is in between 1 and 2GBs. That
> means even Snapshot Rep is not suitable!
> Shall i go for Merge Rep? I heard that there are conflicts in that?
I doubt that merge replication is possible without PKs. If rows can't
be identified, it's getting darn difficult to do replication.
Seems to me you have three options:
1) Add PKs to the database, and do transactional replication.
2) Regularly backup the database and restore on the other end.
3) Log shipping.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns968035B96389Yazorman@.127.0.0.1...
> debian mojo (debian_mojo@.yahoo.com) writes:
>>
>> The problem is that all the source tables dont have a pri key... and
>> i have learnt that Trans Rep doesnt support replicating tables without
>> Pri key. Besides there's no question of adding Pri Keys to the database
>> which is already live!
> Well, it want take long before the database is dead. Not having
> primary keys is asking for serious problems.
>> Second thing is that, the database size is in between 1 and 2GBs. That
>> means even Snapshot Rep is not suitable!
>>
>> Shall i go for Merge Rep? I heard that there are conflicts in that?
> I doubt that merge replication is possible without PKs. If rows can't
> be identified, it's getting darn difficult to do replication.
> Seems to me you have three options:
> 1) Add PKs to the database, and do transactional replication.
> 2) Regularly backup the database and restore on the other end.
> 3) Log shipping.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
I fully agree with everything Erland has said.
You may not currently have primary keys, but surely there are fields that
you are treating as unique? How do you currently uniquely identify a record?
A primary key can be defined using more than one field.
Treat Erland's comment as a dire warning:
> Well, it want take long before the database is dead. Not having
> primary keys is asking for serious problems.
Its put very well, and spot on.
Brian.
www.cryer.co.uk/brian|||Hello Erland and Brian,
Thanks for the reply!
The problem is that there are around 36 user tables in the source
database and only 5 of them have pri keys, the rest are either using
foriegn keys or dont have any kinda keys at all!
Is it good to have pri keys on all tables in the database.
Please do note that the database is for a website and it is estimated
that it will hit 10GB in the first one month!
Is there any chance of replication without compromising the database
integrity or shall i look for any other options such as log shipping?
Please advise!
Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> Please do note that the database is for a website and it is estimated
> that it will hit 10GB in the first one month!
Of course with no Pkeys, the chances for duplicates increase, and so
will the database size.
> Is there any chance of replication without compromising the database
> integrity or shall i look for any other options such as log shipping?
If you don't want to add primary keys (and save the database from a
disaster furtther afield) log shipping or backup/restore is what you
have to look into.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks again, Erland,
Since the database has problems with Pri keys, i'm thinking about Log
Shipping as an alternative.
But as it is mentioned in BOL that for LS to work, it's necessary to
configure three servers:
1. Source Server
2. Monitor Server
3. Destination Server.
Now the destination and the source databases can be on the same
server, but it is mandatory to keep the monitor server separate.
The problem with this approach is that i've access to only two servers
- one source and one destination. I wont have the privilege of having
another one just for setting up the monitor server!
What should i do?
Thanks in advance!
Debian
*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> Since the database has problems with Pri keys, i'm thinking about Log
> Shipping as an alternative.
> But as it is mentioned in BOL that for LS to work, it's necessary to
> configure three servers:
> 1. Source Server
> 2. Monitor Server
> 3. Destination Server.
> Now the destination and the source databases can be on the same
> server, but it is mandatory to keep the monitor server separate.
> The problem with this approach is that i've access to only two servers
> - one source and one destination. I wont have the privilege of having
> another one just for setting up the monitor server!
Looks like you are starting to get some good arguments: "Either we
fix primary keys to the database, or we go and buy some more hardware,
else we can't run this show".
I will have to admit that the need for third machine was news to me,
but I have never set up log shipping myself.
Actually, add PKeys to those tables does not have to be a killer
work. If there are unique indexes, just drop these and recreate
them as primary keys. If there are not any primary keys, just add
an uniqueidentifier with the default of NEWID() to the tables, and
make that the primary key (nonclustered!). That does not really help
to make the data model any better, but at least you can set up
replication over this dying grace.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
That's true that moving away from the problem is not a solution. But
you see that the database i'm talking about is a 24 x 7 database, and
i'm not pretty sure how apps that are using this db.
If i do pri keys to the tables, is there any possibility that the apps
may suffer is some way.
I do agree that in most of the cases there's no harm... but you see
the database is a production one and i'm really paranoid about it's
safety!
I hope you understand!
BTW, your advice was an eye-opener for me.
So what do you suggest? what are the other things i should take care
of before adding the pri keys?
Debian
*** Sent via Developersdex http://www.developersdex.com ***|||what are the other things i should take care of on the app side, before
adding the pri keys?
Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***|||"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:aeawe.4$jU.2182@.news.uswest.net...
> Thanks Erland,
> That's true that moving away from the problem is not a solution. But
> you see that the database i'm talking about is a 24 x 7 database, and
> i'm not pretty sure how apps that are using this db.
> If i do pri keys to the tables, is there any possibility that the apps
> may suffer is some way.
> I do agree that in most of the cases there's no harm... but you see
> the database is a production one and i'm really paranoid about it's
> safety!
> I hope you understand!
> BTW, your advice was an eye-opener for me.
> So what do you suggest? what are the other things i should take care
> of before adding the pri keys?
> Debian
>
> *** Sent via Developersdex http://www.developersdex.com ***
Don't make changes to a live production database unless you have tested it
first and are comfortable about the changes you are going to make. You NEED
primary keys, but don't make the assumption that adding a new primary key
won't break one of your applications. If all you are doing is redefining an
existing unique index as a primary key then that shouldn't hurt anything,
but if you are adding a new field as a primary (default NEWID as per
Erland's suggestion [a very good suggestion by the way]) then that does have
the potential to break something - I think its only likely to break badly
written code, but the potential is there, so test it first.
Take a copy, add your primary keys to that and then run through the whole
range of applications and ensure that everything works as you expect. Only
then, once you are entirely satisfied, introduce the changes to the live
database and even then be sure that you can roll your changes back if it all
goes horribly wrong.
Brian.
www.cryer.co.uk/brian|||debian mojo (debian_mojo@.yahoo.com) writes:
> That's true that moving away from the problem is not a solution. But
> you see that the database i'm talking about is a 24 x 7 database, and
> i'm not pretty sure how apps that are using this db.
> If i do pri keys to the tables, is there any possibility that the apps
> may suffer is some way.
Sure. Apps that do SELECT * on the tables and then spit out all columns
somewhere, or assume that it has a certain width will be confused by an
extra column if you add one. If you add the new column anywhere but last,
apps that refers to columns by column number will croak. All this is
bad practice, but since this site already has proven a fondness for
bad practice...
As Brian says, you need to test any changes in a safe environment. Which
includes finding out how long time it takes to add the indexes and the new
columns, so you can determine the downtime.
I should have added that beside looking for unique indexes, also look
for existing IDENTITY columns and existing guid columns, as they can
be used for the task.
> So what do you suggest? what are the other things i should take care
> of before adding the pri keys?
Read Brian's article again. There was a lot of good advice there!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Replication problem with stored procedure
I am working on a transaction replication, i have no problem with all the tables, but when adding the stored procedures, i got the error message:
Insert Error: Column name or number of supplied values does not match table definition.
The first column - wsSTUAPP_WEXP_iID 's "identity incremnet" definition of table tbl_wsSTUAPP_WEXP is set to 1.
The system works well in the publisher server, and the table itself has no problem for replication, but when adding the stored procedure to the replication article, the problem happens.
Please advise how to slove this problem. Below is the part of the store procedure and the error message:
THANK YOU IN ADVANCE!
STOREd PROCEDURE:
ALTER PROCEDURE [dbo].[sp_wsSetSTUAPPWEXP]
@.wsSTUAPP_WEXP_iID int
, @.wsSTUAPP_iID int
, @.wsSTUAPP_WEXP_iOrder int
, @.wsSTUAPP_WEXP_sEmployerName varchar(75)
, @.wsSTUAPP_WEXP_sJobTitle varchar(75)
, @.wsSTUAPP_WEXP_sJobDESC varchar(360)
, @.wsSTUAPP_WEXP_dStart datetime
, @.wsSTUAPP_WEXP_dEnd datetime
AS
IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@.wsSTUAPP_iID
,@.wsSTUAPP_WEXP_iOrder
,@.wsSTUAPP_WEXP_sEmployerName
,@.wsSTUAPP_WEXP_sJobTitle
,@.wsSTUAPP_WEXP_sJobDESC
,@.wsSTUAPP_WEXP_dStart
,@.wsSTUAPP_WEXP_dEnd
)
END
ELSE
BEGIN
UPDATE tbl_wsSTUAPP_WEXP
SET wsSTUAPP_WEXP_iOrder= @.wsSTUAPP_WEXP_iOrder
,wsSTUAPP_WEXP_sEmployerName= @.wsSTUAPP_WEXP_sEmployerName
,wsSTUAPP_WEXP_sJobTitle= @.wsSTUAPP_WEXP_sJobTitle
,wsSTUAPP_WEXP_sJobDESC= @.wsSTUAPP_WEXP_sJobDESC
,wsSTUAPP_WEXP_dStart= @.wsSTUAPP_WEXP_dStart
,wsSTUAPP_WEXP_dEnd= @.wsSTUAPP_WEXP_dEnd
WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID
END
ERROR MESSAGE:
Command attempted:
CREATE PROCEDURE "dbo"."sp_wsSetSTUAPPWEXP"
@.wsSTUAPP_WEXP_iID int
, @.wsSTUAPP_iID int
, @.wsSTUAPP_WEXP_iOrder int
, @.wsSTUAPP_WEXP_sEmployerName varchar(75)
, @.wsSTUAPP_WEXP_sJobTitle varchar(75)
, @.wsSTUAPP_WEXP_sJobDESC varchar(360)
, @.wsSTUAPP_WEXP_dStart datetime
, @.wsSTUAPP_WEXP_dEnd datetime
AS
IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@.wsSTUAPP_iID
,@.wsSTUAPP_WE
(Transaction sequence number: 0x00006D8E00000160000B00000000, Command ID: 12)
Error messages:
Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213
Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213
Hi Jim,
You are likely to be hitting the same issue as discussed in the following forum thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=493307&SiteID=1
and this is basically the unintended consequence of the interaction among the following factors:
1) Replicated identity columns are marked "not for replication" by default at the publisher when you setup replication through SSMS in SQL2005
2) Any insert statements into a table with an identity column marked 'not for replication' executed from a replication agent (distribution agent for example) must supply an explicit value for the identity column
3) In SQL2005, the server verifies the validity of insert statements in stored procedures and triggers during the creation of these objects and raises error if it determines that an embedded insert statement does not supply all the required values.
In more concrete terms, 1) will basically lead to all identity columns being marked 'not for replication' at both the publisher and the subscriber and given that your replicated stored procedure is going to be created through a distribution agent connection, 2) and 3) will lead the subscriber server into thinking that the insert statement embedded in your stored procedure would need to supply an explicit value for the identity column marked 'not for replication' and so it raises an error when an explicit identity value is found missing. Now, the stored procedure that you are replicating is probably intended to be executed outside of replication even though it is created through replication so the check is technically invalid despite the good intentions behind it.
The only workaround that I can think of is to use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher and then re-initialize your subscriber using a new snaphsot. This is admittedly not a very good workaround but the good news is that this particular problem is fixed for snapshot processing in SP2. The bad news is that the same problem applies to our ddl replication logic (alteration of stored procedure e.g.) and we don't have a general solution in that part of replication yet. My suggestion would be to separate your schema objects (stored procedures) into a separate snapshot publication so changes to your stored procedures etc. can be refreshed through snapshot processing when SP2 comes out.
Hope that hope,
-Raymond
|||The failure may be caused by implict column list in INSERT statement. Do you replicate all the columns in the table or are there any identity column in the replicated table? You can try to explicitly specify the column list in your INSERT statement.
Hope it helps.
Peng
|||Thanks Raymond and Peng,
The problem has been sloved by the following
"use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher"
I really appreciate it!
Jim
|||Hello Raymond,
I failed to use 'sp_identitycolumnforreplication' to disable the 'not for replication' property on the identity columns. However, it was succeessful before. But it doesn't work any more.
I tried to restore the database, and re-install SQL server, no use.
How to fix 'sp_identitycolumnforreplication' ?
Here is my query:
declare @.int int
set @.int =object_id('tbl_wsSTUAPP_WEXP')
exec sys.sp_identitycolumnforreplication @.int,0
GO
Thank you!
Replication problem with stored procedure
I am working on a transaction replication, i have no problem with all the tables, but when adding the stored procedures, i got the error message:
Insert Error: Column name or number of supplied values does not match table definition.
The first column - wsSTUAPP_WEXP_iID 's "identity incremnet" definition of table tbl_wsSTUAPP_WEXP is set to 1.
The system works well in the publisher server, and the table itself has no problem for replication, but when adding the stored procedure to the replication article, the problem happens.
Please advise how to slove this problem. Below is the part of the store procedure and the error message:
THANK YOU IN ADVANCE!
STOREd PROCEDURE:
ALTER PROCEDURE [dbo].[sp_wsSetSTUAPPWEXP]
@.wsSTUAPP_WEXP_iID int
, @.wsSTUAPP_iID int
, @.wsSTUAPP_WEXP_iOrder int
, @.wsSTUAPP_WEXP_sEmployerName varchar(75)
, @.wsSTUAPP_WEXP_sJobTitle varchar(75)
, @.wsSTUAPP_WEXP_sJobDESC varchar(360)
, @.wsSTUAPP_WEXP_dStart datetime
, @.wsSTUAPP_WEXP_dEnd datetime
AS
IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@.wsSTUAPP_iID
,@.wsSTUAPP_WEXP_iOrder
,@.wsSTUAPP_WEXP_sEmployerName
,@.wsSTUAPP_WEXP_sJobTitle
,@.wsSTUAPP_WEXP_sJobDESC
,@.wsSTUAPP_WEXP_dStart
,@.wsSTUAPP_WEXP_dEnd
)
END
ELSE
BEGIN
UPDATE tbl_wsSTUAPP_WEXP
SET wsSTUAPP_WEXP_iOrder= @.wsSTUAPP_WEXP_iOrder
,wsSTUAPP_WEXP_sEmployerName= @.wsSTUAPP_WEXP_sEmployerName
,wsSTUAPP_WEXP_sJobTitle= @.wsSTUAPP_WEXP_sJobTitle
,wsSTUAPP_WEXP_sJobDESC= @.wsSTUAPP_WEXP_sJobDESC
,wsSTUAPP_WEXP_dStart= @.wsSTUAPP_WEXP_dStart
,wsSTUAPP_WEXP_dEnd= @.wsSTUAPP_WEXP_dEnd
WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID
END
ERROR MESSAGE:
Command attempted:
CREATE PROCEDURE "dbo"."sp_wsSetSTUAPPWEXP"
@.wsSTUAPP_WEXP_iID int
, @.wsSTUAPP_iID int
, @.wsSTUAPP_WEXP_iOrder int
, @.wsSTUAPP_WEXP_sEmployerName varchar(75)
, @.wsSTUAPP_WEXP_sJobTitle varchar(75)
, @.wsSTUAPP_WEXP_sJobDESC varchar(360)
, @.wsSTUAPP_WEXP_dStart datetime
, @.wsSTUAPP_WEXP_dEnd datetime
AS
IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@.wsSTUAPP_iID
,@.wsSTUAPP_WE
(Transaction sequence number: 0x00006D8E00000160000B00000000, Command ID: 12)
Error messages:
Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213
Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213
Hi Jim,
You are likely to be hitting the same issue as discussed in the following forum thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=493307&SiteID=1
and this is basically the unintended consequence of the interaction among the following factors:
1) Replicated identity columns are marked "not for replication" by default at the publisher when you setup replication through SSMS in SQL2005
2) Any insert statements into a table with an identity column marked 'not for replication' executed from a replication agent (distribution agent for example) must supply an explicit value for the identity column
3) In SQL2005, the server verifies the validity of insert statements in stored procedures and triggers during the creation of these objects and raises error if it determines that an embedded insert statement does not supply all the required values.
In more concrete terms, 1) will basically lead to all identity columns being marked 'not for replication' at both the publisher and the subscriber and given that your replicated stored procedure is going to be created through a distribution agent connection, 2) and 3) will lead the subscriber server into thinking that the insert statement embedded in your stored procedure would need to supply an explicit value for the identity column marked 'not for replication' and so it raises an error when an explicit identity value is found missing. Now, the stored procedure that you are replicating is probably intended to be executed outside of replication even though it is created through replication so the check is technically invalid despite the good intentions behind it.
The only workaround that I can think of is to use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher and then re-initialize your subscriber using a new snaphsot. This is admittedly not a very good workaround but the good news is that this particular problem is fixed for snapshot processing in SP2. The bad news is that the same problem applies to our ddl replication logic (alteration of stored procedure e.g.) and we don't have a general solution in that part of replication yet. My suggestion would be to separate your schema objects (stored procedures) into a separate snapshot publication so changes to your stored procedures etc. can be refreshed through snapshot processing when SP2 comes out.
Hope that hope,
-Raymond
|||The failure may be caused by implict column list in INSERT statement. Do you replicate all the columns in the table or are there any identity column in the replicated table? You can try to explicitly specify the column list in your INSERT statement.
Hope it helps.
Peng
|||Thanks Raymond and Peng,
The problem has been sloved by the following
"use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher"
I really appreciate it!
Jim
|||Hello Raymond,
I failed to use 'sp_identitycolumnforreplication' to disable the 'not for replication' property on the identity columns. However, it was succeessful before. But it doesn't work any more.
I tried to restore the database, and re-install SQL server, no use.
How to fix 'sp_identitycolumnforreplication' ?
Here is my query:
declare @.int int
set @.int =object_id('tbl_wsSTUAPP_WEXP')
exec sys.sp_identitycolumnforreplication @.int,0
GO
Thank you!
Tuesday, March 20, 2012
replication problem
hi guys,
Just a question, I set up transaction replication between 2 servers.
it's working fine with data, table structures are also being replicated to
destination (I do reinitialize subscription). However, indexes and foreign
key are not being replicated. what is going on?
I checked the replication monitor, I dont' see any errors.
any hint?
thanks
kevin
By default the replication of fk's and pk's is not done. You can select to
include DRI in the articles property dialog. Right click on your
publication, select publication properties, click on the article tab, and
click on the browse button to the right of each table. In the snapshot tab
at the bottom you will find this option.
HOWEVER!!!! Why do you want to include DRI on your subscriber. Your data is
flowing from your publisher to your subscriber and ideally your DML will be
occuring on your publisher not your subscriber. So your DRI will be
maintained on your publisher and there is no reason to include it on your
subscriber.
If you are doing DML on your subscriber as well and replication does work
with this, it is advisable to put the NFR attribute on your constraints on
your subscriber tables.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23Zprj%23L1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> hi guys,
> Just a question, I set up transaction replication between 2
> servers. it's working fine with data, table structures are also being
> replicated to destination (I do reinitialize subscription). However,
> indexes and foreign key are not being replicated. what is going on?
> I checked the replication monitor, I dont' see any errors.
> any hint?
> thanks
> kevin
>
|||okay.. you're right.
I shouldn't worry about foreign key, but I do worry about indexes.
also, I added an article to subscription using sp_addarticle, but when I
force to run snapshot agent, snapshot agent said
no snapshot is available. why doesn't snapshot agent know I just added an
article?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Om$eJGM1FHA.3256@.TK2MSFTNGP09.phx.gbl...
> By default the replication of fk's and pk's is not done. You can select to
> include DRI in the articles property dialog. Right click on your
> publication, select publication properties, click on the article tab, and
> click on the browse button to the right of each table. In the snapshot tab
> at the bottom you will find this option.
> HOWEVER!!!! Why do you want to include DRI on your subscriber. Your data
> is flowing from your publisher to your subscriber and ideally your DML
> will be occuring on your publisher not your subscriber. So your DRI will
> be maintained on your publisher and there is no reason to include it on
> your subscriber.
> If you are doing DML on your subscriber as well and replication does work
> with this, it is advisable to put the NFR attribute on your constraints on
> your subscriber tables.
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23Zprj%23L1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>
|||causer you have to issue a sp_refreshsubscriptions. You might have to
re-rerun the snapshot agent as well.
Keep in mind that on the subscriber the PK's are by default replaced by
unique indexes which are the functional equivalent of a pk, only it will
allow a single null - but the unique key is a clustered index.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23oVx3WM1FHA.916@.TK2MSFTNGP10.phx.gbl...
> okay.. you're right.
> I shouldn't worry about foreign key, but I do worry about indexes.
> also, I added an article to subscription using sp_addarticle, but when I
> force to run snapshot agent, snapshot agent said
> no snapshot is available. why doesn't snapshot agent know I just added an
> article?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Om$eJGM1FHA.3256@.TK2MSFTNGP09.phx.gbl...
>
|||sorry, Last question.
it looks like after I did sp_refreshsubscriptions, the destination table
(subscriber) was truncated first, now it's buck copying data to destination
table at subscriber.
But originally, I had 5200000 rows in destination table(subscriber), In
same table in Publisher, It has 8000000 rows. I thought it would just copy
over 2800000 rows over instead of doing everything all over again. is
there a system procedure that can do such ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ubhUEiM1FHA.3756@.tk2msftngp13.phx.gbl...
> causer you have to issue a sp_refreshsubscriptions. You might have to
> re-rerun the snapshot agent as well.
> Keep in mind that on the subscriber the PK's are by default replaced by
> unique indexes which are the functional equivalent of a pk, only it will
> allow a single null - but the unique key is a clustered index.
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23oVx3WM1FHA.916@.TK2MSFTNGP10.phx.gbl...
>
|||No there is no proc to do this. In SQL 2005 snapshots are restartable which
is probably what you are looking for.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:u8IS%23IO1FHA.2132@.TK2MSFTNGP15.phx.gbl...
> sorry, Last question.
> it looks like after I did sp_refreshsubscriptions, the destination table
> (subscriber) was truncated first, now it's buck copying data to
> destination table at subscriber.
> But originally, I had 5200000 rows in destination table(subscriber), In
> same table in Publisher, It has 8000000 rows. I thought it would just
> copy over 2800000 rows over instead of doing everything all over again.
> is there a system procedure that can do such ?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ubhUEiM1FHA.3756@.tk2msftngp13.phx.gbl...
>
Just a question, I set up transaction replication between 2 servers.
it's working fine with data, table structures are also being replicated to
destination (I do reinitialize subscription). However, indexes and foreign
key are not being replicated. what is going on?
I checked the replication monitor, I dont' see any errors.
any hint?
thanks
kevin
By default the replication of fk's and pk's is not done. You can select to
include DRI in the articles property dialog. Right click on your
publication, select publication properties, click on the article tab, and
click on the browse button to the right of each table. In the snapshot tab
at the bottom you will find this option.
HOWEVER!!!! Why do you want to include DRI on your subscriber. Your data is
flowing from your publisher to your subscriber and ideally your DML will be
occuring on your publisher not your subscriber. So your DRI will be
maintained on your publisher and there is no reason to include it on your
subscriber.
If you are doing DML on your subscriber as well and replication does work
with this, it is advisable to put the NFR attribute on your constraints on
your subscriber tables.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23Zprj%23L1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> hi guys,
> Just a question, I set up transaction replication between 2
> servers. it's working fine with data, table structures are also being
> replicated to destination (I do reinitialize subscription). However,
> indexes and foreign key are not being replicated. what is going on?
> I checked the replication monitor, I dont' see any errors.
> any hint?
> thanks
> kevin
>
|||okay.. you're right.
I shouldn't worry about foreign key, but I do worry about indexes.
also, I added an article to subscription using sp_addarticle, but when I
force to run snapshot agent, snapshot agent said
no snapshot is available. why doesn't snapshot agent know I just added an
article?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Om$eJGM1FHA.3256@.TK2MSFTNGP09.phx.gbl...
> By default the replication of fk's and pk's is not done. You can select to
> include DRI in the articles property dialog. Right click on your
> publication, select publication properties, click on the article tab, and
> click on the browse button to the right of each table. In the snapshot tab
> at the bottom you will find this option.
> HOWEVER!!!! Why do you want to include DRI on your subscriber. Your data
> is flowing from your publisher to your subscriber and ideally your DML
> will be occuring on your publisher not your subscriber. So your DRI will
> be maintained on your publisher and there is no reason to include it on
> your subscriber.
> If you are doing DML on your subscriber as well and replication does work
> with this, it is advisable to put the NFR attribute on your constraints on
> your subscriber tables.
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23Zprj%23L1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>
|||causer you have to issue a sp_refreshsubscriptions. You might have to
re-rerun the snapshot agent as well.
Keep in mind that on the subscriber the PK's are by default replaced by
unique indexes which are the functional equivalent of a pk, only it will
allow a single null - but the unique key is a clustered index.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23oVx3WM1FHA.916@.TK2MSFTNGP10.phx.gbl...
> okay.. you're right.
> I shouldn't worry about foreign key, but I do worry about indexes.
> also, I added an article to subscription using sp_addarticle, but when I
> force to run snapshot agent, snapshot agent said
> no snapshot is available. why doesn't snapshot agent know I just added an
> article?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Om$eJGM1FHA.3256@.TK2MSFTNGP09.phx.gbl...
>
|||sorry, Last question.
it looks like after I did sp_refreshsubscriptions, the destination table
(subscriber) was truncated first, now it's buck copying data to destination
table at subscriber.
But originally, I had 5200000 rows in destination table(subscriber), In
same table in Publisher, It has 8000000 rows. I thought it would just copy
over 2800000 rows over instead of doing everything all over again. is
there a system procedure that can do such ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ubhUEiM1FHA.3756@.tk2msftngp13.phx.gbl...
> causer you have to issue a sp_refreshsubscriptions. You might have to
> re-rerun the snapshot agent as well.
> Keep in mind that on the subscriber the PK's are by default replaced by
> unique indexes which are the functional equivalent of a pk, only it will
> allow a single null - but the unique key is a clustered index.
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23oVx3WM1FHA.916@.TK2MSFTNGP10.phx.gbl...
>
|||No there is no proc to do this. In SQL 2005 snapshots are restartable which
is probably what you are looking for.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:u8IS%23IO1FHA.2132@.TK2MSFTNGP15.phx.gbl...
> sorry, Last question.
> it looks like after I did sp_refreshsubscriptions, the destination table
> (subscriber) was truncated first, now it's buck copying data to
> destination table at subscriber.
> But originally, I had 5200000 rows in destination table(subscriber), In
> same table in Publisher, It has 8000000 rows. I thought it would just
> copy over 2800000 rows over instead of doing everything all over again.
> is there a system procedure that can do such ?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ubhUEiM1FHA.3756@.tk2msftngp13.phx.gbl...
>
Replication problem
I'm getting this error with a transactional replication
publication I'm working with, I think my distribution
database is hosed, I got another error cannot find object
syspublications, I'm just kind of wondering how it got
that way and how I can fix it...
TIA
SQL Server Enterprise Manager encountered errors creating
push subscriptions for the following Subscribers:
MNCOROLAPTST-V1: Error 2601: Cannot insert duplicate key
row in object 'MSsubscriptions' with unique
index 'ucMSsubscriptions'.
Could not update the distribution database subscription
table. The subscription status could not be changed.
The subscription could not be created.
The statement has been terminated.
BTW, has anyone noticed that the msnews.microsoft.com
server was down to day, or is that just me...?
Its just you
Your problem though sounds very unusual. How are you creating your
subscriptions? Via a script?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:112301c4a1b3$ae62e070$a301280a@.phx.gbl...
> I'm getting this error with a transactional replication
> publication I'm working with, I think my distribution
> database is hosed, I got another error cannot find object
> syspublications, I'm just kind of wondering how it got
> that way and how I can fix it...
> TIA
>
> SQL Server Enterprise Manager encountered errors creating
> push subscriptions for the following Subscribers:
> MNCOROLAPTST-V1: Error 2601: Cannot insert duplicate key
> row in object 'MSsubscriptions' with unique
> index 'ucMSsubscriptions'.
> Could not update the distribution database subscription
> table. The subscription status could not be changed.
> The subscription could not be created.
> The statement has been terminated.
>
> BTW, has anyone noticed that the msnews.microsoft.com
> server was down to day, or is that just me...?
|||Hello Hilary,
We typically use Enterprise Manager Replication >
Publications > Publication [Context Menu] Push New
subscription.. This was working without any problems
before our tech ops guys restored a database that happens
to contain the articles for the publication on the
server...
Can I blow away the distribution database and start
over...? Right now I cannot delete any pubs create any
subscriptions or anything else with replication it's
totally useless on this box..
Thanks, I appreciate your help!!
Dan
>--Original Message--
>Its just you
>Your problem though sounds very unusual. How are you
creating your
>subscriptions? Via a script?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:112301c4a1b3$ae62e070$a301280a@.phx.gbl...
object[vbcol=seagreen]
creating[vbcol=seagreen]
key
>
>.
>
|||Now it all makes sense. An easy way around this is to recreate your
publications with a different name.
However, you will probably be best to disable replication (tools,
replication, disable publishing) and then by re-enabling your server for
replication.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:034201c4a1bc$28d8b1b0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hello Hilary,
> We typically use Enterprise Manager Replication >
> Publications > Publication [Context Menu] Push New
> subscription.. This was working without any problems
> before our tech ops guys restored a database that happens
> to contain the articles for the publication on the
> server...
> Can I blow away the distribution database and start
> over...? Right now I cannot delete any pubs create any
> subscriptions or anything else with replication it's
> totally useless on this box..
> Thanks, I appreciate your help!!
> Dan
>
> creating your
> message
> object
> creating
> key
|||That's exactly what I was looking for...
Thanks!!!
Dan
>--Original Message--
>Now it all makes sense. An easy way around this is to
recreate your
>publications with a different name.
>However, you will probably be best to disable replication
(tools,
>replication, disable publishing) and then by re-enabling
your server for
>replication.
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:034201c4a1bc$28d8b1b0$a501280a@.phx.gbl...
happens[vbcol=seagreen]
replication[vbcol=seagreen]
subscription
>
>.
>
publication I'm working with, I think my distribution
database is hosed, I got another error cannot find object
syspublications, I'm just kind of wondering how it got
that way and how I can fix it...
TIA
SQL Server Enterprise Manager encountered errors creating
push subscriptions for the following Subscribers:
MNCOROLAPTST-V1: Error 2601: Cannot insert duplicate key
row in object 'MSsubscriptions' with unique
index 'ucMSsubscriptions'.
Could not update the distribution database subscription
table. The subscription status could not be changed.
The subscription could not be created.
The statement has been terminated.
BTW, has anyone noticed that the msnews.microsoft.com
server was down to day, or is that just me...?
Its just you
Your problem though sounds very unusual. How are you creating your
subscriptions? Via a script?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:112301c4a1b3$ae62e070$a301280a@.phx.gbl...
> I'm getting this error with a transactional replication
> publication I'm working with, I think my distribution
> database is hosed, I got another error cannot find object
> syspublications, I'm just kind of wondering how it got
> that way and how I can fix it...
> TIA
>
> SQL Server Enterprise Manager encountered errors creating
> push subscriptions for the following Subscribers:
> MNCOROLAPTST-V1: Error 2601: Cannot insert duplicate key
> row in object 'MSsubscriptions' with unique
> index 'ucMSsubscriptions'.
> Could not update the distribution database subscription
> table. The subscription status could not be changed.
> The subscription could not be created.
> The statement has been terminated.
>
> BTW, has anyone noticed that the msnews.microsoft.com
> server was down to day, or is that just me...?
|||Hello Hilary,
We typically use Enterprise Manager Replication >
Publications > Publication [Context Menu] Push New
subscription.. This was working without any problems
before our tech ops guys restored a database that happens
to contain the articles for the publication on the
server...
Can I blow away the distribution database and start
over...? Right now I cannot delete any pubs create any
subscriptions or anything else with replication it's
totally useless on this box..
Thanks, I appreciate your help!!
Dan
>--Original Message--
>Its just you
>Your problem though sounds very unusual. How are you
creating your
>subscriptions? Via a script?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:112301c4a1b3$ae62e070$a301280a@.phx.gbl...
object[vbcol=seagreen]
creating[vbcol=seagreen]
key
>
>.
>
|||Now it all makes sense. An easy way around this is to recreate your
publications with a different name.
However, you will probably be best to disable replication (tools,
replication, disable publishing) and then by re-enabling your server for
replication.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:034201c4a1bc$28d8b1b0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hello Hilary,
> We typically use Enterprise Manager Replication >
> Publications > Publication [Context Menu] Push New
> subscription.. This was working without any problems
> before our tech ops guys restored a database that happens
> to contain the articles for the publication on the
> server...
> Can I blow away the distribution database and start
> over...? Right now I cannot delete any pubs create any
> subscriptions or anything else with replication it's
> totally useless on this box..
> Thanks, I appreciate your help!!
> Dan
>
> creating your
> message
> object
> creating
> key
|||That's exactly what I was looking for...
Thanks!!!
Dan
>--Original Message--
>Now it all makes sense. An easy way around this is to
recreate your
>publications with a different name.
>However, you will probably be best to disable replication
(tools,
>replication, disable publishing) and then by re-enabling
your server for
>replication.
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:034201c4a1bc$28d8b1b0$a501280a@.phx.gbl...
happens[vbcol=seagreen]
replication[vbcol=seagreen]
subscription
>
>.
>
Monday, March 12, 2012
Replication only working 1 way
Hi Guys
I am running SQL2000 I have set up Transactional Replication with a push
subscription.
My 2 servers are not in the same domain but can see eachother.
Server 1 is Distributor as well as publisher. All insert, delete & updates
made on this side replicates fine to server 2, however any inserts, delete or
update made on server 2 does NOT replicate back to server 1.
I do not get any errors everything seems fine except for the lack of
replication.
Any ideas?
Thanks.
Regards
Jonas
Jonas,
Transactional replication is one way by default (Publisher -> Subscriber).
To get it bidirectional, you need to set up immediate updating subscriptions
or queued updating subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I am running SQL2000 I have set up Transactional Replication with a push
subscription.
My 2 servers are not in the same domain but can see eachother.
Server 1 is Distributor as well as publisher. All insert, delete & updates
made on this side replicates fine to server 2, however any inserts, delete or
update made on server 2 does NOT replicate back to server 1.
I do not get any errors everything seems fine except for the lack of
replication.
Any ideas?
Thanks.
Regards
Jonas
Jonas,
Transactional replication is one way by default (Publisher -> Subscriber).
To get it bidirectional, you need to set up immediate updating subscriptions
or queued updating subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Friday, March 9, 2012
Replication Not working for a table
We have about 20 tables setup for replications . All std.
setup . Replication is using stored procs created by SQL
Server instead of the Statements. All tables are fine
except one table stpped changing data at all. No errors
etc.
What should I do.
Table has 2 coulms as PK . using SQL stored proc,
sp_msIns..,sp_msUpd.., sp_msdel..
What could be wrong. PLease help. All other tables are
fine.
No Primary key or columns have been changed at all .
ThanksYour table name contains white spaces or special characters? If so,
rename your stored procedures.
MoreThanInsane
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1340150.html
setup . Replication is using stored procs created by SQL
Server instead of the Statements. All tables are fine
except one table stpped changing data at all. No errors
etc.
What should I do.
Table has 2 coulms as PK . using SQL stored proc,
sp_msIns..,sp_msUpd.., sp_msdel..
What could be wrong. PLease help. All other tables are
fine.
No Primary key or columns have been changed at all .
ThanksYour table name contains white spaces or special characters? If so,
rename your stored procedures.
MoreThanInsane
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1340150.html
Subscribe to:
Posts (Atom)