Hi Greg , Any Solution For Alter Tables On Publisher And Subscriber End As Well ....?
Dec 11, 2006
Hi Greg Y and seniors ones,
I am working with replication on sql server 2005 (standard edition sp1).There is scenario that some time one of the team of coders want to alter objects mostly tables being replicated on publication database but unable to do that due to error on adding column "Cannot add columns to table 'table1' because it is being published for merge replication.." in sql server 2000.
While other one want to alter replicated objects on subscriber end (like name of objects, add columns in replicated table etc).
We was working on sql server 2000 and for implementing this scenario I always use mechanism disabling/reconfigure the replication setup by the mean of long exercise.
After that, In order to alter the objects in publication database simple DDL script was executed after disabling the replication.
While manipulating the requirements on subscriber end, I created tables with same structure as replicated tables and replicate the data on self created tables by customizing code in triggers (ins_C9D57350-605A-4D87-85C0-0DB645F1CEC8 etc.) of replicated tables.
Also I have script of all replicated tables€˜s triggers but when I rerun snapshot agent it replaces the name of triggers with new one so at that time I lost my mind and I put code again in all tables€™ triggers. IS there any way to force sql server 2000/2005 generate and rerun snapshot but use already generated guid of articles instead created new one .
Let me know Plz, is there any solution/feature or any easy way in sql server 2005 to avoid this annoy exercised. I could implement this scenario.
I have a database, MobileApp, on a server called Server03, which is a subscriber to a database Stock on Server01. I create the database as a subscriber on Server01, and it gets replicated to Server03 correctly.
I now want to use MobileApp on Server03 as a publisher to mobile devices. I click to add a new publication, and add all the tables. I get all the way to the end, but when I click on finish, I get an error for each table - 'an article with a different subscriber_upload_options value already exists for object.
I have a clustering between Server A and Server B. There is a clustered SQL Server installed.
Server A is a primary node(oltp production server) and Server B is a secondary node.
i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.
Currently both servers have sp1.
Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)
So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?
I'm reading the MS online docs about replication. there's something not so clear until so far.
I have one central server and 7 client servers. Sometimes new data is entered at one client server. This should be replicated (when client comes online) to the Central Server (merge replication).
Also, the other way, once the central server gets new data (that was replicated from a client), it should be again replicated to all 6 other clients (from the central server).
now, who should i configure as the distributor, publisher and 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 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 been looking for a way to replicate data from a DB2 system running on an AIX machine. I found some information related to doing this with SQL2000, but not with 2005. Can this be done - preferably without writing our own provider.
Hi Nerds There are 2 production servers (SQL Server 6.5). One is publisher and the other one is subscriber. Data is replicated from publisher to subscriber which is a transaction based replication. Each day at 8:00PM data comes into publisher and the updations,replication,backup,reporting are carried out till 11:00AM next day. The subscriber server is used as a reporting server and the publishing server is used as the data warehouse server. I want to cleanup a table on the publisher. Since it is replicated on the subscriber, can I do delete operation on the subscriber, make the subscriber server a publisher and the publisher server a subscriber, do replication from publisher(new) to subscriber(new) and again change publisher to subscriber and subscriber to publisher without affecting the production line of the databases. I have to do this between 11:00AM to 8:00PM. Pranav.
Sadly the hostnames for the boxes I am trying to replicate are similar:
- db1.nyc.mydomain.tld - db1.sac.mydomain.tld
The servers can talk to each other over all necessary ports however when I generate a push subscription from the publisher, I am asked to add the SQL Server subscriber and it already shows DB1 listed since itself is DB1.nyc. How can I attach the remote subscriber when setting up the subscription from the publisher if the hostname conflicts in my unique scenario?I created a entry on the publishers host file to use db2sac for the SAC IP and entering that alias in as the remote subscriber but no dice.
Hi Everyone, I plan to do transactional replication. The publisher/distributor is sql2005 and subscriber is sql 2000. Is that possible. Because When I setup subscriber, I keep getting "The directory service is currently unavailable" in finding sql server replications.
For transactional replication, are there any issues and is it even possible to have 32-bit SQL 2000 publisher and SQL 2005 64-bit distributor and subscriber? Thanks
Hi,I'm trying to setup replication using SQL 2000 as the publisher and SQL 2005 as the subscriber.The problem I have is that the SQL 2000 instance is running on a non standard port 1083, so the name of the instance I'm connecting to is: TESTPC1SQL2000,1083SQL 2005 requires you to provide the "requires the actual server name" which would be "TESTPC1SQL2000" which unfortunately will not work as it's on a different port.The error message I'm getting when attempting to setup the subscription on SQL 2005 Management Studio is this:TITLE: Connect to Server------------------------------Cannot connect to TESTPC1SQL2000,1083.------------------------------ADDITIONAL INFORMATION:SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'TESTPC1SQL2000'. (Replication.Utilities)Does anyone know a workaround to this?Thanks,Andy
I want to have transactional replication setup with SQL 2000 as a publisher and SQL Express 2005 as a subscriber? Where can I get some documentation around that?
Basically, the problem I am stuck with is that while registering the subscriber in SQL2000, SQL2000 cannot recognize my SQL Express 2005 instance as a subscriber. I have tried both SQL Ent. Mgr. and sp_addsubscription.
According to BOL, you can initialize a transactional subscriber by restoring the Publisher's backup to the Subscriber DB. But any timestamp columns must be converted to binary(8) and indexed views must be converted to tables. But are there other things you must do? What about identity columns? Triggers?
I was wondering if it is possible to setup replication between a 2005 SQL Publisher and a 2000 SQL Subscriber? Is there any special setup steps I need?
I have taken over a transactional replication setup that is being usedfor fault tolerance (I know, I know...).The scenario I am concerned with is where the publisher goes down due tofailure, so we need to point our application at the subscriber and startupdating the data there. We are not using the immediate updating option.How do I go about re-syncing the publisher with the data that been addedto, or changed on the subscriber, when the publisher comes back online?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
The subscriptions will be pulled from the distributor and pushed to the subscriber. Basically, all the work will be done by the distributor (in the hope of improving performance).
Has anyone had any experience of a similar architecture? All SQL servers are running with the latest service packs.
I have a primary and secondary servers both running Windows 2000 SP3 with SQL 2000 SP3. I have set up transactional replication with the primary server as publisher and the secondary server has the distributor and subscriber DB. I am testing the scenerio where my primary server goes down and I have to make updates to the secondary server until my primary server comes back up. I am able to update my subscriber database and the transactions go into the MSreplication_queue table to be pushed back to the primary when it comes back up. When I bring the primary server back up and start the queue agent job it starts pushing the transactions over and then stops after 4 or 5 transactions with the error "Failed while applying queued message to publisher". I have attached part of the log file for the agent below
In the sql server logs I am getting this message: Replication-Replication Transaction Queue Reader Subsystem: agent Repl Queue Reader failed. Failed while applying queued message to publisher. Error: 14151, Severity: 18, State: 1
I have a setup of transaction replication between one publisher and subscriber in the Same server.Now, I need to add a new subscriber to the existing publisher. So publisher database name is DB_A and Subscriber 1 name is DB_B. So the new subscriber will be DB_C. Is this kind of setup possible on one server?
If yes then at the time of reinitialization is it going to apply the snapshot on DB_B as well as DB_C?Also let say if due to disk error DB_B gets corrupted then will data be still replicated between DB_A and DB_C? (Assuming publisher, subscriber 1 and 2 are sitting on individual disks).
Are there any requirements that dictate the SQL Server version for the distribution agent for a SQL 2000 publisher with a transactional push subscription to a SQL 2005 subscriber?
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.
version SQL Server 2005 SP2 table in SQL Server 2000 (80) compatibility level publication in SQL Server 2000 compatibility level - Distributor : SQL Server 2005 SP2 - Subscriber : SQL Server 2000 SP4 + hotfixes (version 8.00.2187)
There is only one article in our publication (a simple table with a GUID and a nvarchar(50) columns), and we have left the default resolver.
1 - The first synchronization of the subscription is successfully completed, and it is the same for the different updates/inserts/deletes or conflicts.
2 - Then we change the Resolver of our article by our own COM-Based Custom Resolver (developped in C# 2.0). This resolver only change the default behaviour: the subscriber always win (this is for a test, in the future we will have a complex business logic). All the synchronizations works fine and do what we want in the conflicts.
3 - We rollback the resolver to the default one... and here is the problem! The synchonizations stop to work correctly. For all of them we've got the same error:
It is no more possible to synchronize this subscription... Any remark will be helpfull cause we did not find anything on the net concerning this error.
How to check if web merge sync is working between a subscriber and publisher thru HTTPS ? SQL port 1433 at subscriber is blocked so no direct connection to subscriber.
I'm trying to set up a transactional replication from SQl Server 2005 to SQl server 2000. The Distributor and the publisher are on our server and the subscription is supposed to be on a SQL 2000 server on a different location. Before upgrading to 2005 I didn't have problems - the replication from 2000 to 2000 was working perfectly.
After I succesfully created the distributor and the publication the first problem that I encountered was that when I tried to create the subscription it was giving me an error that I cannot use an IP to acces the server. I realized to fix that issue by creating an Alias in the SQL Server Configuration Manager for the server where I wanted to create the subscription (a push subscription).
Now when I try to add the subscriber it gives me another message "Execute permission denied on object 'sp_MS_replication_installed', database 'master', owner 'dbo'" - so I cannot create the subscriber.
The user that I use to create the subscriber on the 2000 server is dbo for the subscriber database but it doesn't have rights on the master database. Also I realized that on the 2000 server I still have the old subscription but I cannot delete it - for the same reason - no access on the master database. Before upgrading to 2005 I had the exact same rights on the 2000 server.
I'm using merge replication to maintain a backup copy of my main (publisher)MSDE database. A push subscription periodically (1 per minute) updates the backup DB. It's intended that if the main db goes down then the backup (subscription) db can be configured as a publisher. This must all be performed via scripting. The initial configuration of the main publisher and subscription is controlled via scripting, which works fine. The problems occur when I try to configure the subsciber to become a publisher. A script is executed on the subscriber but fails at the point when it's configuring the publisher detail. The error is something like "unable to configure a publication for a database setup as an anonymous subscription". I'm guessing that there are subscritpion artifacts added to the database which need to be removed before it can be configured as a new publisher.
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String
With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName
'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With
'Notify user MsgBox("Data restored successfully")
Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table
'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want) Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename)
'User notification ) MsgBox("A new empty database was created successfully")
'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification MsgBox("The database was compacted successfully")
