What's The Relationship Among Replication, Publication And Subscription
Oct 31, 2006What's the relationship among replication, publication and subscription?
I want to create replication database, what things should I know first?
ADVERTISEMENT
Is DB Publication/subscription?
Jul 26, 2004Because of problems trying to alter databases used
for replication my software would need to find out
if a database is a publisher or was repliated (using
T-SQL). Is this possible?
The Subscription To Publication Is Invalid
Dec 25, 2005Hi,
I have two SQL 2000 Server running on Windows 2003 Standard Edtion. I am using merge option to replicate database as Transactional option is excluding some tables. Both server are not in Active Directory and I am trying to replicate over the internet. I got following error message while using Merge option:
Error Message:
The subscription to publication 'XXX' is invalid.
Error details:
The subscription to publication 'XXX' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
---------------------------------------------------------------------------------------------------------------
The remote server is not defined as a subscription server.
(Source: x.x.x.x (Data source); Error number: 14010)
---------------------------------------------------------------------------------------------------------------
Any help regarding the same will be appricated. Thanks.
How To Log The Transactions Of Publication And Subscription Databases
Oct 24, 2007I am using sql 2005 replication in my application. But how can i log the transactions that are happening in the server and client databases please help me...
Pull Subscription: Publication Not Visible In Assistant
Feb 4, 2002I've a merge rep running with 4 subscribers for a while. Now I
wanted to add another subscriber and when I open the publisher
in the pull subscription assistant dialog, there are no publications
in it. (even not if i switch to "allow anonymous sub") But every-
thing seems to be alright wirh the publication itself! It even doesn't
work to select the publication in a pull subs. from an other db on the
same server! Any ideas?
Any help will be greatly appreciated!!!
Synchronizing Expired Subscription And Publication Databases
Oct 28, 2005I had a merge subscription expire and now need to synchronize two disparate databases. How do I do so without overwriting the subscriber database with the publication snapshot? Thanks for any help you can give.
View 3 Replies View RelatedThe Subscription To Publication 'xxxx' Has Expired Or Does Not Exist
Dec 7, 2006Hi,
Is there any way to find out if "subscription to publication has expired" in advance using RMO?
Last Time Publication/subscription Replicated Data
Jun 21, 2007Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)??
I want to set up monitoring to make sure I am aware if something for some reason does not replicate
OH and it is on SQL 2005 enterprise... an
The Publisher Failed To Allocate A New Set Of Identity Ranges For The Subscription After Another Publication Deletion
Jan 17, 2007Hi.
First of all, I apologize for my english
I have two publications. Some of the data are the same on the two publications. Both are configured as follow : The identity range management is set to "automatic" and the tracking-level is set to "Column-level tracking". Until there, every things works fine.
But, if i'm deleting one of the publication and if i'm deleting one of the rows that were replicated on the two publications i'm getting the following SQL Exception : "Invalid object name 'dbo.MSmerge_repl_view_1CAD32C4FF904A3CA27518B0C4BFF716_70308DE2261C4EC784C56131902E7D1C'"
If i'm watching the status of the leftover replication through the replication monitor, i get this error message :
"Error messages:
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. (Source: MSSQLServer, Error number: 20663)
Get help: http://help/20663"
I checked the given links but they're useless.
So I tried to reinitialize the subscription with the "use a new snapshot" option enabled without any success either. I did only obtain a new error message :
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
Failed to pr"
I didnt have any idea to correct this issue, so I would appreciate any help.
Thanks.
David.
SQL Express And Replication Publication?
Aug 9, 2006Hi Mate !
I was searching to enable replication publication on my SQL 2005 Express, but as far as I look, this feature don't seem to be included.
Am I, correct?
Is there a way to enable this fearture?
Thanks
Zak
Transaction Replication Multiple Publication
May 14, 2008:mad:
I have one Publisher! (SQL 2005)
Two Subscribers. (SQL 2005)
Each subscriber requires different row filter from the published tables.
So, i need to create Two publications for each subscriber for the same db.
When i setup first publication, and apply filters to published tables.
All goes fine.
As soon i create an other publication with same published tables but DIFFERENT row filter; the log reader agent fails!
The process could not execute 'sp_replcmds' on 'DB1'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Execution of filter stored procedure 2076702271 failed. See the SQL Server errorlog for more information. (Source: MSSQLServer, Error number: 18764)
Get help: http://help/18764
The Log Reader Agent failed to construct a replicated command from log sequence number (LSN) {0008443a:000003b5:003e}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
:confused:
I have tried to re-initialze the subscriptions and re-run snapshots but no luck.
As i remove the row filter from the second publication; log reader starts working fine.
Previously i was using Merge replication and all was going BUT, on very large DML on publisher, we use to receive deadlock errors on application.
I decided to use Tran repl instead because log reader reads the changes from tran-log instead of locking the published tables; NOW I AM STUCK!
Ideas please!!!!
Delete A Merge Replication Publication
Mar 15, 2007I tried to drop a merge replication publication and it is giving me an invalid object name dbo.ms_mergeagents message. I tried to drop using:
SSMS
sp_dropmergepublication
sp_replicationdboption
I got the last two from Microsoft KB article 324401. The subscription was never created because it gave another error invalid object_name msmerge_subscriptions. I can create new publications but not drop the existing ones.
Please assist...
Replication - Can Not Create Publication Database
Jul 23, 2005Hi,I am trying to use Enterprise Manager Wizard to create a newPublication database but I got the following message.Error 2812: Could not find stored procedureMyDB.dbo.sp_MSadd_logreader_agentThere seems that some replication stored procedures.Any help?Thanks in Advance
View 1 Replies View RelatedCopy Replication Publication Definition
Jun 12, 2006Is there an easy way to copy a replication publication definition from one server to another? In our environment we have a development environment, testing environment and soon a production environment. Right now keeping the replication definition between the development and testing environments is a manual process, are there tools that can do a diff on this or a way to keep this in sync?
For example when we change what information gets replicated in the development environment I need a way to propogate that change to the testing environment.
I know I can generate scripts from within SQL Server Management Studio, but it creates scripts very machine specific. So short of going through that and modifying the server names to match the different environments, is there a better way?
Allowing Transformations When Creating Publication For Replication
Dec 2, 2003I am at my wits end here. For Replication the Books Online clearly state:
"The option to allow transformations is set at the time you create a publication"
However, I cannot find any options that allow me to do this in the Create Publication Wizard.
Once the Publication has been created I see in the Properties in the Subscription Options tab that "Use DTS to transform data before distributing it to a Subscriber" is set to No and there is no way to change it.
Where am I going wrong?
Merge Replication Failing After Recreating The Publication
Jun 7, 2006I am having a problem with merge replication after recreating a publication. It is a simple two-way replication between two servers allowing applications to update data at both ends, i.e. 1 publisher, 1 subscriber for all tables except some junk ones. The problem started after I did the following:
1) I dropped and recreated the publication to allow for some changes to the database schema. These caused problems so I dropped the subscription and publication and restored the databases at each end from backups taken before we started.
2) When I created the publication again, it wouldn't let me use the same name as it thought the publication already existed. It let me use a different name instead.
3) Most things work OK but one of the tables is not replicating inserts from the subscriber to the publisher. There are no errors and no conflicts, other similar tables replicate OK. Inserts go across the other way OK.
4) I am getting replication conflicts on another table that says 'Unable to synchronize the row because the row was updated by a different process outside of replication'
I believe the problem is to do with the original publication details still being in the restored databases, so am looking to drop the publication & subscription, remove the remnants of the old publication, fix the data and recreate pub & sub. What do I need to do to get rid of the old replication data in the database?
Any help much appreciated.
Problem Creating New Publication For MERGE REPLICATION
Jan 3, 2008Hi,
I am stuck when trying to create a new publication. I fill all the wizard tabs but when finishing I get that message (6 times, one for every table):
Code Block
SQL Server Management Studio could not create article 'applicationModules'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
This article cannot use the 'subscriber_upload_options' feature because the publication compatibility level is less than 90. Use sp_changemergepublication to set the publication_compatibility_level of publication 'testMR' to '90RTM'.
Changed database context to 'test'.
Publication 'testMR' has the following property: 'compensate_for_errors'. SQL Server subscribers below version '8.00.0858' will ignore this setting. (Microsoft SQL Server, Error: 21522)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21522&LinkId=20476
The error seemed pretty clear, so I end up doing:
EXEC sp_changemergepublication
@publication = N'testMR',
@property = N'publication_compatibility_level',
@value = N'90RTM'
and running the wizard again.
But I got the same error.
The link on the message take me nowhere.
and by googling for error 21522 I couldn't find any help at all.
Please, could anyone help?
Cheers
SQL 2012 :: Configure Replication Which Will Gather Logs From Publication Once In A Day?
Jan 21, 2015I wanted to schedule the transaction replication. How do I do it? Currently I have set up a transaction replication which runs continuously and synchronizes the changes with immediate effect.
I need to configure a replication which will gather logs from the publication once in a day.
Is there any possibility?
Setting Up Publication For Merge Replication Using Stored Procedures
Aug 2, 2006Hello.I am trying to use Transact SQL to prepare publication for replication (SQL Server 2000 SP4 and SQL Mobile). I can easily create publication, add filters etc. but there is one thing which I can't find : parameter which tells me subscriber type and allow me to change subscriber type for publication. I don't know where I can change (or even if I can change) this property.
If someone made publication for mobile devices and replication with filters,resolvers etc. will it be a problem to share experiences here? Maybe some sample code with script or something what will guide me. Thanks in advance. Maciej Wysocki
Snapshot Agent Fails For Merge Replication Publication
Nov 9, 2006I'm trying to setup merge replication and get the following error from the snapshot agent after I've finished the 'New Publication Wizard'. This doesn't make sense to me as it is an automatic process. Also, it looks like it is complaining about a 'dbo.MSmerge_cont...' view which is a view created automatically for the merge publication.
ERROR MESSAGE:
The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128.
FULL DETAILS:
Message: The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128.
Command Text: IF OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT AUTHORITYSYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90 as select * from MSmerge_contents where 1 = 2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP VIEW [<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT AUTHORITYSYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90 as select * from MSmerge_contents where 1 = 2</CommandText></TSQLCommand></EVENT_INSTANCE>]
Parameters:
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteAdHocQueryDiscardResults(String strQuery, SqlConnection connection)
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.GenerateSystemTableBcpFiles()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPostArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 103)
Get help: http://help/103
Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128. (Source: MSSQLServer, Error number: 103)
Get help: http://help/103
SQL 2012 :: Adding Articles To Existing Publication In Transactional Replication
Sep 26, 2014I have an existing publication in sql 2012 with 2 articles, and then I add 2 more articles. After that when I generate a snapshot, will the snapshot be generated for 2 new articles only or for all 4 articles?
I remember adding 1 new articles to one existing publication with 150 articles and when I generated snapshot, it was generated only for 1 article. But I don't remember clearly.
Does it behave differently for small and large number of articles?
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
SQL Server 2008 :: Temporarily Stop Replication / What To Do When Publication Is Dropped
Aug 12, 2015I have transactional replication configured where the publisher and subscriber are on two different servers. Yesterday a database upgrade was carried out, and the DBA dropped replication by issuing sp_removedbreplication on the published database. The subscription is still set up.
I have two questions:
1 - What is the safest way to temporarily switch off replication without losing the publication or subscription? As far as I was aware (my replication knowledge isn't great), simply disabling the relevant agents would do the job.
2 - I now have the task of creating the publication again (fortunately we have a saved script). If I recreate this publication, will I be able to point the existing subscription at it?
URGENT::Oracle To SQL 2005 Replication ( Drop Coulmn From Publication)
Jul 9, 2007
I'm in a middle of a project where replication established between Oracle 8i and SQL server 2005. Now we need to drop one column from on of the tables replicated from Oracle side. My question : Is there anyway I can do this in Oracle and automatically reflected on SQL server? If not what is the proper steps to do it. Realy appreciate your fast response.
Regards
What Happens If I Remove The 'Enforce Relationship For Replication' Setting?
Sep 28, 2005Hi I have a problem. I have a replicated database. Now I have a couple of tables within the database where I delete the content row by row and caluted the new record and insert them. Now when I do this on the development system that is unreplicate it is fine. However on production that is replicated I get this error:
System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.Data.SqlClient.SqlException: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VARLISTS_USERVARLISTS'. The conflict occurred in database 'BHP', table 'USERVARLISTS', column 'AutoID'.
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VARLISTS_USERVARLISTS'. The conflict occurred in database 'BHP', table 'USERVARLISTS', column 'AutoID'.
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VARLISTS_USERVARLISTS'. The conflict occurred in database 'BHP', table 'USERVARLISTS', column 'AutoID'.
Now I persume this is caused by the foreign key being contrained by the replicated database. I had though about unticking the box 'Enforce Relationship for replication' but I am not sure what problems this may cause.
Woudl this fix my problem? If not do you have any idea what would.
Thanks Ed
Disappearing Parameterised Filters And Filter Joins In Publication Properties Page Of Replication Monitor
Jan 22, 2007Hi,
We have an issue with our replication configuration when viewed through replication monitor. Parameterised Filters and joined filters don't appear in the gui. However, when we script the publication all the filters are present.
This issue only seems to occur when we have a remote distributor.
I should also point out that we have a merge push topology that uses a custom RMO synchronisation component on a separate server to either the publisher or the distributor. Also all the databases in the topology are called the same name. This has caused us other issues relating to this topology in particular so I raise it here as well although I don't expect it to be the case in this instance.
Any help would be greatly appreciated in clarifying this matter.
Replication - Subscription
May 23, 2001Hi,
I am doing a repl.in SQL7/NT.
At time of 'Push Subscription wizard' - I used the option "Yes,Initialize the schema and data......" whereas I already had data and schema at the subscriber.
Now my question is what will happen now and how long will this process take(for approx5GB of DAta).
OR should I stop the process - it's already an hour when I started the process.
Any suggestion appreciated.
Replication - Subscription Inactive, Why ?
Jul 23, 2003I have a snapshot & a transcation publication on Server A, and a pull subscription for each on Server B.
The distribution agent on ServerA has the error:
"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."
Any idea what caused this ..... Everything was fine for a week or so.
Does anyone know of any good books or publications on Replication ? I've got some DBA books that cover the basics, but I'd like something that helps explain the oddball situations.
SQL 2K
Replication (pulling A Subscription)
May 19, 2004I am trying to pull subscription for a database but I always get the error message ...
Error 15004: Name cannot be null
What exactly have I done wrong
Plaese help
Thanks
Nneka Echebe
Merge Replication - FTP - PULL Subscription
Dec 18, 2002Trying to set up Merge Replication using FTP with PULL subscriptions on sql 2000
Are there any issues that anyone is aware of that may affect this working.
Replication - Merge Vs Updateable Subscription
Feb 14, 2003Does anyone have advice as choosing between merge replication and updatable subscriptions? I've read the documentaion, they seem to offer the same functionality, and I don't see any clear guidelines as to why to pick one over the other...any suggestions would be appreciated!
Ed
Pull Subscription For Transactional Replication
Jan 5, 2005I have setup a pull subscription on the subscriber for transactional replication. The distribution job continues to run, but doesn't do anything because it says: The initial snapshot for publication 'man4' is not yet available.
How can I tell if the snapshot is running, and can I see the snapshot where it was created?
Replication Question - Updatable Subscription?
Apr 11, 2006Hi all,
I am planning replication around a large SQL Database Server; 3 databases need to be replicated for reporting purposes.
My plan was to use Transaction Replication, as the Reporting database will be mostly read-only. However, we must add a whole bunch of additional, historical data to some of the tables of the reporting database that does not exist in the Publisher. I understand that changes can be made to subscriptions in Transaction Replication scenarios, ideally when the changes are few and far between. (Merge replication is excessive for our purposes.)
I don't want the additional historical data to be propagated back to the publisher. Is it possible that data is written to a subscriber in a Transactional Replication scenario without writing these updates to the Publisher? Given the nature of the data, there will not be conflicts between the historical data and the new changes applied from the publisher.
Any help would be much appreciated!
Thanks,
Faraz
Merge Replication - Uninitialized Subscription
May 7, 2007I have created a Merge Subscription between two databases. I have set the security setting in the UNC folder of the publisher to allow 'Full Control' for 'Everyone'.
I have done various searches, google, here, etc. But cant seem to find much information to help me out.
When I create the subscription it says it was a success, but when I launch the replication monitor and click the publication it says its subscribers are uninitialized.
Can anyone help?
Cheers