Database Mirroring vs SQL Server FailOver (Performance)

Hi there.

I′m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.

The tests i′m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.

My question here is: What if i use Sql Server failover mechanism?

I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?

Database mirroring performance will vary depending on many factors. I assume you have been using Mirrroring in high protection mode which ensures all writes are synchronously made to both databases. This has a larger impact on throughput than the high performance asynchronous mode. The failover mechanism should not have any impact on the performance of you database workload since it's basic purpose is to re-direct the connection to the mirror database when the principal fails.

|||

I think you missunderstood me. My question isn′t very clear, sorry for that.

I′m using High Availability mode (with witness).

I want to know if, in fact, i should expect better performance when using SQL Server Failover Clustering(with shared disk array).

Thanks in advance.

|||

If you are asking if you are getting better performance (throughput) using database mirroring or Failover clustering, then the answer is Failover clustering will have better peformance.

In fact, the cluster performance should be about the same (if not exactly the same) as a stand alone server.

Failover times are another matter, though.

|||

I also was thinking that cluster performance would be the same, when comparing to a stand alone server. But i′m not very deep into this mechanism and needed an answer from someone with "know how" in the area.

Thanks for your help Mark.

|||Database mirroring is useful technology for disaster scope. You can have two failover clusters in two geographics positions and database mirroring help you data be in consistent state (sorry for my english :( )|||

To be clear, failover times for the database mirroring can be in the seconds, because the instance of SQL is up and running and constantly redoing as much it can.

For failover clustering, for a failover to happen, the instance on the new node has to start up and then run recovery on the database before it is opened. This generally takes 10s of seconds and even could take place on the order of minutes.

Thanks,

Mark

|||

Fail-over clustering will perform better than database mirroring (in synchronous mode), since the Principal has to wait for the Mirror to commit transactions. This makes you dependent on the bandwidth and latency of the network connection between the two databases, and on the disk IO on the Mirror.

DB Mirroring will fail-over much faster than fail-over clustering, so it is better for routine maintence.

Database Mirroring vs SQL Server FailOver (Performance)

Hi there.

I′m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.

The tests i′m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.

My question here is: What if i use Sql Server failover mechanism?

I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?

Database mirroring performance will vary depending on many factors. I assume you have been using Mirrroring in high protection mode which ensures all writes are synchronously made to both databases. This has a larger impact on throughput than the high performance asynchronous mode. The failover mechanism should not have any impact on the performance of you database workload since it's basic purpose is to re-direct the connection to the mirror database when the principal fails.

|||

I think you missunderstood me. My question isn′t very clear, sorry for that.

I′m using High Availability mode (with witness).

I want to know if, in fact, i should expect better performance when using SQL Server Failover Clustering(with shared disk array).

Thanks in advance.

|||

If you are asking if you are getting better performance (throughput) using database mirroring or Failover clustering, then the answer is Failover clustering will have better peformance.

In fact, the cluster performance should be about the same (if not exactly the same) as a stand alone server.

Failover times are another matter, though.

|||

I also was thinking that cluster performance would be the same, when comparing to a stand alone server. But i′m not very deep into this mechanism and needed an answer from someone with "know how" in the area.

Thanks for your help Mark.

|||Database mirroring is useful technology for disaster scope. You can have two failover clusters in two geographics positions and database mirroring help you data be in consistent state (sorry for my english :( )|||

To be clear, failover times for the database mirroring can be in the seconds, because the instance of SQL is up and running and constantly redoing as much it can.

For failover clustering, for a failover to happen, the instance on the new node has to start up and then run recovery on the database before it is opened. This generally takes 10s of seconds and even could take place on the order of minutes.

Thanks,

Mark

|||

Fail-over clustering will perform better than database mirroring (in synchronous mode), since the Principal has to wait for the Mirror to commit transactions. This makes you dependent on the bandwidth and latency of the network connection between the two databases, and on the disk IO on the Mirror.

DB Mirroring will fail-over much faster than fail-over clustering, so it is better for routine maintence.

Database Mirroring vs SQL Server FailOver (Performance)

Hi there.

I′m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.

The tests i′m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.

My question here is: What if i use Sql Server failover mechanism?

I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?

Database mirroring performance will vary depending on many factors. I assume you have been using Mirrroring in high protection mode which ensures all writes are synchronously made to both databases. This has a larger impact on throughput than the high performance asynchronous mode. The failover mechanism should not have any impact on the performance of you database workload since it's basic purpose is to re-direct the connection to the mirror database when the principal fails.

|||

I think you missunderstood me. My question isn′t very clear, sorry for that.

I′m using High Availability mode (with witness).

I want to know if, in fact, i should expect better performance when using SQL Server Failover Clustering(with shared disk array).

Thanks in advance.

|||

If you are asking if you are getting better performance (throughput) using database mirroring or Failover clustering, then the answer is Failover clustering will have better peformance.

In fact, the cluster performance should be about the same (if not exactly the same) as a stand alone server.

Failover times are another matter, though.

|||

I also was thinking that cluster performance would be the same, when comparing to a stand alone server. But i′m not very deep into this mechanism and needed an answer from someone with "know how" in the area.

Thanks for your help Mark.

|||Database mirroring is useful technology for disaster scope. You can have two failover clusters in two geographics positions and database mirroring help you data be in consistent state (sorry for my english :( )|||

To be clear, failover times for the database mirroring can be in the seconds, because the instance of SQL is up and running and constantly redoing as much it can.

For failover clustering, for a failover to happen, the instance on the new node has to start up and then run recovery on the database before it is opened. This generally takes 10s of seconds and even could take place on the order of minutes.

Thanks,

Mark

|||

Fail-over clustering will perform better than database mirroring (in synchronous mode), since the Principal has to wait for the Mirror to commit transactions. This makes you dependent on the bandwidth and latency of the network connection between the two databases, and on the disk IO on the Mirror.

DB Mirroring will fail-over much faster than fail-over clustering, so it is better for routine maintence.

database mirroring vs log shipping

what could be the difference database mirroring vs log shipping in terms of
redundancy and performance.
thanks.Hi Chris
"chris" wrote:
> what could be the difference database mirroring vs log shipping in terms of
> redundancy and performance.
> thanks.
I believe the difference is mainly in the latency, with database mirroring
providing a warmer standby than log shipping! Mirroring provides a method of
automatic failover to a single mirrored database, log shipping can service
multiple standby databases and requires manual intervention to make it live,
see
http://msdn2.microsoft.com/en-us/library/ms190202.aspx and
http://msdn2.microsoft.com/en-us/library/ms187016.aspx
John

Database Maintenance Plans and shrink database

I have read that having databases auto shrink can cause performance
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
ThanksYes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>

Database Maintenance on a SQL 7 Database

I have a client that I just took on who is running SQL 7. Their database
isn't too big - about 400mb. They are telling me that the performance isn't
nearly as good as it once was. They have been using this database for about 6
years now. I don't believe they have been doing any kind of maintenance on
the database except for backing it up. Can anyone tell me what I should be
doing to this database on a routine basis? Also, they tell me that it is
mainly saving data that takes a very long time. Could this be due to the fact
that maintenance hasn't been performed on this database in it's entire
existence? Thank you.Hi Jeff
You could look at using a maintenance plan and go through the wizard to
specify your index maintenance, or you could write your own more targetted
process using DBCC SHOWCONTIG, DBCC INDEXDEFRAG and DBCC DBREINDEX the
action you take depending on the level of fragmentation reported (you can use
the example in SQL 2000 books online DBCC SHOWCONTIG as the basis for such a
process).
John
"Jeff" wrote:
> I have a client that I just took on who is running SQL 7. Their database
> isn't too big - about 400mb. They are telling me that the performance isn't
> nearly as good as it once was. They have been using this database for about 6
> years now. I don't believe they have been doing any kind of maintenance on
> the database except for backing it up. Can anyone tell me what I should be
> doing to this database on a routine basis? Also, they tell me that it is
> mainly saving data that takes a very long time. Could this be due to the fact
> that maintenance hasn't been performed on this database in it's entire
> existence? Thank you.

Database Maintenance on a SQL 7 Database

I have a client that I just took on who is running SQL 7. Their database
isn't too big - about 400mb. They are telling me that the performance isn't
nearly as good as it once was. They have been using this database for about 6
years now. I don't believe they have been doing any kind of maintenance on
the database except for backing it up. Can anyone tell me what I should be
doing to this database on a routine basis? Also, they tell me that it is
mainly saving data that takes a very long time. Could this be due to the fact
that maintenance hasn't been performed on this database in it's entire
existence? Thank you.
Hi Jeff
You could look at using a maintenance plan and go through the wizard to
specify your index maintenance, or you could write your own more targetted
process using DBCC SHOWCONTIG, DBCC INDEXDEFRAG and DBCC DBREINDEX the
action you take depending on the level of fragmentation reported (you can use
the example in SQL 2000 books online DBCC SHOWCONTIG as the basis for such a
process).
John
"Jeff" wrote:

> I have a client that I just took on who is running SQL 7. Their database
> isn't too big - about 400mb. They are telling me that the performance isn't
> nearly as good as it once was. They have been using this database for about 6
> years now. I don't believe they have been doing any kind of maintenance on
> the database except for backing it up. Can anyone tell me what I should be
> doing to this database on a routine basis? Also, they tell me that it is
> mainly saving data that takes a very long time. Could this be due to the fact
> that maintenance hasn't been performed on this database in it's entire
> existence? Thank you.

Database Maintenance on a SQL 7 Database

I have a client that I just took on who is running SQL 7. Their database
isn't too big - about 400mb. They are telling me that the performance isn't
nearly as good as it once was. They have been using this database for about
6
years now. I don't believe they have been doing any kind of maintenance on
the database except for backing it up. Can anyone tell me what I should be
doing to this database on a routine basis? Also, they tell me that it is
mainly saving data that takes a very long time. Could this be due to the fac
t
that maintenance hasn't been performed on this database in it's entire
existence? Thank you.Hi Jeff
You could look at using a maintenance plan and go through the wizard to
specify your index maintenance, or you could write your own more targetted
process using DBCC SHOWCONTIG, DBCC INDEXDEFRAG and DBCC DBREINDEX the
action you take depending on the level of fragmentation reported (you can us
e
the example in SQL 2000 books online DBCC SHOWCONTIG as the basis for such a
process).
John
"Jeff" wrote:

> I have a client that I just took on who is running SQL 7. Their database
> isn't too big - about 400mb. They are telling me that the performance isn'
t
> nearly as good as it once was. They have been using this database for abou
t 6
> years now. I don't believe they have been doing any kind of maintenance on
> the database except for backing it up. Can anyone tell me what I should be
> doing to this database on a routine basis? Also, they tell me that it is
> mainly saving data that takes a very long time. Could this be due to the f
act
> that maintenance hasn't been performed on this database in it's entire
> existence? Thank you.