May 21, 2014 at 7:48 am
I have a control room with the following configuration:
- 2 PCs running the control software, each with its own SQL Server Database for data store (the 2 databases are identical)
- 1 PC is the active PC, the other is the standby PC (hot backup)
- the Active PC collects production data and store them to its own SQL Server database. The Standby PC does nothing, but listens to the Active PC: in case of Active PC failure, the Standby PC becomes the Active one.
- the SQL Server Database in the Standby PC must be constantly up to date with the data collected by the Active PC (the Standby PC does nothing to its own Database)
The problem is the Synchronization of the two databases: they must have the same data up to the milliseconds, even if the only PC collecting data is the Active one. The data collection frequency is up to 30 samples per second, i.e. the Active PC inserts 30 samples per second into its own database.
Our first solution was running a .NET Application of our own on both PCs, which, by means of the MS Sync Framework classes ( http://msdn.microsoft.com/en-us/sync/bb736753.aspx ), keeps the two databases in synch. But when we reach peek insert frequencies i.e. 30 samples per second, we experience some problems, timeouts starting occuring in the Sync Framework Orchestrator.
Maybe MS Sync Framework is not there for such a scenario, I don't know!
Any suggestion about best practices to get that "hot backup" working? Maybe leaving the Sync Framework aside, choosing a completely different solution....
Thank you in advance
May 21, 2014 at 7:54 am
pgmoschetti (5/21/2014)
I have a control room with the following configuration:- 2 PCs running the control software, each with its own SQL Server Database for data store (the 2 databases are identical)
- 1 PC is the active PC, the other is the standby PC (hot backup)
- the Active PC collects production data and store them to its own SQL Server database. The Standby PC does nothing, but listens to the Active PC: in case of Active PC failure, the Standby PC becomes the Active one.
- the SQL Server Database in the Standby PC must be constantly up to date with the data collected by the Active PC (the Standby PC does nothing to its own Database)
The problem is the Synchronization of the two databases: they must have the same data up to the milliseconds, even if the only PC collecting data is the Active one. The data collection frequency is up to 30 samples per second, i.e. the Active PC inserts 30 samples per second into its own database.
Our first solution was running a .NET Application of our own on both PCs, which, by means of the MS Sync Framework classes ( http://msdn.microsoft.com/en-us/sync/bb736753.aspx ), keeps the two databases in synch. But when we reach peek insert frequencies i.e. 30 samples per second, we experience some problems, timeouts starting occuring in the Sync Framework Orchestrator.
Maybe MS Sync Framework is not there for such a scenario, I don't know!
Any suggestion about best practices to get that "hot backup" working? Maybe leaving the Sync Framework aside, choosing a completely different solution....
Thank you in advance
If you want two databases be fully sync, you should use Mirroring or AlwaysOn.
If both are on the same network and you have low latency values, Mirroring will do the trick.
May 21, 2014 at 8:01 am
I agree that synchronous database mirroring is probably the way to go here. Decent hardware should be able to keep up with 30 actions per second without much issue. The usual bottlenecks in the real world are network latency (throughput rarely) and disk write performance (esp. tlog).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 21, 2014 at 8:57 am
TheSQLGuru (5/21/2014)
I agree that synchronous database mirroring is probably the way to go here. Decent hardware should be able to keep up with 30 actions per second without much issue. The usual bottlenecks in the real world are network latency (throughput rarely) and disk write performance (esp. tlog).
Interesting solution, but, after some reading on Always ON topic, I guess we need at least Windows Server 2008, don't we? Unfortunately the 2 PCs in the control room are running just Windows 7 Professional
Thank you, regards
May 21, 2014 at 10:13 am
pgmoschetti (5/21/2014)
TheSQLGuru (5/21/2014)
I agree that synchronous database mirroring is probably the way to go here. Decent hardware should be able to keep up with 30 actions per second without much issue. The usual bottlenecks in the real world are network latency (throughput rarely) and disk write performance (esp. tlog).Interesting solution, but, after some reading on Always ON topic, I guess we need at least Windows Server 2008, don't we? Unfortunately the 2 PCs in the control room are running just Windows 7 Professional
Thank you, regards
AlwaysOn is a SQL2012 feature so you need to follow software requirements. Don't remember if you can install SQL2012 on Win7.
Mirroring is a SQL2005, 2008 and 2012 feature. But I think runs smoother on SQL2008 and above. There were some initial issues with latencies on Mirroring and internal MS code design.
May 21, 2014 at 12:52 pm
sql-lover (5/21/2014)
pgmoschetti (5/21/2014)
TheSQLGuru (5/21/2014)
I agree that synchronous database mirroring is probably the way to go here. Decent hardware should be able to keep up with 30 actions per second without much issue. The usual bottlenecks in the real world are network latency (throughput rarely) and disk write performance (esp. tlog).Interesting solution, but, after some reading on Always ON topic, I guess we need at least Windows Server 2008, don't we? Unfortunately the 2 PCs in the control room are running just Windows 7 Professional
Thank you, regards
AlwaysOn is a SQL2012 feature so you need to follow software requirements. Don't remember if you can install SQL2012 on Win7.
Mirroring is a SQL2005, 2008 and 2012 feature. But I think runs smoother on SQL2008 and above. There were some initial issues with latencies on Mirroring and internal MS code design.
Even worse Always On is an ENTERPRISE Edition Feature of SQL Server 2012+. That's why I didn't mention it - certainly didn't sound like a scenario that would have that.
Now that it is revealed that you are on Windows 7 I have to mention that if you are using SQL Express then mirroring isn't allowed. If you are using the Development Edition of SQL Server, well, that isn't allowed. 🙂
The mirroring improvement came from compression of the log stream between the two machines.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 22, 2014 at 12:00 am
if your second DB is standby. you can also refer log shipping or transaction replication as per your current setup
May 22, 2014 at 10:31 am
srikantmeister (5/22/2014)
if your second DB is standby. you can also refer log shipping or transaction replication as per your current setup
One would figure that there is a high probability that real-or-near-real-time data transfer was required or they would not have used Sync Framework in the first place. So TLOG shipping probably isn't viable.
Transactional Replication can REALLY hammer a system, much more so than mirroring, due to all of the (fully-logged) activities that happen with the distributor. Much more memory is used to create/retrieve/process the data pages for the distributor too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 23, 2014 at 1:33 am
TheSQLGuru (5/22/2014)
One would figure that there is a high probability that real-or-near-real-time data transfer was required or they would not have used Sync Framework in the first place. So TLOG shipping probably isn't viable.
Transactional Replication can REALLY hammer a system, much more so than mirroring, due to all of the (fully-logged) activities that happen with the distributor. Much more memory is used to create/retrieve/process the data pages for the distributor too.
You are perfectly right Mr Boles, the application needs a near-real-time data transfer, beacause as soon as the Primary PC fails, the standby PC shall take control over and its own database must have the data up to date exactly as that of the faulted PC. So is AlwaysOn really a feasible way? Or is it too slow? I ask that becaues I see that it is not so easy to implement, if one starts with no notion about that and I'd prefer not to waste efforts on that.
Thank you in advance
May 23, 2014 at 1:44 am
it depends on the size of DB as well.
if there is no issue with costing and other stuff go for failover clustering and other...
May 23, 2014 at 7:11 am
pgmoschetti (5/23/2014)
TheSQLGuru (5/22/2014)
One would figure that there is a high probability that real-or-near-real-time data transfer was required or they would not have used Sync Framework in the first place. So TLOG shipping probably isn't viable.
Transactional Replication can REALLY hammer a system, much more so than mirroring, due to all of the (fully-logged) activities that happen with the distributor. Much more memory is used to create/retrieve/process the data pages for the distributor too.
You are perfectly right Mr Boles, the application needs a near-real-time data transfer, beacause as soon as the Primary PC fails, the standby PC shall take control over and its own database must have the data up to date exactly as that of the faulted PC. So is AlwaysOn really a feasible way? Or is it too slow? I ask that becaues I see that it is not so easy to implement, if one starts with no notion about that and I'd prefer not to waste efforts on that.
Thank you in advance
Always On is not an option for you. It is a SQL Server Enterprise Edition feature, which I don't think you can install on Windows 7. Even if you can I am CERTAIN that you cannot create a Windows Server Failover Cluster on Win7, which one of the requirements for AGs.
Remember though - Always On is simply glorified mirroring. It should be no faster or slower than mirroring (assuming you don't make the secondary readable, which can do nasty things, including to your primary database). Both have one thread for the tlog move/replay. This effectively limits you to maybe 50MB/sec of activity. I doubt that your monitoring system has anywhere near that much activity. If you do, your hardware is almost certainly not up to it anyway.
I would set up a replay scenario that mimics your current machines/network and set up mirroring and run your exact workload against it to see how it holds up. This is easy to do with the Replay features that come with SQL Server 2012.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 23, 2014 at 7:25 am
One thing to be aware of is that Database Mirroring is now deprecated. I'm not saying don't use it, but that Microsoft intend to remove it in a future version of SQL Server.
Regards
Lempster
May 23, 2014 at 8:08 am
Lempster (5/23/2014)
One thing to be aware of is that Database Mirroring is now deprecated. I'm not saying don't use it, but that Microsoft intend to remove it in a future version of SQL Server.Regards
Lempster
I don't know if deprecated is the right word. It is set to be removed on future releases. In fact, I think is still available on SQL 2014.
There are lot of SQL 2008 and SQL 2012 machines around so I doubt MS will pull the plug right now on clients that are actively using it.
AlwaysON, which does not seem to fit OP's need here, is not mature enough to me (disclaimer, my opinion here). Not saying it's not a viable option on certain cases, but I've seen several issues myself during testing and horror stories with colleagues and people online.
May 23, 2014 at 8:12 am
sql-lover (5/23/2014)
I don't know if deprecated is the right word. It is set to be removed on future releases.
That's what 'deprecated' means!! I didn't say that mirroring is not available in 2012.
May 23, 2014 at 8:21 am
Lempster (5/23/2014)
sql-lover (5/23/2014)
I don't know if deprecated is the right word. It is set to be removed on future releases.That's what 'deprecated' means!! I didn't say that mirroring is not available in 2012.
And I did not say you indicated it was not. I said I do believe is still available on SQL 2014.
To me (my bad it that was not the case) the post "sounded" like a scary one, indicating to stay away of Mirroring on this particular case. And again, on my personal opinion, I don't see how that can be a problem for him.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply