Log Shipping vs database mirroring

Hi Mvps:

Whats the major difference between Database mirroring and Log shipping?. I did read few differences but was told when it comes to applying the transaction logs on the secondary server , DB Mirroring has a diffferent methodology. Is that right?. Please clarify.

Thanks

Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring supports only one miror for each principal database. Database mirroring also supports automatic failover if the principal database becomes unavailable. The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting, etc.

Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondaries for each primary database.

Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages:

1. it provides backup files as part of the process
2. multiple secondaries are supported
3. it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error

More information about both technologies is available in SQL Server 2005 Books Online in the topics "Understanding Log Shipping" and "Overview of Database Mirroring".

Phillip Garding
Senior Program Manager
Microsoft SQL Server

|||

Log shipping uses the tried and true restore logic.

Database mirroring uses a variation on the restore logic that is completely incorporated into the mirroring process. Plus redo is multi-threaded in Enterprise Edition.

|||

Hi Meher,

Other differences are listed below:

With Log Shipping:

Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required

With Database Mirroring:

Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred
Server Limitation: Can be applied to only one mirror server
Failover: Automatic
Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change: Role change is fully automatic
Client Re-direction: Fully automatic as it uses .NET 2.0

Hope this helps

Thank you,
Saleem Hakani
Saleem@.sqlnt.com

|||

thankx Saleem,

i would appreciate if you could help me. i am gloin to have my iterview in the comin weeks. so if you could

help me this problem i will b greatful... my email is is gibtronics2000@.gmail.com. The reply of yours is really helpful.

keep it up...

thankx

gangster

|||One important thing to keep in mind is that snapshots from db mirroring only works with Enterprise edition -- $$$ (especially if you need 2 or 3 copies of it). One last thing to consider is bi-directional transactional replication (this takes some thinking to have your schema support it correctly though).|||Saleem, Phillip

Phillip says "Mirroring is preferable to log shipping in most cases"...
Could I submit one case to you, please?

We have a web site for which we already have failover clustering but we want to setup some disaster recovery plan.
We will have cheaper hardware in a different location with exactly the same software and a "recent" copy of the database. We have connection between the two sites to keep our copy up-to-date.
A manual "switch" would be fine.

Although there is no plan for this, it might be useful to use the secondary database for reporting but I think this should be fine with both technologies (as we have Sql Server Enterprise Edition).

Could you advise between Asynchronous Mirroring and Log Shipping?

Thanks

Eric
|||

I have to disagree with Saleem on a few points.

Client Re-direction: Fully automatic as it uses .NET 2.0

It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes adavatage of mirroring if you tell it the alternate server in the connection string.

Failover: Automatic

Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds

Role Change: Role change is fully automatic

Failover is only automatic if you are running mirroring in High-Safety mode with a Witness server that is up and functioning and able to contact the mirroring partner.

I should also add that failing back to the original primary server is not supported. If you want mirroring to return to the original primary server, as soon as it becomes available, you have to set up a job to do it. I set up a job that runs every minute on the mirroring server that checks for databases that are the primary mirror and that are in the synchronized state (Where sys.database_mirroring.mirroring_state = 4 And sys.database_mirroring.mirroring_role = 1) and fail it over.

Another point about mirroring is that the official recommendation by MSFT is to only mirror a maximum of 10 databases per server because each mirroring session consumes 2 threads. Logshipping does not have this limitation.

Yet another good point about mirroring is that you can mirror the publisher of merge replication and configure replication to automatically use the alternate database if it fails over. This is designed to replace Alternate Synchronizing partners in replication as Alternate Synchronizing partners is being deprecated.

You should also take note that you can do both mirroring and log shipping.

|||Robert,

Thanks for the info but it does not really help me in choosing...

Can I phrase my question slightly differently...

I don't see how log shipping could have a serious negative effect on my primary server as it's pretty simple in principle...
Is there anyway that Asynchronous Mirroring could have a negative impact on my Primary Server?

Eric

|||

Hi Robert,

I will be setting up Mirroring in the next few days. I cam across this thread and saw your comments:

"It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes advantage of mirroring if you tell it the alternate server in the connection string."

Can you post sample code on connection string to do this? I believe our developers do not have this string comnnection option.

Another thing: "I should also add that failing back to the original primary server is not supported". Do you mind if you give me a bit of technique on how you do the job for failover to revert back to the primary node? This feature is new to me. I am not really into things like this. And one question, if the failover returns back to the primary server, do all transactions made in the mirrored database be automatically reflected on the primary?

Thanks!

Log Shipping vs database mirroring

Hi Mvps:

Whats the major difference between Database mirroring and Log shipping?. I did read few differences but was told when it comes to applying the transaction logs on the secondary server , DB Mirroring has a diffferent methodology. Is that right?. Please clarify.

Thanks

Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring supports only one miror for each principal database. Database mirroring also supports automatic failover if the principal database becomes unavailable. The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting, etc.

Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondaries for each primary database.

Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages:

1. it provides backup files as part of the process
2. multiple secondaries are supported
3. it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error

More information about both technologies is available in SQL Server 2005 Books Online in the topics "Understanding Log Shipping" and "Overview of Database Mirroring".

Phillip Garding
Senior Program Manager
Microsoft SQL Server

|||

Log shipping uses the tried and true restore logic.

Database mirroring uses a variation on the restore logic that is completely incorporated into the mirroring process. Plus redo is multi-threaded in Enterprise Edition.

|||

Hi Meher,

Other differences are listed below:

With Log Shipping:

Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required

With Database Mirroring:

Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred
Server Limitation: Can be applied to only one mirror server
Failover: Automatic
Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change: Role change is fully automatic
Client Re-direction: Fully automatic as it uses .NET 2.0

Hope this helps

Thank you,
Saleem Hakani
Saleem@.sqlnt.com

|||

thankx Saleem,

i would appreciate if you could help me. i am gloin to have my iterview in the comin weeks. so if you could

help me this problem i will b greatful... my email is is gibtronics2000@.gmail.com. The reply of yours is really helpful.

keep it up...

thankx

gangster

|||One important thing to keep in mind is that snapshots from db mirroring only works with Enterprise edition -- $$$ (especially if you need 2 or 3 copies of it). One last thing to consider is bi-directional transactional replication (this takes some thinking to have your schema support it correctly though).|||Saleem, Phillip

Phillip says "Mirroring is preferable to log shipping in most cases"...
Could I submit one case to you, please?

We have a web site for which we already have failover clustering but we want to setup some disaster recovery plan.
We will have cheaper hardware in a different location with exactly the same software and a "recent" copy of the database. We have connection between the two sites to keep our copy up-to-date.
A manual "switch" would be fine.

Although there is no plan for this, it might be useful to use the secondary database for reporting but I think this should be fine with both technologies (as we have Sql Server Enterprise Edition).

Could you advise between Asynchronous Mirroring and Log Shipping?

Thanks

Eric
|||

I have to disagree with Saleem on a few points.

Client Re-direction: Fully automatic as it uses .NET 2.0

It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes adavatage of mirroring if you tell it the alternate server in the connection string.

Failover: Automatic

Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds

Role Change: Role change is fully automatic

Failover is only automatic if you are running mirroring in High-Safety mode with a Witness server that is up and functioning and able to contact the mirroring partner.

I should also add that failing back to the original primary server is not supported. If you want mirroring to return to the original primary server, as soon as it becomes available, you have to set up a job to do it. I set up a job that runs every minute on the mirroring server that checks for databases that are the primary mirror and that are in the synchronized state (Where sys.database_mirroring.mirroring_state = 4 And sys.database_mirroring.mirroring_role = 1) and fail it over.

Another point about mirroring is that the official recommendation by MSFT is to only mirror a maximum of 10 databases per server because each mirroring session consumes 2 threads. Logshipping does not have this limitation.

Yet another good point about mirroring is that you can mirror the publisher of merge replication and configure replication to automatically use the alternate database if it fails over. This is designed to replace Alternate Synchronizing partners in replication as Alternate Synchronizing partners is being deprecated.

You should also take note that you can do both mirroring and log shipping.

|||Robert,

Thanks for the info but it does not really help me in choosing...

Can I phrase my question slightly differently...

I don't see how log shipping could have a serious negative effect on my primary server as it's pretty simple in principle...
Is there anyway that Asynchronous Mirroring could have a negative impact on my Primary Server?

Eric

|||

Hi Robert,

I will be setting up Mirroring in the next few days. I cam across this thread and saw your comments:

"It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes advantage of mirroring if you tell it the alternate server in the connection string."

Can you post sample code on connection string to do this? I believe our developers do not have this string comnnection option.

Another thing: "I should also add that failing back to the original primary server is not supported". Do you mind if you give me a bit of technique on how you do the job for failover to revert back to the primary node? This feature is new to me. I am not really into things like this. And one question, if the failover returns back to the primary server, do all transactions made in the mirrored database be automatically reflected on the primary?

Thanks!

Log Shipping vs database mirroring

Hi Mvps:

Whats the major difference between Database mirroring and Log shipping?. I did read few differences but was told when it comes to applying the transaction logs on the secondary server , DB Mirroring has a diffferent methodology. Is that right?. Please clarify.

Thanks

Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring supports only one miror for each principal database. Database mirroring also supports automatic failover if the principal database becomes unavailable. The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting, etc.

Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondaries for each primary database.

Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages:

1. it provides backup files as part of the process
2. multiple secondaries are supported
3. it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error

More information about both technologies is available in SQL Server 2005 Books Online in the topics "Understanding Log Shipping" and "Overview of Database Mirroring".

Phillip Garding
Senior Program Manager
Microsoft SQL Server

|||

Log shipping uses the tried and true restore logic.

Database mirroring uses a variation on the restore logic that is completely incorporated into the mirroring process. Plus redo is multi-threaded in Enterprise Edition.

|||

Hi Meher,

Other differences are listed below:

With Log Shipping:

Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required

With Database Mirroring:

Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred
Server Limitation: Can be applied to only one mirror server
Failover: Automatic
Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change: Role change is fully automatic
Client Re-direction: Fully automatic as it uses .NET 2.0

Hope this helps

Thank you,
Saleem Hakani
Saleem@.sqlnt.com

|||

thankx Saleem,

i would appreciate if you could help me. i am gloin to have my iterview in the comin weeks. so if you could

help me this problem i will b greatful... my email is is gibtronics2000@.gmail.com. The reply of yours is really helpful.

keep it up...

thankx

gangster

|||One important thing to keep in mind is that snapshots from db mirroring only works with Enterprise edition -- $$$ (especially if you need 2 or 3 copies of it). One last thing to consider is bi-directional transactional replication (this takes some thinking to have your schema support it correctly though).|||Saleem, Phillip

Phillip says "Mirroring is preferable to log shipping in most cases"...
Could I submit one case to you, please?

We have a web site for which we already have failover clustering but we want to setup some disaster recovery plan.
We will have cheaper hardware in a different location with exactly the same software and a "recent" copy of the database. We have connection between the two sites to keep our copy up-to-date.
A manual "switch" would be fine.

Although there is no plan for this, it might be useful to use the secondary database for reporting but I think this should be fine with both technologies (as we have Sql Server Enterprise Edition).

Could you advise between Asynchronous Mirroring and Log Shipping?

Thanks

Eric
|||

I have to disagree with Saleem on a few points.

Client Re-direction: Fully automatic as it uses .NET 2.0

It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes adavatage of mirroring if you tell it the alternate server in the connection string.

Failover: Automatic

Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds

Role Change: Role change is fully automatic

Failover is only automatic if you are running mirroring in High-Safety mode with a Witness server that is up and functioning and able to contact the mirroring partner.

I should also add that failing back to the original primary server is not supported. If you want mirroring to return to the original primary server, as soon as it becomes available, you have to set up a job to do it. I set up a job that runs every minute on the mirroring server that checks for databases that are the primary mirror and that are in the synchronized state (Where sys.database_mirroring.mirroring_state = 4 And sys.database_mirroring.mirroring_role = 1) and fail it over.

Another point about mirroring is that the official recommendation by MSFT is to only mirror a maximum of 10 databases per server because each mirroring session consumes 2 threads. Logshipping does not have this limitation.

Yet another good point about mirroring is that you can mirror the publisher of merge replication and configure replication to automatically use the alternate database if it fails over. This is designed to replace Alternate Synchronizing partners in replication as Alternate Synchronizing partners is being deprecated.

You should also take note that you can do both mirroring and log shipping.

|||Robert,

Thanks for the info but it does not really help me in choosing...

Can I phrase my question slightly differently...

I don't see how log shipping could have a serious negative effect on my primary server as it's pretty simple in principle...
Is there anyway that Asynchronous Mirroring could have a negative impact on my Primary Server?

Eric

|||

Hi Robert,

I will be setting up Mirroring in the next few days. I cam across this thread and saw your comments:

"It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes advantage of mirroring if you tell it the alternate server in the connection string."

Can you post sample code on connection string to do this? I believe our developers do not have this string comnnection option.

Another thing: "I should also add that failing back to the original primary server is not supported". Do you mind if you give me a bit of technique on how you do the job for failover to revert back to the primary node? This feature is new to me. I am not really into things like this. And one question, if the failover returns back to the primary server, do all transactions made in the mirrored database be automatically reflected on the primary?

Thanks!

Log Shipping vs database mirroring

Hi Mvps:

Whats the major difference between Database mirroring and Log shipping?. I did read few differences but was told when it comes to applying the transaction logs on the secondary server , DB Mirroring has a diffferent methodology. Is that right?. Please clarify.

Thanks

Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring supports only one miror for each principal database. Database mirroring also supports automatic failover if the principal database becomes unavailable. The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting, etc.

Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondaries for each primary database.

Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages:

1. it provides backup files as part of the process
2. multiple secondaries are supported
3. it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error

More information about both technologies is available in SQL Server 2005 Books Online in the topics "Understanding Log Shipping" and "Overview of Database Mirroring".

Phillip Garding
Senior Program Manager
Microsoft SQL Server

|||

Log shipping uses the tried and true restore logic.

Database mirroring uses a variation on the restore logic that is completely incorporated into the mirroring process. Plus redo is multi-threaded in Enterprise Edition.

|||

Hi Meher,

Other differences are listed below:

With Log Shipping:

Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required

With Database Mirroring:

Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred
Server Limitation: Can be applied to only one mirror server
Failover: Automatic
Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change: Role change is fully automatic
Client Re-direction: Fully automatic as it uses .NET 2.0

Hope this helps

Thank you,
Saleem Hakani
Saleem@.sqlnt.com

|||

thankx Saleem,

i would appreciate if you could help me. i am gloin to have my iterview in the comin weeks. so if you could

help me this problem i will b greatful... my email is is gibtronics2000@.gmail.com. The reply of yours is really helpful.

keep it up...

thankx

gangster

|||One important thing to keep in mind is that snapshots from db mirroring only works with Enterprise edition -- $$$ (especially if you need 2 or 3 copies of it). One last thing to consider is bi-directional transactional replication (this takes some thinking to have your schema support it correctly though).|||Saleem, Phillip

Phillip says "Mirroring is preferable to log shipping in most cases"...
Could I submit one case to you, please?

We have a web site for which we already have failover clustering but we want to setup some disaster recovery plan.
We will have cheaper hardware in a different location with exactly the same software and a "recent" copy of the database. We have connection between the two sites to keep our copy up-to-date.
A manual "switch" would be fine.

Although there is no plan for this, it might be useful to use the secondary database for reporting but I think this should be fine with both technologies (as we have Sql Server Enterprise Edition).

Could you advise between Asynchronous Mirroring and Log Shipping?

Thanks

Eric
|||

I have to disagree with Saleem on a few points.

Client Re-direction: Fully automatic as it uses .NET 2.0

It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes adavatage of mirroring if you tell it the alternate server in the connection string.

Failover: Automatic

Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds

Role Change: Role change is fully automatic

Failover is only automatic if you are running mirroring in High-Safety mode with a Witness server that is up and functioning and able to contact the mirroring partner.

I should also add that failing back to the original primary server is not supported. If you want mirroring to return to the original primary server, as soon as it becomes available, you have to set up a job to do it. I set up a job that runs every minute on the mirroring server that checks for databases that are the primary mirror and that are in the synchronized state (Where sys.database_mirroring.mirroring_state = 4 And sys.database_mirroring.mirroring_role = 1) and fail it over.

Another point about mirroring is that the official recommendation by MSFT is to only mirror a maximum of 10 databases per server because each mirroring session consumes 2 threads. Logshipping does not have this limitation.

Yet another good point about mirroring is that you can mirror the publisher of merge replication and configure replication to automatically use the alternate database if it fails over. This is designed to replace Alternate Synchronizing partners in replication as Alternate Synchronizing partners is being deprecated.

You should also take note that you can do both mirroring and log shipping.

|||Robert,

Thanks for the info but it does not really help me in choosing...

Can I phrase my question slightly differently...

I don't see how log shipping could have a serious negative effect on my primary server as it's pretty simple in principle...
Is there anyway that Asynchronous Mirroring could have a negative impact on my Primary Server?

Eric

|||

Hi Robert,

I will be setting up Mirroring in the next few days. I cam across this thread and saw your comments:

"It is only fully automatic IF the client app is using ADO.Net 2.0 (not all do) AND IF the developers have coded the connections correctly to take advantage of it. ADO.Net only takes advantage of mirroring if you tell it the alternate server in the connection string."

Can you post sample code on connection string to do this? I believe our developers do not have this string comnnection option.

Another thing: "I should also add that failing back to the original primary server is not supported". Do you mind if you give me a bit of technique on how you do the job for failover to revert back to the primary node? This feature is new to me. I am not really into things like this. And one question, if the failover returns back to the primary server, do all transactions made in the mirrored database be automatically reflected on the primary?

Thanks!

sql

Log Shipping v Database Mirroring (again!)

Hi

I'm looking at replicating our primary SQL server to a secondary offsite server (linked via 100Mb so effectively LAN speed). What are people's preference when deciding on a solution?

On the surface mirroring looks much better but having dug a little I've found it is recommended only 10 databases are mirrored per instance. That said, I've found a post from someone who is upto 58 databases mirrored. Are there similar limitations with log shipping?

Does anyone have any experience of mirroring and is using it in prefence to log shipping?

Our current recovery strategy is the classic restore the SQL dump from tape onto a rebuilt server so either method will be a vast improvement. None of our databases are mission critical that they need upto the second replication. 15 minute replication would be fine leading me to think that log shipping may be better given the possible limitations of mirroring

For recovery, I was considering amending the DNS records of the Database servers. Does anyone see any issues with this approach? I understand there is a automatic failure function if using mirror but this may require the application to be coded correctly?

Thanks in advance for any feedback

Robert

I also was greatly disappointed to learn that MS recommends only 10 mirrors per server instance. Considering that I have around 1300 databases in a federated model spread out over 12 servers, it threw a major monkey-wrench into our plans. So, now we are considering ranking clients on a scale and putting top level clients on DB Mirrors and 2nd level clients on log shipping. Demo db's for the various inernal departments get nothing (no soup for you!!).

Currently, I am still trying to figure out how many mirrors I can actually do on a server. I have 19 mirrors running on a 64 bit server with a 64 bit mirror with no noted performance degradation. Thread consumption did double (approximately) when I added the mirrors, but I'm still way below what's available (average of 100 threads consumed out of an available 512). However, I have to keep in mind that this is the slow part of the year for our clients and we have to leave enough threads avaialble to handle the increased load that will come at the end of the year.

I've suggested offering DB miroring as an upgrade that clients can purchase due to the increased hardware requirements. Log shipping = free, mirroring = $$$.

Yes, handling of the automatic failover does require some specific coding. The SQL Native Client and ADO.Net 2.0 have the ability to specify a failover partner in the connection string, and it automatically tries the failover partner if the principal server is not available. Earlier versions of ADO or other db access interfaces that do not support failover partners must be coded to manually try a second connection string if the first fails (which generally invovles a long timeout period). Another option is to build a middle tier that handles the transport of client db access so that the client isn't even aware of which server it is using.

If indeed you do not need the data to be up to the minute, then perhaps log shipping is the answer for you.

|||

Yes, I'm rapidly coming to the conclusion that 15 minute log shipping will be sufficient for our needs.

The majority of our database requirements are from 3rd party software. There are often very slow to update - many don't support SQL 2005 yet anyway!

|||

Robert,

I would be interested in any real world information you would like to share about mirroring. It would be good fodder for future articles at SSWUG.ORG as well as support to my company for implementing mirroring. They are concerned about the newness of this feature.

Cheers,

Ben

btaylor@.sswug.org

|||

As was stated above, we currently recommend no more than ten databases be configured for mirroring on a single instance. This is not a fixed limit, but due to resource consumption is a good general guideline. Some customers are perfectly happy with larger number of mirrored databases, however your mileage may vary depending on hardware, application, etc. Mirroring was not designed for large numbers of database on the same instance. If you are facing this scenario, MSCS is a more natural choice (with optional addition of storage level replication for disaster recovery).

Regards,

Matt Hollingsworth

Microsoft SQL Server

Log Shipping v Database Mirroring (again!)

Hi

I'm looking at replicating our primary SQL server to a secondary offsite server (linked via 100Mb so effectively LAN speed). What are people's preference when deciding on a solution?

On the surface mirroring looks much better but having dug a little I've found it is recommended only 10 databases are mirrored per instance. That said, I've found a post from someone who is upto 58 databases mirrored. Are there similar limitations with log shipping?

Does anyone have any experience of mirroring and is using it in prefence to log shipping?

Our current recovery strategy is the classic restore the SQL dump from tape onto a rebuilt server so either method will be a vast improvement. None of our databases are mission critical that they need upto the second replication. 15 minute replication would be fine leading me to think that log shipping may be better given the possible limitations of mirroring

For recovery, I was considering amending the DNS records of the Database servers. Does anyone see any issues with this approach? I understand there is a automatic failure function if using mirror but this may require the application to be coded correctly?

Thanks in advance for any feedback

Robert

I also was greatly disappointed to learn that MS recommends only 10 mirrors per server instance. Considering that I have around 1300 databases in a federated model spread out over 12 servers, it threw a major monkey-wrench into our plans. So, now we are considering ranking clients on a scale and putting top level clients on DB Mirrors and 2nd level clients on log shipping. Demo db's for the various inernal departments get nothing (no soup for you!!).

Currently, I am still trying to figure out how many mirrors I can actually do on a server. I have 19 mirrors running on a 64 bit server with a 64 bit mirror with no noted performance degradation. Thread consumption did double (approximately) when I added the mirrors, but I'm still way below what's available (average of 100 threads consumed out of an available 512). However, I have to keep in mind that this is the slow part of the year for our clients and we have to leave enough threads avaialble to handle the increased load that will come at the end of the year.

I've suggested offering DB miroring as an upgrade that clients can purchase due to the increased hardware requirements. Log shipping = free, mirroring = $$$.

Yes, handling of the automatic failover does require some specific coding. The SQL Native Client and ADO.Net 2.0 have the ability to specify a failover partner in the connection string, and it automatically tries the failover partner if the principal server is not available. Earlier versions of ADO or other db access interfaces that do not support failover partners must be coded to manually try a second connection string if the first fails (which generally invovles a long timeout period). Another option is to build a middle tier that handles the transport of client db access so that the client isn't even aware of which server it is using.

If indeed you do not need the data to be up to the minute, then perhaps log shipping is the answer for you.

|||

Yes, I'm rapidly coming to the conclusion that 15 minute log shipping will be sufficient for our needs.

The majority of our database requirements are from 3rd party software. There are often very slow to update - many don't support SQL 2005 yet anyway!

|||

Robert,

I would be interested in any real world information you would like to share about mirroring. It would be good fodder for future articles at SSWUG.ORG as well as support to my company for implementing mirroring. They are concerned about the newness of this feature.

Cheers,

Ben

btaylor@.sswug.org

|||

As was stated above, we currently recommend no more than ten databases be configured for mirroring on a single instance. This is not a fixed limit, but due to resource consumption is a good general guideline. Some customers are perfectly happy with larger number of mirrored databases, however your mileage may vary depending on hardware, application, etc. Mirroring was not designed for large numbers of database on the same instance. If you are facing this scenario, MSCS is a more natural choice (with optional addition of storage level replication for disaster recovery).

Regards,

Matt Hollingsworth

Microsoft SQL Server

Log shipping best method? How to initiate an existing plan?

My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.

I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?

Thanks in advance!

Rollin

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.

Each technology has its own place and usage. Your requirements will determine what is the best method to use.

|||

Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.

Is there an area that discusses this for SQL 2000?

Thank you,

Rollin

|||

The following link provides more information specific to SQL 2000.

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx

But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.

As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.

1. Failover clustering

2. Log shipping

3. Transactional replication

4. Backup / Restore.

There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.

I hope this helps.

Thanks,

Mark

|||

Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.

Thanks again, Rollin

|||Can I log ship from a 2000 server toi a 2005 server?|||

This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.

It is not supported through the normal log shipping stored procedures.

But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.

Does this make sense?

Thanks,

Mark

|||

I don't know if this is still active, but...

Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?

Thanks,

Jason

Log shipping best method? How to initiate an existing plan?

My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.

I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?

Thanks in advance!

Rollin

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.

Each technology has its own place and usage. Your requirements will determine what is the best method to use.

|||

Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.

Is there an area that discusses this for SQL 2000?

Thank you,

Rollin

|||

The following link provides more information specific to SQL 2000.

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx

But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.

As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.

1. Failover clustering

2. Log shipping

3. Transactional replication

4. Backup / Restore.

There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.

I hope this helps.

Thanks,

Mark

|||

Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.

Thanks again, Rollin

|||Can I log ship from a 2000 server toi a 2005 server?|||

This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.

It is not supported through the normal log shipping stored procedures.

But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.

Does this make sense?

Thanks,

Mark

|||

I don't know if this is still active, but...

Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?

Thanks,

Jason

Log shipping best method? How to initiate an existing plan?

My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.

I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?

Thanks in advance!

Rollin

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.

Each technology has its own place and usage. Your requirements will determine what is the best method to use.

|||

Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.

Is there an area that discusses this for SQL 2000?

Thank you,

Rollin

|||

The following link provides more information specific to SQL 2000.

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx

But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.

As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.

1. Failover clustering

2. Log shipping

3. Transactional replication

4. Backup / Restore.

There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.

I hope this helps.

Thanks,

Mark

|||

Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.

Thanks again, Rollin

|||Can I log ship from a 2000 server toi a 2005 server?|||

This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.

It is not supported through the normal log shipping stored procedures.

But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.

Does this make sense?

Thanks,

Mark

|||

I don't know if this is still active, but...

Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?

Thanks,

Jason

Log shipping best method? How to initiate an existing plan?

My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.

I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?

Thanks in advance!

Rollin

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.

Each technology has its own place and usage. Your requirements will determine what is the best method to use.

|||

Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.

Is there an area that discusses this for SQL 2000?

Thank you,

Rollin

|||

The following link provides more information specific to SQL 2000.

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx

But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.

As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.

1. Failover clustering

2. Log shipping

3. Transactional replication

4. Backup / Restore.

There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.

I hope this helps.

Thanks,

Mark

|||

Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.

Thanks again, Rollin

|||Can I log ship from a 2000 server toi a 2005 server?|||

This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.

It is not supported through the normal log shipping stored procedures.

But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.

Does this make sense?

Thanks,

Mark

|||

I don't know if this is still active, but...

Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?

Thanks,

Jason

Log Shipping AND Database Mirroring ?

We are in the planning stages of setting up a redundant failover SQL
environment and were wondering if it's possible to integrate Database
Mirroring for some our databases (<10) and log shipping for the rest of our
databases.
All DB's are on a single instance of SQL
Thanks
Nick
Answered my own question.
http://msdn2.microsoft.com/en-us/library/ms187016.aspx
"N." <larosan@.yahoo.com> wrote in message
news:6Nznh.158$cK.82@.newsfe11.lga...
> We are in the planning stages of setting up a redundant failover SQL
> environment and were wondering if it's possible to integrate Database
> Mirroring for some our databases (<10) and log shipping for the rest of
> our databases.
> All DB's are on a single instance of SQL
> Thanks
> Nick
>
>
>