January 28, 2010 at 6:38 am
Dear all
i have a server for windows 2003 and sql server database 2005 and have around 10 database
and also i have the same server lying in canada and it has also got the same windows server 2003
and database server 2005
now i want to sysnc them both means what database i have in here i want the same to be in the server
in canada and i want that to happen regularly that both server should sync in the evening so how can i
do that can anybody help
regards
puneet brar
January 28, 2010 at 7:10 am
It depends on how you intend to use that other server in Canada. Can you provide more details as to what you are trying to accomplish?
January 28, 2010 at 7:16 am
Can you connect to the server in canada from your server.
if yes, then you have many different methods to sync up your databases in 2 locations.
for eg:
Snapshot Replication (schedule once / twice a day)
Other Replications
Log shipping
DB Mirroring!
Pavan.
January 28, 2010 at 7:19 am
thanks for the prompt response
Yep i can log on to server in canada basically i want that both the database should be same as sometimes my server in india goes down or the internet gets down so thats a problem
so what i want is that when i leave office the database in the canada server should be the same so that if this server goes down we can use that
January 28, 2010 at 7:22 am
In this case you can go for
Log shipping
Transactional replication
database mirroring
explore these before you take a step!
Pavan.
January 28, 2010 at 7:30 am
can you please tell me some step by step procedure i mean how can i do that and hhow can i start on that score
i will b ereally thankfull
February 1, 2010 at 7:43 am
I do not believe that it is a straight forward answer.
There is a lot of analysis that needs to be done before you can choose which high availability mechanism you would choose.
Factors to consider are:
1. Max time Latency between the two databases
2. Geographical location
3. Network bandwidth between these two servers
4. Number of databases you need to replicate
5. Size of databases
6. Usage pattern of databases
After pondering on all these things, you will find a log of articles on MSDN and other sites on how to setup each one up. You can also go through sql server books for a beginner level detailed explanation.
Regards
Akhil
February 1, 2010 at 9:37 am
Puneet Brar (1/28/2010)
Dear alli have a server for windows 2003 and sql server database 2005 and have around 10 database
and also i have the same server lying in canada and it has also got the same windows server 2003
and database server 2005
now i want to sysnc them both means what database i have in here i want the same to be in the server
in canada and i want that to happen regularly that both server should sync in the evening so how can i
do that can anybody help
regards
puneet brar
How big is the database? What will be the size of it after 1 one year?
February 1, 2010 at 10:33 pm
1. Max time Latency between the two databases
latency between the two databases should not be an issue as i suppose i do not want the database to be mirrored all the time i just want them to be mirrored or replicated on every evening at around 20:00 hours
2. Geographical location
One database server is in india and one is in canada
3. Network bandwidth between these two servers
bandwidth is one side we have a t100 line and one side that is in india we have a 784mbps connection
4. Number of databases you need to replicate
number of databases are eight to 10
5. Size of databases
max 1gb the size i of each database once we can manually shift the whole database but after that
i want them to be mirrored
6. Usage pattern of databases
usage pattern is mainly development purpose as one database remains free the whole day
and we just wanted to use them when one is down
After pondering on all these things, you will find a log of articles on MSDN and other sites on how to setup each one up. You can also go through sql server books for a beginner level detailed explanation.
February 2, 2010 at 2:24 am
From what you've said, your connection will possibly sustain mirroring with automatic failover, if you have another SQL Server you can use as a witness (this can be a SQL Server Express installation). This will allow you High Availability, and you will be able to schedule snapshots of the database which is set to "recovering" (the mirror) for reporting purposes. In this configuration, the mirror will be entirely synchronised with the principal as changes are committed to the mirror prior to being comitted on the principal. The downside of this is that if your connection isn't sufficient to sustain the mirroring process, the speed of your system will suffer. If you notice performance issues, you should consider using mirroring in Performance mode. This doesn't require a witness server and is asynchronous, so it won't affect system performance. In this configuration, if a problem occurs on the principal database, you will need to perform a manual failover.
Since you mention you have 8 to 10 database you wish to "replicate" you need to be careful if the databases have interdependencies since mirrored databases can fail over individually, not necessarily collectively.
If you have the capacity, I would recommend you test both these configurations and see which is more suitable for your infrastructure.
I hope this is of some help.
February 3, 2010 at 6:27 am
True HA/DR is a VERY complex yet critical topic and you really should consider getting an expert on board to advise/assist you. You are heading for trouble without that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply