| Author |
Topic |
|
betesh
Starting Member
34 Posts |
 Posted - 2008-06-17 : 06:26:51
|
| We are setting up the clients' database with transactional replication that allows the subscriber to send updates by pull subscriptions.The plan is to create a second database so the users can keep working on the original database while we get the replication set up and working. Once we feel confident that the replication is working on the second database, the plan was to backup the old one, then detach it, and then restore the new database with the latest data from the old one. My question is whether this would preserve all the replication configurations that we set up? Since the replication adds a column to each table what would happen to that column? Or what is an alternative, which would allow us to set up the replication without disturbing the user's work, and then implement the replication with the latest data?I am also wondering how to set the synchronization to happen once daily? I do not see where I can set that. I only see options for continuous vs on demand. Does on demand mean I can somehow schedule with an external program to run once a day?Thanks for your patience, and I hope my questions make sense. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-17 : 07:06:11
|
| You can not set up replication like this. The easiest way is to set this up as you were going to and then script out the replication scripts. Run these scripts on the database (if the database is required to be available during working hours, then you will need to do this out of hours).As they have a subscriber database (or at least from your post, that is the way it seems), your publisher must hold all the data currently, so how are you currently giving them the data? A replicated database is just a part or full copy of your main database?!?A pull subscription just means that the subscriber can pull the data rather than the publisher sending the data on a schedule. If you are using updateable transaction replication, there is more than just the few fields to look after. You also need to look after the unique keys and ranges for these.If the subscriber and the publisher already have all information at the tiume of setup, then you can tell SQL not to drop and re-create the table. If the subscriber has information the publisher does not have, then you will need to be very careful in how you go about setting this up and you will need to make sure that this data will go into your publication without any problems first. |
 |
|
|
betesh
Starting Member
34 Posts |
Posted - 2008-06-17 : 07:47:11
|
| Hi Rick,Thanks for your reply.Your idea of using the scripts against the original database makes sense.Currently the server has SQL express installed and the users use VPN to get in. Some of the users have an Access application where they enter new data. Other just view the data. The problem is that the VPN access is slow which is why we thought to do replication where once a day they can send in their updates.What do you mean by "You also need to look after the unique keys and ranges for these"?How can we be careful in handling the most recent data updates as we set up replication? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-17 : 08:43:25
|
| Ensure that your tables have primary keys, usually identities. On your publisher you would have say a range of 0 to 100, then on your subscriber you would have 101 to 200. This would mean that any records inserted on the publisher would be in the first set of values and any on the subscriber would be in the second set. When these records are written back to the publisher, there will be no clashes with pre-existing records. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-17 : 09:38:21
|
| I would go with Merge Replication and synchronize whenever you like to if you have many subscribers. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-17 : 10:37:03
|
| They seem to have one subscription and for that merge is a bit overkill. Peer-to-peer may be a better option in this situation, although it would initially take longer to set up. |
|
|
|
betesh
Starting Member
34 Posts |
Posted - 2008-06-17 : 10:56:33
|
| I can find out how many users there are who are updating. If I had to guess I'd say about 3. More than one but not many.I forgot to mention that we are planning to use the Enterprise trial for 6 months and then get the workgroup license. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-17 : 14:25:02
|
| And you can't do two-way transactional replication with SQLexpress edition .It can only be subscriber which can't send updates to publisher. But Merge replication Supports. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-18 : 22:09:13
|
| Workgroup edition can be publisher of transactional replication. |
|
|
|
betesh
Starting Member
34 Posts |
Posted - 2008-06-18 : 23:45:26
|
| Well, we already installed the trial so today I will set up the replication and see what happens. (of course on a copy of the database to start with)I'll report back with the results. |
|
|
|
betesh
Starting Member
34 Posts |
Posted - 2008-06-19 : 08:20:33
|
| I am having replication over vpn problems. I can't set up replication on my machine, only on the server. On my machine it gives me the no ipaddress or alias error. I have tried adding an alias as the server name but no luck. Also on the server how can I specify my machine as the subscriber? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-19 : 23:02:29
|
| Can the server and your machine ping each other? |
|
|
|
|
Restore data but maintain replication settings?