1

I see here that we have a lot of questions about best approach to mirror a single database. I got a new job and people there is under a task to mirror all databases, to a secondary one, in a different city for security (if we lost a server, we have everything on the mirror).

1) to mirror ALL databases, should I repeat the mirroring step for every database?

2)is this the best approach to high availability?

I'm thinking about something with raid, so we have a mirror disk that if a disk fails, there is this secondary ( i just forgot what raid is this, im on the cellphone and will search later).

I think there will be a lot of requests to mirror the data ( it's not going to be synchronous) if we really chose to mirror ALL databases ( there are more than 200).

What would you guys do in this situation? I would even prefer log shipping.

I just found this question but i really need to sleep now. Will study it tomorrow. It says about log shipping too.

Improve this question
4
  • There are some constraints you should be aware of with mirroring, in particular the maximum number of mirrored databases per database instance. This number can float anywhere up to 50+, though I have often seen it start to affect performance in the single-digit range as instance activity will heavily influence these limits. More info on the details behind why these limits exist can be found here.
    – John Eisbrener
    Apr 17, 2017 at 14:04
  • This is what I thought. With so many databases, there will be for sure performance problems. I'm out of Idea on how to handle this. I made log shipping, mirror and etc before but, with one database.
    – Racer SQL
    Apr 17, 2017 at 14:24
  • I would suggest Log Shipping based on your version of SQL Server if you can tolerate some small level of data loss in the event of a failure. Do a google search to look for one of the many approaches already out there to configure it for multiple databases.
    – John Eisbrener
    Apr 17, 2017 at 14:30
  • Yes, this is what I'm thinking about. Or log Shipping or Replication. The problem with log shipping is, I will need hundreds of jobs, each one for each database.
    – Racer SQL
    Apr 17, 2017 at 14:50

2 Answers 2

Reset to default
2

I do not see what version of SQL Server you are using and from the tag assuming it is SQL Server.

From Books Online: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On Availability Groups instead.

  1. If you are at 2012 or above I highly recommend you to consider Always ON Availability group. Besides High Availability you can also gain performance benefit by offloading certain maintenance operations and read traffic. It will depend on your setup, application design and requirement. You can read more in details here. FYI :This technology is built upon mirroring which you mentioned in the question.

  2. Log Shipping is definitely another option but your read only copy cannot be available for read traffic all the time and not in sync as close to real time as Always ON Availability Group. See details about Log Shipping here.

Few more options that you can consider based on your requirements:

  1. Failover Clustering
  2. Failover Clustering in conjuntion with storage level replication.
  3. AlwaysOn Failover Clustering Instances
  4. Replication
Improve this answer
5
  • As I see. Always On needs to be part of a Windows Cluster. Deploying Always On Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster. I know how Cluster works and I worked some time with it, but, can the cluster be made across cities? like in a different server? Because I worked with a 4 node cluster with failover, but all nodes were on the same server.
    – Racer SQL
    Apr 16, 2017 at 14:31
  • Do you mean all nodes are in same data center? Yes it is possible to be in different cities depending on the version you are using. See details here .
    – SqlWorldWide
    Apr 16, 2017 at 14:57
  • Thank you very much. now its my first day here, and it's sql server 2008 that they want to mirror.
    – Racer SQL
    Apr 17, 2017 at 13:44
  • @RafaelPiccinelli I will highly recommend (as you are in 2008 edition) this [article] (technet.microsoft.com/en-us/library/ff658546(v=sql.100).aspx) if you have any input on the choice. Please accept the answer if it satisfied your quesiton.
    – SqlWorldWide
    Apr 17, 2017 at 13:48
  • Yes for Sure SqlWorldWide. I'm just waiting some more opinions. People don't usually access StackExchange during weekends haha. I'm trying to gather more information as possible. but for now , yes this is going the be the accepted answer.
    – Racer SQL
    Apr 17, 2017 at 14:17
1

Since the comments are running long, I'm dumping this to a formal answer.

First, in regards to mirroring, there are some hard limitations on the number of databases that can be mirrored between instances. This number can float anywhere up to 50+, though I have often seen it start to affect performance in the single-digit range as instance activity will heavily influence these limits. More info on the details behind why these limits exist can be found here.

Because you're using SQL 2008, you are a little more limited in what's available. Basically you're left with Log Shipping or Replication.

Log Shipping is lightweight and is what I would recommend if you can accept some level of data loss in the event of a failure. You can log ship hundreds of databases between instances as performance limitations are related more to file-level and bandwidth constraints.

Replication, much like Mirroring, also suffers from volume limitations as a publication requires some resources to maintain. What's arguably worse about Replication is that it may require DML changes be made to the data model of a database if tables don't have PKeys. These changes are necessary to ensure records are replicated properly and your subscriber(s) are kept in sync. There are other considerations as well, found here, so Replication is not something I would say is simple to implement and should only be used after ample testing.

If you don't think Mirroring, Log Shipping, or Replication will work, your other options are basically hardware related such as SAN-to-SAN replication, or simply upgrade to SQL 2012 or later and take advantage of Availability Groups as stated by SqlWorldWide.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.