Hi ...i am new to sql server replication, im using the sql server 2005.
I was configure the replication n publish a database , i wanna add a new subscription, but i am facing the follow error
You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
How do i add another subscription database,since in my sql server has only 1 subscriber?
I have setup a merge replication publication (SQL server 2005 wth SQL Mobile) with dynamic filters based on host name and when i sync to the pda all data appears fine. When I make changes on the PDA and synchronise, the changes are pushed back to the server fine.
However when I change data on the server and synchronise at the PDA, no changes ever appear! Surely I must have missed something obvious or done something really stupid.
If i check the replication monitor it just says no changes so any suggestions would be great.
I have a unusual problem with a snapshot publication that I am having a hard time troubleshooting. The servers in question are all SQL 05 Enterprise. Here is the scenario:
Staging Server - This server acts as the publisher of the snapshot Production Server - This server is the subscriber of the staging snapshot Cascade Servers - These servers receive transactional updates from the production server
The staging server applies the snapshot to production successfully. From there the production server has a transactional publication which the subscribers are the cascade servers.
This model worked for a long time where we would generate the snapshot apply it to production and the updates would be sent to the cascade servers. All of a sudden there is a problem with the transactional publication from production -> cascade. There are 7-10 tables that do not receive the updates and all data in the problematic tables is truncated. The other remaining tables in the subscription receive the updates as needed. Looking at replication monitor and all other logs shows that the transactions are being replicated, but yet the 7-10 problematic tables do not contain any data. The only solution I have found for this is to recreate the entire replication model from scratch.
Any advice on how to handle this scenario? Is my model for using a transactional publication to cascade changes to a subset of servers incorrect?
I have added a new table to a database (existing publication) using T-SQL, I then opened up publisher properties, and ticked the new table/article so that it would be added to the subscriber. It did not show up.
I did not use a snapshot to initialize the subscription.
Immediate Synch is 0. allow_anonymous is 0.
I mark the subscription to be reinitialized. When I start the snapshot agent I get '0% A snapshot was not generated because no subscriptions needed initialization'.
What could I be doing wrong, or missed out? Do I need to drop and recreate the subscription to get the article to show up?
Hi, We have an existing merged replication schema that works well. One of the tables is named audit. Currently this is a bidirectional transfer. However, we want a new audit table that will transfer data from the subscriber to the publisher and not the other way round. I have a script that was generated as a backup script when replication was first created. I have no idea which parameter to change in order to have the merge replication going from subscriber to publisher. Any advise is helpful. Thanks.
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
I want to create one more publication on a database (SQL Server 2005) that is already a publisher and has subscribers. The subscribers of the second publication will not be inserting new records so I will mark the articles (tables) as download-only.
Is there anything extra that I should take into consideration? Are there any side effects of this process?
Hi,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
When I tried to restore backup of publication database, time taken for restoration is too long [ 4 hours]. The actual database restore takes only 20 minutes but the stored procedures used for cleanup replication takes the remaining time. Is the issue related to my environment or the cleanup replication stored procedure?
Hi, everyone, I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.
Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.
I have a Subscriber database updated using transactional replication and want to create a copy for development & testing. The Distribution database is running on the same 2005 instance as the Subscriber and the Publication database on another server running SQL Server 2005.
If I use a TSQL script to run the restore, the database is restored to a usable state but I get the following messages:
Msg 15247, Level 16, State 1, Procedure sp_MSrepl_check_job_access, Line 112 User does not have permission to perform this action. The replication agent job [job name] was not removed because it has a non-standard name; manually remove the job when it is no longer in use. (I€™ve removed the job name from the message) RESTORE DATABASE successfully processed 46219 pages in 935.413 seconds (0.404 MB/sec).
It looks like an attempt is being made to restore replication information too. I note that the RESTORE statement has a KEEP_REPLICATION options which implies (I would have thought) that by default the replication information is not be restored.
Incidentally, before implementing replication, backing up the live database and restoring the backup to a new database took < 7 minutes but running the same process with a Subscriber database backup takes about 15 minutes.
I€™d be interested to know if anyone has any idea what I€™m overlooking or doing wrong.
I am attempting to drop a database (sales), however I receive this message: Error 3274 is "Cannot drop the database 'sales' because it is published for replication." Yet, I no longer have any publications in this database. It seems that there is some orphaned information related to a publication that existed. Any help would be great.
can anyone tell me if there is a way to determine with SMO or RMO if a database is a subscriber when using merge replication. If only have the Server and database at this point too!!
I want to provide a small app that creates a merge publication but only if the database isn't a subscriber.
We are working on using a database as a publisher and a remote database as a subscriber for one replication in Canada.
WE havw just been told that another application on a different server(publisher/ distribution) wants to replication to our database as a subscriber.
We also will need(as a publisher/distribution) to replication to a second subscriber database after we completely test the first replication to Canada.
Therefore my questions are:
1) Can a database on a local database -- our database -- on one server be a publisher and subscriber to ther different databases?
2)has any one been involved with replication on a database used both as a publisher and subscriber to different replications requirements?
What are the pitfalls aside from the scheduling conflicts?
3) In terms of database space requirements, how much disk space extra do we need? What guidelines should we use? For each table as an article, assuming we are doing the full table, do we add 100% more space or what?
I have 1 table to be merge replicated to 2 other servers (DATA5 and DATA5R). Whichever server I push a subscription to first works just fine. The second server get the following error.
Table 'UPDATE_LOG' does not exist in the Subscriber database. (Source: DATA5R (Data source); Error number: 21078) --------------------------------------------------------------------------------------------------------------- '. Check sysobjects. (Source: DATA5R (Data source); Error number: 2501) --------------------------------------------------------------------------------------------------------------- The process could not deliver the snapshot to the Subscriber. (Source: Merge Replication Provider (Agent); Error number: -2147201001) ---------------------------------------------------------------------------------------------------------------
I'm not sure why it expect the table to be there already. It doesn't on the first server. Am I missing something. If I create the substription for DATA5R first then it works and DATA5 fails.
I have a central server (CS1) with database CDB and a branch server (BR1) Inside the branch server, there are two database DB1 and DB2. CDB contains data for DB1 and DB2
I made a subscription in the brancserver named BR1:DB1(this will replicate the data from CDB to DB1) I also made a subscription for DB2 named BR1:DB2(this will replicate the data from CDB to DB2)
Unfortunately, upon starting the synchronization for BR1:DB1, the data replicated into DB1 also contains data for DB2. same happens with the BR1:DB2..
How do I filter the data that will be replicate per database..Example:only data for DB1 coming from CDB should be replicated into DB1 if that specific subscription(BR1:DB1) is synchronized.
I have replication setup between our main site and a remote one, and have recently noticed that the database at the remote site's .MDF file is about 3 times as large as the main site's. This doesn't seem to make sense since essentially all of our data is replicated between the two servers. Can anyone suggest why this might be happening and what is safe to do to shrink the remote file?
I have database (DB) on the server SQL1. This database (DB) is published on SQL1 server for SQL2 server. So, SQL1 is publisher and SQL2 is subscriber for (DB). Can I publish database (DB) on the server SQL2 for the server SQL3? I would like to implement this scheme of Publication/Subscription:
<o:p></o:p> SQL1 -> SQL2 ->SQL3..
I know, that it is simpler just to publish DB for SQL2 and SQL3 from SQL1, but, because of network connectivity, I can't do that...
Dear All, i dont know the exact reson why, but i'm guessing the reason for each table had a new guid column is because of transactional replication with update subscribers. actually i dont need this. can i change this to transactional replication? or i need to drop the column in each table? please guide me
Arnav Even you learn 1%, Learn it with 100% confidence.
When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.
so reinitialized with new snapshot but all the indexes are gone from the subscriber database.
How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot
I am doing replication through C#.net after creating the publication in server and subscription in client. But i want to know how can we view the transactions that are happening to the both databases through the C#.net code...If anybody knows please help me....
We have transaction replication set up on one of our servers whose replicated DB is used for reporting purpose:
Now, the replicated database "D" of size 350 GB has mainly grown huge as compared to Published DB (200 GB) because of the index sizes at subscribed DB. Therefore:
I have found that Database D has one table of data size 15 GB, with rows 8349533 and index size 28 GB the biggest in the database. And adding have total of 109 Non clustered indexes:
On using SP_blitzindex i have found approx 50 NC indexes unused with below usage:
Reads: 0 Writes:273,243 and total size of indexes being 18 GB
Last User Seek Last User Scan
Last User Lookup all '0'Â 0 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates;Â except for few 3 or 4 where updates are more than 4000.
Is this sufficient enough to delete all the above 49 unused NC indexes? And Can i create Missing index on Subscriber database?
We have a database which is (a subset of tables are) replicated to another via transactional replication. Whilst most changes made at the published database reach the subscriber within a matter of seconds, we have a SQL Agent job which performs a calculation in the published database and then immediately exports data from the subscriber using log shipping. The result is that the calculated changes do not make it through to the exported transaction logs in time.
Is there a way to manually "refresh" the subscriber databases using T-SQL?
We have a database we are replicating to about 8 SQL Express subscribers from a SQL 2012 SP2 publisher. The size of the database grew too large for the 10GB license limit for SQL Express and now replication refuses to replicate any of our deletions on the publisher to reduce the size of the database. I've come up with a few options below.
1) Drop one of the larger table indices on the subscriber database to get below the size restriction. Permit the replication to replicate the deleted records and then rebuild the index. (I'm not sure how important an index is to this table. Is it merely performance related?)
2) "Upsize" SQL Express to SQL Standard on the affected boxes. Allow the deletes to replicate. Backup the database, downgrade to SQL Express and restore the database back to SQL a new SQL express instance. This would involve a lot of work on each box. I'd like to avoid it if possible.
I have a strange error, when I try to create publication
Msg 3933, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1222 Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
What is it?
P. S. The error arise with all types of replication.
I have a problem with a publication which is a transactional replication on SQL Server 7.0 . I want to add stored procedure to the existing publication along with the tables . But the Enterprise manager does not allow to select or check the box against the required stored procedure although it displays when i right click on the publication and select proeprties .
Can i have some help in this regard ? Does anybody knows to add a stored procedure by means of a query .
Is there any other way to modify publication in SQL Server 7.0 although it is possible in SQL Server 2000 .?
Because 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?
i had configured a merge replication betwen 2 server and i was running fine. but due to network problems my network admin has changed the ip address and name of the publisher, distributor and subscriber.
what i did. i just drop the publication and now i m trying to create a new one. but i cannt' able to create. i dont' know y.. this is the error i m facing..
Msg 208, Level 16, State 1, Procedure sp_addmergepublication, Line 239
Invalid object name 'dbo.sysmergepublications'.
Msg 21040, Level 11, State 1, Procedure sp_MSaddmergepub_snapshot, Line 65
Publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 208, Level 16, State 1, Procedure sp_changemergepublication, Line 205
I have been wrestling with this one all day and cannot find anything on MSDN, google, etc.... I have configured replication, but cannot create a new publication either programmatically or using the wizard. I programmatically did the following: 1. I set up my local server as a distributor, and created a distribution database (@distributor set to server sysname) execute sp_adddistributor @distributor = @distributor execute sp_adddistributiondb @database = @distributionDB, @security_mode=1; 2. I registered my local server also as a Publisher (@publisher set to server sysname, @distribution = 'distributor') SELECT @sql = 'sp_adddistpublisher @publisher = ''' + @publisher + ''', @distribution_db = ''' + @distributionDB + ''', @working_directory = ''' + @directory + ''', @security_mode = 1' SELECT @sp_executesql = quotename(@distributionDB) + '..sp_executesql ' EXEC @sp_executesql @sql 3. I enabled my local database for transactional replication (@replicationDB) exec sp_replicationdboption @dbname = @replicationDB, @optname = 'publish', @value = 'true 4. I added a logReaderAgent on the ReplicationDB SELECT @sql = 'sp_addlogreader_agent @publisher_security_mode = 1' SELECT @sp_executesql = quotename(@replicationDB) + '..sp_executesql EXEC @sp_executesql @sql
All is well up to this point, no problems, all looks great. When I check the status of things using sp_help repl stored procedures to check on publisher, distributor all looks healthy. Also looks good when I use the SSMS replication interface to check on distributor, publisher, etc. . HOWEVER...... I cannot create any publications.
I tried programmatically, and also by using the wizard. I keep getting the same error :'Cannot insert the value NULL into column 'pubid', table'. This is the sql I try to run: EXEC sp_addpublication @publication = 'test3333', @status = N'active', @allow_push = N'true', @allow_pull = N'true' This is the error: Msg 515, Level 16, State 2, Procedure sp_MSrepl_addpublication, Line 1320 Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails. The statement has been terminated. Msg 14018, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1348 Could not create the publication. If I use the wizard instead of trying to create the publication programmatically, I get this error (just about the same content, but a little different wording): Creating Publication - Creating Publication 'NewPublication' (Error) Messages * SQL Server could not create publication 'NewPublication'. (New Publication Wizard)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails.
Could not create the publication. Object 'NewPublication' does not exist or is not a valid object for this operation. Changed database context to 'CypressMaster'. The statement has been terminated. (Microsoft SQL Server, Error: 515)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476
