February 11, 2009 at 4:10 am
Hi to everybody.
Sorry if i post a long message, but it is much better to exlpain everithing at the beginning. Also, 'im quite e newbie to this stuff, so please try to be supportive. 🙂
I have this scenario: a costomer of mine, a dentist, runs two sites, in two different towns. The two sites used to sare one server, and they were (and are) connected via VPN.
Due to performance considerations, and to the fact that the ADSL line is not fully reliable, the two sites have been recently separated. The database duplicated, backeup forn on site and restored to the other.
Still, they need, on occasion, to send one patient from one site to the other. This means they need to access the data of any patient, both local and "remote" (remotenes is relative to the site, of course), and also to access the appointment (agenda), for reading and updating. Right now, the use a VCN connection to access a remote client, accessing, that way, the remote database.
This is completelty unsatisfactory, to my opinion. Despite the fact there is a lot of data replcation, the two databases are drifting away day after day; backup and maintenance are quite complex.
After reading some, i believe that data base replication is the solution. But after reading some, i am confused: for some of my needs, it seems the best solution is transactional replication. For other, it seems to be merge replication.
Some more things:
. The system has to be designed from scratch. The current servers are old, and they run W2000 server and Windows XP (go figure!). So, for this time, i will not have to face with what i have. I think to set up 2 servers for the 2 sites, Windows 2003 server (rumors are that 2008 is not fully reliable yet).
. I understand i will have to make my customer by the licence for SQL 2005, standard edition. Right now, they are running SQL Express. Is it necessary to install two SQL standard edition, i believe, given the two servers have to be exactly the same, both publisher and subscriber.
. The data are not under my control. There is some managemente software, written in VB6, as far as i know, connecting via ADO. To setup an empty data base, i have to install the software to the server, with the "server" option (versus client). To move the databases to a different location could be difficult, if not impossible.
Thanks to everybody that could read me to tis point.
AFM
February 11, 2009 at 5:15 am
You really need more information on this:
The type of data and quantity of data to be replicated
and one thing that differs from most of them is does it get updated at the subscriber end? I understand it does.
Points to consider for Transactional Replication:
Transfer changes to the subscriber instantaneously
The databases replicated requires low latency
The Publisher has a high volume of transactions(insert,delete,update)
Points to consider for Merge Replication:
Subscribers(mobile servers/databases) make changes and then later synchronize the changes with the Publisher
The scenario/plan requires change at once rather than incremental like transactional
February 11, 2009 at 6:23 am
You really have lots of work to do on this. Your main issue is that you cannot start from the scratch. You have data that is existing that needs to be kept as it is. Also your two DB is totally out of Synch. To b honest, in this case you have explained, I would have a service that Synchs the data as they change. Much more easier to maintain.
This is just my opinion. 🙂
Another thing, I am not sure who said SQL 2008 is unstable. From what I have read from other users, it is much better performance wise than SQL 2005.
-Roy
February 11, 2009 at 7:28 am
From what you've described, it sounds like you should be going with either Merge or Peer-To-Peer transactional replication.
Regular transactional replication does not seem to fit your situation due to the fact that changes cannot be made at the subscribers. From your description, it sounds like you would need the database to be updated at either location.
Further, if you do require the ability to modify the same data at each location, Merge is probably your best solution because of its conflict resolution abilities.
No matter which solution you go with, you're going to have to do some work to merge all of your data from both databases to whichever server you designate as the publisher. You can't set up replication with your data being out of sync. (unless you are ok with losing data on the subscribers)
Regards, Jim C
February 11, 2009 at 7:49 am
Thank you Roy, i know i have a lot of work to do.
Roy Ernest (2/11/2009)
You really have lots of work to do on this. Your main issue is that you cannot start from the scratch. You have data that is existing that needs to be kept as it is. Also your two DB is totally out of Synch.
I can reset to sync my two DBs, by adding records missing from one to the other, and vice versa. I can do that with a program, the only complexity are the number of tables. Of course, i would resynch when ready to backup the DB from the old server(s), and restore to the new ones. Taking care for the few identity fields.
I can't do this on a routine basis, because this kind of synchronization would not allow for deleteted records.
To b honest, in this case you have explained, I would have a service that Synchs the data as they change. Much more easier to maintain.
This is just my opinion. 🙂
This sounds very interesting: is there something existing, or something i should implement? I did this kind of thing, but the DB was designed by me, and i used UID fields and timestamps.
Another thing, I am not sure who said SQL 2008 is unstable. From what I have read from other users, it is much better performance wise than SQL 2005.
The instabilty i have heard of was about Windows Server 2008, not SQL Server. Same number, differnte stuff.
February 11, 2009 at 7:58 am
Thanks Jim, it sounds good to me.
Because as i was reading the MSDN documentation, they suggest a transational approach. But i felt that merge is what i need. And i am confirmed by your words.
Jim C (2/11/2009)
From what you've described, it sounds like you should be going with either Merge or Peer-To-Peer transactional replication.Regular transactional replication does not seem to fit your situation due to the fact that changes cannot be made at the subscribers. From your description, it sounds like you would need the database to be updated at either location.
Right. Data has to be changed at both location. And latency time is not critical, given it is kept less than one minute or so.
By the way, i could easily signal the user if the VPN connection is alive: that way, she would know she will have to check.
Further, if you do require the ability to modify the same data at each location, Merge is probably your best solution because of its conflict resolution abilities.
No matter which solution you go with, you're going to have to do some work to merge all of your data from both databases to whichever server you designate as the publisher. You can't set up replication with your data being out of sync. (unless you are ok with losing data on the subscribers)
I was aware of this, and i am ready to programmaticlay synchronize the two DBs. It is a good thing the DB designer did not set any relation between tables.
February 11, 2009 at 7:58 am
aldo_marchioni (2/11/2009)
Hi to everybody.Sorry if i post a long message, but it is much better to exlpain everithing at the beginning. Also, 'im quite e newbie to this stuff, so please try to be supportive. 🙂
I have this scenario: a costomer of mine, a dentist, runs two sites, in two different towns. The two sites used to sare one server, and they were (and are) connected via VPN.
Due to performance considerations, and to the fact that the ADSL line is not fully reliable, the two sites have been recently separated. The database duplicated, backeup forn on site and restored to the other.
Still, they need, on occasion, to send one patient from one site to the other. This means they need to access the data of any patient, both local and "remote" (remotenes is relative to the site, of course), and also to access the appointment (agenda), for reading and updating. Right now, the use a VCN connection to access a remote client, accessing, that way, the remote database.
This is completelty unsatisfactory, to my opinion. Despite the fact there is a lot of data replcation, the two databases are drifting away day after day; backup and maintenance are quite complex.
After reading some, i believe that data base replication is the solution. But after reading some, i am confused: for some of my needs, it seems the best solution is transactional replication. For other, it seems to be merge replication.
Some more things:
. The system has to be designed from scratch. The current servers are old, and they run W2000 server and Windows XP (go figure!). So, for this time, i will not have to face with what i have. I think to set up 2 servers for the 2 sites, Windows 2003 server (rumors are that 2008 is not fully reliable yet).
. I understand i will have to make my customer by the licence for SQL 2005, standard edition. Right now, they are running SQL Express. Is it necessary to install two SQL standard edition, i believe, given the two servers have to be exactly the same, both publisher and subscriber.
. The data are not under my control. There is some managemente software, written in VB6, as far as i know, connecting via ADO. To setup an empty data base, i have to install the software to the server, with the "server" option (versus client). To move the databases to a different location could be difficult, if not impossible.
Thanks to everybody that could read me to tis point.
AFM
Hi Aldo,
As already suggested, you need to do more research on your clients requirements, as from your overview, the system is not what I would title production grade.
Firstly, I'll approach this from a "let's fix what you already have" perspective. I would suggest you first advise your customer to upgrade hardware as you are already thinking and upgrade operating system to Windows 2003 R2 Standard Edition or Windows 2008 Standard Edition to improve performance and availability in general. In terms of SQL Server version, I would recommend you go for SQL Server 2005 or SQL Server 2008 Standard Edition to avoid future upgrade as the business grows, thus "future proofing the investment".
You have two sites where data can change at both sites at any point, thus you need a strategy to ensure minimal latency in committing data changes and propogating between sites. Standard transactional replication will not fit the bill due to fact you have more than one site where data can change at either site. As mentioned earlier, peer-to-peer replication would provide a possible solution, but ideally on SQL Server 2008, as its better able to handle data conflicts. Due to the communication link not being very realiable, I would opt for a merge replication topology with regular wake-up of the merge agent to synchronize data between sites. You will need to consider possibility of data conflicts and would need to plan for a conflict resolution strategy as part of migration.
This is very high level overview and as everyone has already suggested, research everything in terms of current setup, before making any final decisions. Also, ensure you include high availability and performance as part of any hardware upgrade strategy, such as clustering or database mirroring.
Thanks,
Phillip Cox
SQL Server Consultant
MCITP - SQL Server 2008|DBAdmin
MCITP - SQL Server 2005|DBAdmin
MCTS - SQL Server 2008
MCTS - SQL Server 2005
MCP - SQL Server 2000
February 11, 2009 at 7:59 am
.
To b honest, in this case you have explained, I would have a service that Synchs the data as they change. Much more easier to maintain.
This is just my opinion. 🙂
This sounds very interesting: is there something existing, or something i should implement? I did this kind of thing, but the DB was designed by me, and i used UID fields and timestamps.
Check out Transactional Replication which replicates incremental changes to another server.
Check this out:
February 11, 2009 at 8:01 am
Ah... Windows Server 2008. 🙂 Thats a different ball game all together. I would wait for cpouple of Service pack to come out before I move into it. 😛
You will have to write a service for what I am talking about. You can use Windows Communication Foundation to get this done. This will be much more safer when you dont have a good Internet connection between the two servers. Replication can be a hassle when you have bad internet connection.
-Roy
February 11, 2009 at 8:05 am
Thank you khrisna
Krishna (2/11/2009)
You really need more information on this:The type of data and quantity of data to be replicated
and one thing that differs from most of them is does it get updated at the subscriber end? I understand it does.
Actually, the thw sites should mirror each other. With a latency time not critical.
I believe merge replication will do.
February 11, 2009 at 8:10 am
Thanks to everybody
You are SO supportive, ans so quick ... 🙂
February 11, 2009 at 8:22 am
aldo_marchioni (2/11/2009)
Thank you khrisnaKrishna (2/11/2009)
You really need more information on this:The type of data and quantity of data to be replicated
and one thing that differs from most of them is does it get updated at the subscriber end? I understand it does.
Actually, the thw sites should mirror each other. With a latency time not critical.
I believe merge replication will do.
This is the ideal solution and I have a client with loads of ADSL links across multiple countries with over 100 subscribers for a logistics warehouse application and this works fine 99% of the time, although they do occassionally have issues with links, but all in all, it works nicely.
Good Luck,
Phillip Cox
SQL Server Consultant
MCITP - SQL Server 2008|DBAdmin
MCITP - SQL Server 2005|DBAdmin
MCTS - SQL Server 2008
MCTS - SQL Server 2005
MCP - SQL Server 2000
February 11, 2009 at 8:22 am
Another thing to keep in mind about merge replication is that it will add a CPU overhead to your servers. In our production environment, we saw about a 5% increase in avg CPU after implementing merge. Of course, this also depends on the quantity of data you are publishing and the quantity of data changed during a given sync cycle.
Regards, Jim C
February 11, 2009 at 8:46 am
Hi Roy,
thanks for your advice. But one thing i do not understand: how do i synchronize record deletion?
I can easily add a record to a DB where it is missing, but that way, I will restore deleted records all the time.
also, if you can point me to some example. I did never use WCF
February 11, 2009 at 8:51 am
For what I am saying, you will need to tag any transaction that happens with what kind of operation it was. (Update, insert and delete).
-Roy
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply