Is there a way to move a SQL replicated database from one server to another without dropping the publication?
I have SQLCE setup with SQL server 2000; I want to redo the server but needs to move my replication to a temp server and then back.
The problem is that I do not want to drop my subscription and recreate it…it needs to stay the same.
How can I list all of the Subscriptions in a CE Database that was created with the .Net System.Data.SqlServerCe.SqlCeReplication.AddSubscription method?
After I create the CE database in an sdf file, I can connect to it with SQL Servr Mgmt Studio, then see the list of Subscriptions in the Object TreeView, under "Replication"
But I can't find that Subscription list in any properties or methods of System.Data.SqlServerCe.SqlCeReplication or System.Data.SqlServerCe.SqlCeConnection
Should I be looking in RMO features like Microsoft.SqlServer.Replication??
We want to confirm that a local CE database was last synced from the expected SQL Server database by checking the Publisher property of each subscription in the CE database.
Thanks
Any advice that can be offered on this subject would be much appreciated.
We are debating whether to backup/restore the replicated databases or simply copy the mdf and ldf files accross and Attach them. The question is whether the replication information is retained for the publshed objects when the files are copied across? Alernatively, must we use backup/restore with KEEP_REPLICATION set for the restore?
We also plan to back up and restore the distribution, publication, master, msdb etc databases as well as rename the SQL Server to have the same name as the original.
Do these things need to happen in any particular order?
Does the timing of the renaming of the SQL Server matter?
At least some of the these operations (restoring the master database and renaming the SQL Server) need to be done in single user mode. Is is best to restore all the system databases in single user mode?
Lastly, is it necsssary (from a SQL Server point of viewpoint) to rename the computer to have the same name as the SQL Server?
Thanks,
Dick Campbell
Hi all! Hope you can help us with this one. We installed SP 2 of Sql server 2005 few months ago, and that caused a strange problem with merge replication.
We have about 150 subscribers in one merge publisher, and 200.000 new rows generated each day (retail system). Most tables are filtered.
Replication and filtering works fine, except 0,1% --> 0,2% of rows are not replicated correctly. These "problem-rows" are always replicated correctly to the publisher database, but publisher can't always replicate these rows further to other subscribers. Before SP2 everything worked fine.
Same problem occurs with filtered and non-filtered tables.
SQL Server shows no errors or warnings, everything SEEMS to work fine.
We have to make "dummy"-updates (like update SALES set id=id) in publisher database, to wake up the replicator. And always after these dummy updates all problem-rows will be replicated correctly from the publisher to subscribers.
Why do we have to "wake up" the replicator time to time with dummy updates? Is there a bug in SP2?
We are using merge replication and it is working fine. We are looking for increase in on numeric column 10,5 instead of 7,2. How I can do it?
Any suggession highly appreciated by us.
Thanks
Hi,
We have to move two of our servers in US hosting SQL Server 2000 database to different location. These servers are clustered using MSCS and uses SAN to store database data. The database on this cluster hosts publisher and distributor. The other two sites in Europe host updateable subscribers. We are using transactional replication. The downtime will be around 24 hours during which servers will be physically moved from one location to other. During this time we will keep the two subscribers running and let the changes queued on subscribers. I have listed following steps based on my limited knowledge of SQL Server. Could somebody please advise if this approach is valid and if I have missed any steps?
1. Make sure no users are accessing database
2. Backup database (In case of emergency need)
3. Stop snapshot, Log Reader, Queue Reader and Distributor Agents
4. Stop SQL Server Service and SQL Agent
5. Take OS backup
5. Shutdown the server
6. Ship the servers and OS backup to new location
7. Setup servers at new location and restore OS backup
8. Start SQL Server Service and SQL Agent
9. Start agents listed in step 3
Regards,
Nilesh
I got Server A with 30 Databases and I was asked to move 30 databases to Server B…
But server A is having 2 databases as Publication and Distribution to two different servers….. and Server A is a subscription for 6 Databases from other servers….
Once I move the databases from Server A to Server B…… what is the best way to get the Replications too…. I mean Server B should have 2 Db’s as Pub & Distr and 6 Db’s as Subscription…
Is there any way I can script it out in Server A and run the same script in Server B with some modification…. so that the Replication work…..??
I have Jobs and Maintenance plans…. Which should also move from Server A to Server B…. I know using DTS...... is there any better way..??
Please some one give me ur suggestion on this…
Thanks
Bob
I have a database A on server_1 that is being merge replicated to database B on server_2 which is production a box
NOW
I want to restore database A on server_3 that is being merge replicated to database B on Server_4 which is test box.
How about do I do that with replication being involved?
Thanx
How can I do an alter table in some table that replicated database ?
I got the error message when I try !
Hi, I have setup a merged replication on two server. Server A contains the Publisher and the Distributor database and Server B contains Subscriber.
If the database at the Subscriber got corrupted. I am thinking maybe i can 'restore' the subscriber database by resynchronising the data from the publisher to subscriber.
But what happen if the Publisher database failed? How should one restore the publisher database and then restore the replication setup?
What will be a good backup/restore strategy for such a setup?
I have a table that was set up with a primary key - that i need to change. The problem is that the database is being replicated to a remote location, and will not allow me to remove or change the primary key on a published database.
Due to the size of the database and bandwith limitations it is not an option to re-initialise the published database.
Is there any to do this without breaking replication
We are going to implement either a replicated database or use log shipping to another database to support our reporting needs. Basically we are moving reporting over data off our transactional database.
With this in mind, I have several questions:
Are there any special configurations in terms of disks for a database in this situation?What are the recommended raid types?
Also, we currently store our data in one filegroup and indexes in another so obviously I'll want those on 2 separate physical disks other than the C:. What about the log files? Do I care where they go (on C: or another physical drive separate from C:, data filegroup drive and index filegroup drive)?
Thanks!
Can someone lend some assistance in this? It sounds like it should beable to be done.We have a large replicated database in SQL 2000. We need to move themdf and ldf files to a location on another drive.Will doing this affect or break replication in any way? Is there anyway to prevent this?Thanks in advance.Glenn DekhayserVoyant Strategies
View 1 Replies View RelatedWhen I try to remove a table, it complains because it's part of apublication. I could script the publication and subscriptions suchthat they can be deleted, the table removed, and then added again.But, I not sure if this is the 'best' approach.Anyone faced this challenge before?Regards,gary
View 1 Replies View RelatedHi All,
I have SQL server 2005 Database which is having following Replication stuff.
1. 6 merge Subscribers
2. 5 Snapshot Subscribers (Push Susbribers)
3. 3 Transactional Publisher
Due to the Performance Issue, there is need to move SQL server from the Current Server to an Higher End Server.
I want to keep all the Replication settings after movement of the Database. Can anyone tell me how to acheive this requirement?
Is there a possibility to keep the Replication settings ? Even we can have the Same System Name to the New Server.
Awaiting response...
Thanks,
Thams.
I'm a novice with replication and am in need of direction on how to restore replicated databases. Server A replicates 5 databases to server B using transactional replication. Server A also contains the distribution database. I'm perfoming PUSH replication.
The development area asked me to restore Server A with a copy of production data located on server C. After I restored the data from C to A, I noticed that server A was no longer published. Is this normal?
I re-published the 5 databases, but noticed that the Log Reader Agent has now been running for 2 hours. I'm trying to give myself a crash course in replication, but am coming up empty regarding (1) the prefered method for restoring replicated databases and (2) determining why the Log Reader Agent is taking so long.
Any suggestions?
Thanks, Dave
I have a production database which uses merge and snapshot replication. The Merge is for 3 tables. The snapshot is to update the rest of the data once daily. I use a Full recovery model and perform database backups (full, differential) and transaction log backups.
I have a database optimization plan which runs 4 times a week. This plan performs and integrity check and rebuilds the indexes. This optimization plan is growing the transaction log by about 8MB each time it is run and we are running out of space on the drive for our log files. The space is not being reused.
I saw in another post where Gail Shaw suggesting using SELECT name, log_reuse_wait_desc FROM master.sys.databases to see why the log space is not being reused. On the database in question, the above returns "REPLICATION".
A colleague tried to backup the transaction log a couple of times to truncate the log this weekend. She was going to perform a DBCC Shrinkfile command afterwards. But the truncate failed. Again looking into things it seems replication prevented the truncation.
We are looking at stopping the merge replication or even removing it to truncate the log file and then recreate the merge replication. How to handle shrinking the log file for now and then seeing if there are any checks or changes I can perform which will allow the transaction log space to be reused.
Hi
I often deal with a problem when replicated database is down due to
electric outage or any other scheduled type of downtime. Primary
database is up but distribution agent is going down 'after 10 attempts
to connect to replicated database'. That's the problem I think because
not only distribution agent goes in undetermined state and I have to
restart sqlagent service, but I need to go through whole process of
snapshot and applaying it to replicated db. I would like it to
accumulate transactions in distribution database and once replicated db
is up - distribute them.
I wonder if anybody can tell me how to configure distribution thread so
it does not go down after 10 unsuccessfull attempts but stays for day
or two.
Thank you, Gene.
Hi,
I have transactional replication set up on a DB and run database maintenance scripts (index defrag, update stats, etc) on this DB daily.
Will the maintenance job/scripts also be propagated to the replica (subscriber) causing these maintenance operations to be performed on the replica OR do I need to create separate jobs on the replica DB (subscriber) also? I'm guessing the latter but wanted to verify to make sure I wasn't missing something. Thanks!
I'm trying to replicate a table(s) in access 2000 to MS SQL server 2000/2005 programatically on a timed instance and then have these tables merged in SQL to create one table.
Any guidance appreciated.
Thank you,
I have a publisher database set up for a merge replication. This is using parameterized filter with join filters.
I also have a stored procedure that does deletes & inserts on the table where the parameterized filter is applied to aid in changing a subscriber's eligibility to receive so and so data. I have observed that running the stored procedure takes extraordinarily long and as a result, the log file grows to a size 1.5 - 2.5 times the database size.
At first I reasoned that this might because I had it set up to use precomputed partitions and changing it requires recalculating the partitions. As a test, I turned off the precomputed partitions. Didn't work. I turned on "optimize synchronization" AKA "keep_partition_changes", which normally is not available when you have precomputed partition on, and that didn't work, either.
At this point, I think I can rule out precomputed partitions being a problem here but I'm stumped now what else I should do to reduce the amount of log writes being required. We do need the parameterized filters & join tables, so that can't go.
Hi,
I'm trying to get my first replication going, and I have set up a database successfully, along with merge replication, however when I attempt to create the first subscriber, I get a permissions issue, regardless of what I do. In the SQLCESA30.LOG file, I get the following entries:
2006/01/07 16:59:36 Hr=00000000 SQLCESA30.DLL loaded 0
2006/01/07 16:59:58 Hr=80004005 ERR:OpenDB failed getting pub version 28627
I posted the initial thread under the "Replication" forum, but am including this 'pointer' post here due to a lack of replies there, and the relevance of this thread to this forum. Please see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193693&SiteID=1 for more details.
Thanks.
I have used backup and restore method to upgrage 2000 databases to 2005 database.
What do i need to do in order to for my application or users to use databse in 2005 and remove 2000
let me know
thanjs
I am new to sql and my boss want me to write the program for database and he wants to pull the info from existing table from sql server which is used by Microsoft GP Dynamics and write me code or some kind of front end so when he wants he can pull same data from GP dynamics table and generate some report with other custom table.
How i can do this task? What I have to do in sql so I can use same table to view info in real time so that means if i enter new data in the table it will show up same time in the front end as well.
I have Transaction Replication setup on a Server X. Server X is publisher and distributor. Server Y is the Subscriber.Everything works perfectly. Â But I need to change the Database with a different DB on Server X that is being replicated to Server Y. I have following questions:
 Â
 1-  Do I create a new distributor on Server X and delete the current distributor?
  2- Create a new Replication Setup on Server X and Y ?Â
  3- Delete the subscriber DB on Server Y (Subscriber) ?
  4- Can I just disable the current setup on Server X and Server Y instead of deleting it
Hi
I want to transfer a database from one PC to another. (Both running SQL Server 2005 express)
I have copied the files (SQL Server Database Primary Data File and SQL Server Database Transaction Log File) from the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData folder of the source PC to the destination PC (Same folder).
I was under the impression that using Microsoft SQL Server 2005 - SQL Server Management Studio Express these files would be picked up and could be used, do I infact need to do something to attach the files/database so that they will appear in SQL Server management?
I have an SQL database running on an old server and i am upgrading the server, but i am having some problems moving my SQL database over. Has any one got a work through they could mail me to give me a hand!!
Cheers
Mike
Hi, Everyone,
I need to copy a Database onto another Server, what is the best way to do it?
Thanks
Our vendor has created a sql server database at his location
We have to transfer this database to our location and run the web application which access this database.
What is the best way to do this.
Can the vendor zip database and sent to us.
What do we do with ZIP file.
Can somebody help
I'm very new to SQL Server 2005, so please be kind.We had a vendor in and they loaded their software and the databasethat was created is sittingon the root (c:) drive. On this server we have another drive wherethe database should reside, isthere a way to move this database to the correct drive? Thank you forany help that you couldprovide.Dave Schaeffer
View 1 Replies View RelatedI have two computers, both with SQL Express 2005 and SQL Server Management Studio Express. I created a database on one computer, and need to copy it to the other computer. I'd like to do it all through the Management Studio interface, without using SQL commands, since I only need to do this manually.
I detached the two files, copied them to the second computer, and tried to attach them. This is generating lots of messages about not having the necessary permissions of various sorts. I think the main problem is that I don't know how to set up the database's users in a way that allows the second computer to attach the database.
I've tried Windows authentication, using a Windows user that has the same name and password on both computers, and I've defined that Windows user as a user within the database. I've connected to SQL when logged in as that user.
I've tried SQL authentication also, defining the same SQL user within both SQL Servers, as db_owner. But when I try to attach the database, I see no place to specify the user that I intend it to use, and it still fails. If I connect to SQL as that user, then SQL doesn't have permission to look at the file system to find the MDF file.
I'd appreciate the help in learning how to do this with both Windows authentication and SQL authentication, via the Management Studio's GUI. Most of the documentation I've seen covers how to do it by using SQL language commands, and I'd like to avoid that.
Hi all,
I want to transfer all the data from one of my Ms Access tables to SQL Server table, using C#.
How can i do it?
Thanks in advance,
J.Jasmeeta.
Hi all,
I have an Ms Access table and a MsSql table. I am running a windows service in my localhost where the data from Ms access table will be copied to Ms sql table for every one minute. Before copying the data, the Ms sql table will be flushed inorder to avoid replicates.
Now i want to copy only the latest records updated within 1 min in Ms access table, to Ms sql table.
My Ms access table
Name Id
jas 100
meena 101
viji 102
My Ms sql table
Name Id
jas 100
meena 101
viji 102
After 1 min, say 2 records are added to my Ms access table like,
Name Id
jas 100
meena 101
viji 102
bhuvana 103
pinky 104
Now i want to insert only the latest records from Ms access to Ms sql like,
Name Id
jas 100
meena 101
viji 102
bhuvana 103
pinky 104
how to do this? thanx in advance.
Jasmeeta.