May 23, 2014 at 8:23 am
TheSQLGuru (5/23/2014)
.... assuming you don't make the secondary readable, which can do nasty things, including to your primary database ....
Unfortunately operators have the option to query the database also on the Standby PC, i.e. the secondary database MUST be readable (on both PCs MS Reporting Services are working to deliver on demand reports: one can ask for a report on the Primary and on the Standby as well).
Thanks
May 23, 2014 at 8:29 am
pgmoschetti (5/23/2014)
TheSQLGuru (5/23/2014)
.... assuming you don't make the secondary readable, which can do nasty things, including to your primary database ....Unfortunately operators have the option to query the database also on the Standby PC, i.e. the secondary database MUST be readable (on both PCs MS Reporting Services are working to deliver on demand reports: one can ask for a report on the Primary and on the Standby as well).
Thanks
Then Mirroring won't work, as the Mirror databases is not available for active use.
Your other option, probably the only one using integrated MS features would be Transactional replication. But like Kevin stated, it will introduce more workload on your systems. You really need to size your hardware very well and be sure connectivity between machines is good enough to avoid latency issues.
I think you need to go back and clarify with your team, or maybe you need to revise your requirements, it seems to me you are contradicting yourself. Because in your initial post you indicated:
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.
So, if the standby PC does nothing, it should not read the database, only take an active role during a failure. On that case, Mirroring or MS Cluster is the way to go.
For reporting purposes or offloading work from primary, replication is used, but it is not for HA, Cluster is usually for that. If you use replication only and primary goes down, you won't be able to connect unless you also recreate logins on the other server. Replication is more to replicate some objects from the database, like an important table, not to replicate the whole database.
May 23, 2014 at 8:37 am
sql-lover (5/23/2014)
pgmoschetti (5/23/2014)
TheSQLGuru (5/23/2014)
.... assuming you don't make the secondary readable, which can do nasty things, including to your primary database ....Unfortunately operators have the option to query the database also on the Standby PC, i.e. the secondary database MUST be readable (on both PCs MS Reporting Services are working to deliver on demand reports: one can ask for a report on the Primary and on the Standby as well).
Thanks
Then Mirroring won't work, as the Mirror databases is not available for active use.
Your other option, probably the only one using integrated MS features would be Transactional replication. But like Kevin stated, it will introduce more workload on your systems. You really need to size your hardware very well and be sure connectivity between machines is good enough to avoid latency issues.
I think you need to go back and clarify with your team, or maybe revise your HA requirements. Because in your initial post you indicated:
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.
So, if the standby PC does nothing, it should not read the database, only take an active role during a failure. On that case, Mirroring or MS Cluster is the way to go.
For reporting purposes or offloading work from primary, replication is used, but it is not for HA, Cluster is usually for that. If you use replication only and primary goes down, you won't be able to connect unless you also recreate logins on the other server. Replication is more to replicate some objects from the database, like an important table, not to replicate the whole database.
Or you simply remove the ability for read-only querying against the secondary.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 23, 2014 at 9:30 am
... The Standby PC does nothing ...
That is: the Standby PC does not collect data, but it is still there for reading purposes.
you simply remove the ability for read-only querying against the secondary
That is not an option: the control room must have 2 PCs ready for data consulting and that's by design.
One more problem is that the 2 PCs have each 2 network adapters with 2 different IPs: if one network adapter fails and that was the path of choice for synchronization, synchronization must go further on the other path. Is that automatically taken into account by Mirroring?
May 23, 2014 at 9:41 am
One more problem is that the 2 PCs have each 2 network adapters with 2 different IPs: if one network adapter fails and that was the path of choice for synchronization, synchronization must go further on the other path. Is that automatically taken into account by Mirroring?
Not to my knowledge.
What performance tuning have you done on your .NET sync app, the hardware, network and SQL Server?? It is completely possible that your use of Win7 on what may well be LOW end machines could be your sole problem. Or a poorly architected sync app or SQL Server settings, etc. I have been consulting on SQL Server for 15 years now and have yet to find ANY system yet that was done right top to bottom. Most are simply horrible in many ways.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply