December 11, 2015 at 1:07 pm
Hi,
We have our datacenter in 2 different location, e.g. LocationA and LocationB.
on locationA we have installed mssql cluster and it is running fine. LocationB is the place we want to setup for disaster switch over.
Management has some unique requirements to implement
1- don't use any db configurations like logshipping
2- Solution should be able to switch over to LocationB and Switch Back to LocationA whenever required.
3- Solution should be able to sync DDL and DML changes in database
4- We should be able to control frequency of sync at table level. like in single database we may able to configure TableA to sync at 10 Sec, TableB at 5 sec etc
5- we should bee able to define selected table in real time sync, not a single transaction should lost
My initial thought was to implement loghsipping, but they refuse to accept it. They need realtime sync with switch over functionality .
do you have any suggestion to implement solution on given requirements ?
Thanks
December 11, 2015 at 1:20 pm
Good luck with this one!! If you find something that does even half of that stuff please post here so I can learn about something I don't know about yet!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 11, 2015 at 1:36 pm
Bloody hell. Surprised you haven't been asked to build it on Express Edition, make it a real challenge.
Step 1. While management are still high on whatever drugs they're taking, ask for a pay rise. 😀
Step2.... ???
1. not even sure what's meant? Build a db solution without using the db?
Mirroring solves 2 and 3. Maybe availability groups. Do they count as db configuration?
Don't know of anything in SQL Server that can realistically do 4 or 5.
December 12, 2015 at 2:02 am
Yes, it is crazy requirement. I can write custom ETL to sync few tables but write routine for all tables and specially if you have 50+ databases , it is hell. we also need to manage DDL changes as well.
Except logshipping and cluster, what other option we can enable at databases level to have real time sync of a database ? this should be reversible process A<-> B.
December 12, 2015 at 5:03 am
thbaig (12/12/2015)
Yes, it is crazy requirement. I can write custom ETL to sync few tables but write routine for all tables and specially if you have 50+ databases , it is hell. we also need to manage DDL changes as well.Except logshipping and cluster, what other option we can enable at databases level to have real time sync of a database ? this should be reversible process A<-> B.
Database mirroring can do real time (i.e. synchronous) and near-real-time (i.e. asynchronous) DML and DDL sync. Can be persnickety when it comes to "reversing", depending on the state of the original primary (just like Always ON and Log Shipping).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 12, 2015 at 9:17 am
You could avoid DB configurations if you use one of the disk level replication features available from some of the larger disk vendors like EMC. But it's not going to allow all the detailed craziness that you're being tasked with. Table level variable degrees of replication... never, ever seen anything like that, let alone wondering how exactly it works if there is any referential integrity (please tell me the database has referential integrity).
I'm pretty sure you're being given an impossible set of requirements.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2015 at 11:27 am
Gazareth (12/11/2015)
Step 1. While management are still high on whatever drugs they're taking, ask for a pay rise. 😀
This!
2, 3 and 5 (mostly, though it'll be for all tables and there can still be data loss if the network to DR fails before the primary server) can be done with availability groups, but they don't satisfy 1 or 4. Basically it's going to be impossible to do all of those.
Seriously, ask management what they're smoking and how many millions are in the budget for a completely custom 'HA' solution.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2015 at 7:23 am
Yes, I have same impression that this lead us to develop custom ETL. but due to referential integrity and number of table it is always impossible to manage. Also DDL changes will also be challenge.
December 13, 2015 at 2:20 pm
Custom ETL won't satisfy any of those requirements (except maybe 4). To even start, you'd probably need to write IO filter drivers, or maybe a transaction log reader, implement something using the Windows Clustering APIs for failover, etc, etc.
How's your C++/Assembler?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2015 at 2:38 pm
Other things to consider for a custom solution: how do you control which is the active location? Point apps/connections at the right server? Transactional consistency across sites? Prevent writes on the secondary? Handle a split brain scenario?
You can push these questions back up to management, not all can be controlled at SQL level.
Not trying to be awkward but these requirements are way beyond what's possible. Asking the questions in the other direction may be the way to finding realistic solutions to the business needs.
December 14, 2015 at 7:55 am
It seems to me that your management are confusing business and technical requirements. All this stuff about different synchronisation timescales sound like business-critical and non-critical tables.
My suggestion is to go back to your management and ask for a meeting to produce the business requirements, and allow you to propose the technical solution.
Some of SQL Server's data distribution processes do allow you to deal with data at an individual table level, but others, such as Always-On, Mirroring and P2P replication work at the database level. This can mean that both critical and non-critical tables in a database get the same quality of service. Your management need to be steered away from requiring that non-critical data must have a lower quality of service than critical data, and towards allowing a different service level if this is compatible with the technology solution.
None of the SQL Server solutions are trouble-free, but all of them have had vastly more testing than any custom solution you may design.
The place I used to work at uses P2P replication to cover both their scale-out and DR requirements. The web traffic was greater than a single DB server could handle, so they needed to scale out to cope with the traffic, and they used a model of concentrating all write traffic on one server and distributing read traffic over the P2P farm (which was hosted in multiple locations). Because P2P was used, any of the servers in the farm could be designated as the master 'write server', which covered the main DR issues. The farm was always kept large enough to allow peak traffic to be served if one DB server was down.
The disadvantage with the P2P approach was that non-critical data got the same QoS as critical data, and in theory critical replication could be stalled due to a problem with a non-critical table. However, overall simplicity of a standard approach made P2P the right solution. More recently Always-On was looked at as a replacement for P2P as this seemed to provide greater simplicity of operation, and this was being implemented by other people when I left.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply