December 9, 2011 at 11:47 pm
We basically need copies of a database across all our servers. Currently we are using replication but it is just getting more and more maintenance with synonyms and other stuff. I would like to know how other DBA' get this done. Some of the other options which i think are available but can't be used are mentioned below:
i) Cannot use logshipping or mirroring because DB has to be always online.
ii) May be can use DB mirroring feature with 2008 where secondary is online, haven't tried this. Has anyone used this feature before?
Is there any third party tool which can get this? Please advice.
Thanks
December 10, 2011 at 1:03 am
Just to clarify what you're asking:
You have a database that you wish to have copies of on multiple other servers. These copies need to always be available for querying (select only? insert/update/delete?).
What is the acceptable delay between modifications at the source database showing up on the copies?
How big is the database?
where are the other database servers located? (same data center, across the globe, etc)
Which edition of sql server are you running?
December 10, 2011 at 8:17 pm
SpringTownDBA (12/10/2011)
Just to clarify what you're asking:You have a database that you wish to have copies of on multiple other servers. These copies need to always be available for querying (select only? insert/update/delete?).
What is the acceptable delay between modifications at the source database showing up on the copies?
How big is the database?
where are the other database servers located? (same data center, across the globe, etc)
Which edition of sql server are you running?
Thanks for your response. I have answered each of your questions below, please advice.
You have a database that you wish to have copies of on multiple other servers. These copies need to always be available for querying (select only? insert/update/delete?).
Only selects.
What is the acceptable delay between modifications at the source database showing up on the copies?
Hmmm..should be less than 5 secs
How big is the database?
Max size 40 GB
where are the other database servers located? (same data center, across the globe, etc)
All the servers are located in same datacenter
Which edition of sql server are you running?
SQL 2008R2 standard
December 11, 2011 at 4:09 am
Please evaluate your requirements on following:
Features Supported by the Editions of SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc645993.aspx
Selecting a High Availability Solution
December 11, 2011 at 9:23 am
Dev (12/11/2011)
Please evaluate your requirements on following:Features Supported by the Editions of SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc645993.aspx
Selecting a High Availability Solution
Thanks.That would give me some idea but i think i have listed out my requirements. I was looking for options what other DBA's use.
December 16, 2011 at 12:26 pm
sqldba_newbie (12/11/2011)
Dev (12/11/2011)
Please evaluate your requirements on following:Features Supported by the Editions of SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc645993.aspx
Selecting a High Availability Solution
Thanks.That would give me some idea but i think i have listed out my requirements. I was looking for options what other DBA's use.
Any other option?
December 16, 2011 at 1:53 pm
Maybe you can also use a DTS/SSIS solution to pull over new data every x time, unless your systems need is actually real time or near time.
Did you consider a Service Broker solution for your replication issue ?
This is message queuing between sqlserver databases or instances.
It really depends on your needs as well as the level of expected load for this system.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2011 at 4:41 pm
ALZDBA (12/16/2011)
Maybe you can also use a DTS/SSIS solution to pull over new data every x time, unless your systems need is actually real time or near time.Did you consider a Service Broker solution for your replication issue ?
This is message queuing between sqlserver databases or instances.
It really depends on your needs as well as the level of expected load for this system.
We will need real time data, may be we can try SSIS to run every min.Not sure how the performance would be.
I thought service broker will no longer be used in Sql2008( we will be migrating early next year)
I was thinking about Mirroring with snapshot option?
December 18, 2011 at 9:33 am
Hmmm... I don't think the SSIS option will be best for you since you stated before that data latency shouldn't exceed 5 seconds.
You can't actually implement database mirroring since you can only have one mirror for each principal. If you only needed the one mirror, mirroring with jobs automatically dropping/creating snapshots would probably have solved your problems.
You can configure Log Shipping and leave the copies in read-only state (you mentioned that you would only need to make select statements to the 3 database copies). But this could create as big an administrative hassle as you may have right now with Replication and, again, won't meet your 5 sec. latency requirement.
As of right now, I believe Replication is the best way to go for you.
---------------
Mel. 😎
December 18, 2011 at 11:24 pm
I thought service broker will no longer be used in Sql2008( we will be migrating early next year)
I've never heard about it and couldn't find any information about this. Can you provide prooflink, please?
I was thinking about Mirroring with snapshot option?
Not an option for you, as you need real-time data and multiple servers to replicate data to. Log shipping will not meet your latency requirements too.
Replication or Service Broker are the most obvious options for you.
December 18, 2011 at 11:58 pm
sqldba_newbie (12/16/2011)
ALZDBA (12/16/2011)
Maybe you can also use a DTS/SSIS solution to pull over new data every x time, unless your systems need is actually real time or near time.Did you consider a Service Broker solution for your replication issue ?
This is message queuing between sqlserver databases or instances.
It really depends on your needs as well as the level of expected load for this system.
We will need real time data, may be we can try SSIS to run every min.Not sure how the performance would be.
I thought service broker will no longer be used in Sql2008( we will be migrating early next year)
I was thinking about Mirroring with snapshot option?
I think you mix up "notification services" with Service Broker.
Service Broker was new with SQL2005 (beta at RTM and supported with SP1)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 19, 2011 at 3:25 pm
Hi
I'm slightly puzzled by the issues you may be having with replication. I've used it for years with both Sybase and SQL Server and it's very robust and generally quite easy to manage, particularly where the subscribers are read only. You mention it's becoming complicated with synonyms, etc, but you don't replicate synonyms which are just pointers at the end of the day, only physical objects (tables, views, stored procs, etc, as appropriate). Most sites only replicate tables in most circumstances. What issues are you having that is making it hard to manage?
If you are only replicating to read only copies at the subscribers, you simply create a single publication with all your tables (assuming you are not making it more complicated than it needs to be by replicating views, procedures, etc) and create a subscription for each server you're replicating to. Use backup/restore to sync the databases to the subscriber. Once it's running there is little work to look after it, unless you're changing your schema a lot.
If you are adding new synonyms for example, that does not affect replication, just add the synonym on each server they are required on. As they are not replicated objects they don't have an effect on replication so there is nothing to maintain in the publication.
Cheers
Roddy
December 28, 2011 at 12:22 pm
Roddy.CAMERON (12/19/2011)
HiI'm slightly puzzled by the issues you may be having with replication. I've used it for years with both Sybase and SQL Server and it's very robust and generally quite easy to manage, particularly where the subscribers are read only. You mention it's becoming complicated with synonyms, etc, but you don't replicate synonyms which are just pointers at the end of the day, only physical objects (tables, views, stored procs, etc, as appropriate). Most sites only replicate tables in most circumstances. What issues are you having that is making it hard to manage?
If you are only replicating to read only copies at the subscribers, you simply create a single publication with all your tables (assuming you are not making it more complicated than it needs to be by replicating views, procedures, etc) and create a subscription for each server you're replicating to. Use backup/restore to sync the databases to the subscriber. Once it's running there is little work to look after it, unless you're changing your schema a lot.
If you are adding new synonyms for example, that does not affect replication, just add the synonym on each server they are required on. As they are not replicated objects they don't have an effect on replication so there is nothing to maintain in the publication.
Cheers
Roddy
Thanks. What you mentioned about synonyms gng in combination with replication theoretically it is right but practically it does break the synonym. I will try explain exact scenario where it breaks. If you have a synonym to remote table and that synonym is used inside the view then resetting up snapshot or rebuilding index on the source table breaks the synonym. We have opened a case with MSFT and after days of troubleshooting they said this cannot be fixed as they will need to make quite a bit of changes in the engine to make it work. Sounds crazy right?...If you have a solution for this i will be more than happy to test it out.
December 28, 2011 at 12:28 pm
SqlMel (12/18/2011)
Hmmm... I don't think the SSIS option will be best for you since you stated before that data latency shouldn't exceed 5 seconds.You can't actually implement database mirroring since you can only have one mirror for each principal. If you only needed the one mirror, mirroring with jobs automatically dropping/creating snapshots would probably have solved your problems.
You can configure Log Shipping and leave the copies in read-only state (you mentioned that you would only need to make select statements to the 3 database copies). But this could create as big an administrative hassle as you may have right now with Replication and, again, won't meet your 5 sec. latency requirement.
As of right now, I believe Replication is the best way to go for you.
Thanks. Any third party tool which does bit level replication and keeps db in ready only mode?
December 30, 2011 at 9:44 am
Hi
So from your latest post, I gather you have a synonym to a remote table? That is, it points to a table on another database in the same machine or another server? If I am not misunderstanding, I would create a local synonym for each one on the remote server. Then you just replicate the tables to keep things simple. Are your set of synonyms constantly changing?
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply