High Performance in Mirror

In this mode, I get that it's asynchronous. Does that just mean it sends the TX over and what happens, happens? Do I at least know it hit the comms stack, or the wire or anything? Or just it will do its est. Thanks.

You are referring to the mode when transaction safety is OFF. In this case, the principal sends the log to the mirror but does not wait for acknowledgement from the mirror. So Mirror can lag the Principal under heavy load. However, if mirror is down or if Principal is not receiving the response to the periodic ping (i.e. communication stack has a problem), the principal takes database offline.

Thanks,

|||When you say 'the principal takes database offline' you mean the MIRROR right, not the Principal database which could still live and service TXs, right?

High Availability over Distance

Hi,
I am looking for suggestions on creating a mirror site. Currently we have a
SQL server in a MS Cluster Environment. We will be creating another
clustered environment which will be 5+ miles away from the primary site.
What is the best way to create a mirror of the primary site?
I am looking for the Best option available (MS or third party). Currently I
am looking at Double Take and Replistor. We have sued Replistor in the past
with some good and very bad experiences. Primary goal is to make sure
secondary site is always up to date (within a minute or so). Money is not an
object (within reason, No Million $ options please :-) )
Thank you.
Hi Henrik,
Just be careful at using the high availabilty mode when the servers
(partners) are not on the same data center and pay special attention to
network reliability, network throughput and performance.
Take a look at this paper
Database Mirroring Best Practices and Performance Considerations
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Henrik Davidsen" wrote:

> Try to read up on SQL Server's Mirroring feature.
>
> There are different modes the mirroring can operate in. One is a High
> Availability mode, whichs offers guarantee that the mirror is ALWAYS 100%
> up-to-date. That however produces some performance degrading. But read up on
> the mirroring
>
> The mirrorring funktionality is available within SQL Server 2005, so no
> extra expenses is nessecary.
> /Sjang
>
>
|||Thanks Guys. I will look into mirroring for sure.
What about any other options?
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:25119072-74B9-4C84-BE55-042AFF85F735@.microsoft.com...[vbcol=seagreen]
> Hi Henrik,
> Just be careful at using the high availabilty mode when the servers
> (partners) are not on the same data center and pay special attention to
> network reliability, network throughput and performance.
> Take a look at this paper
> Database Mirroring Best Practices and Performance Considerations
> http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Henrik Davidsen" wrote:
|||There are technologies available from SAN vendors. Although they probably
have million dollar + price tags. Call up EMC and tell them what you want to
do.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Dragon" <noSpam_baadil@.hotmail.com> wrote in message
news:unDj%23b4NIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Thanks Guys. I will look into mirroring for sure.
> What about any other options?
> Thanks.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:25119072-74B9-4C84-BE55-042AFF85F735@.microsoft.com...
>
|||Thank you Jason.
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:72BEA593-F945-4A39-B413-E25B118AFA7B@.microsoft.com...
> There are technologies available from SAN vendors. Although they probably
> have million dollar + price tags. Call up EMC and tell them what you want
> to do.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "Dragon" <noSpam_baadil@.hotmail.com> wrote in message
> news:unDj%23b4NIHA.4656@.TK2MSFTNGP03.phx.gbl...
>

High Availability over Distance

Hi,
I am looking for suggestions on creating a mirror site. Currently we have a
SQL server in a MS Cluster Environment. We will be creating another
clustered environment which will be 5+ miles away from the primary site.
What is the best way to create a mirror of the primary site?
I am looking for the Best option available (MS or third party). Currently I
am looking at Double Take and Replistor. We have sued Replistor in the past
with some good and very bad experiences. Primary goal is to make sure
secondary site is always up to date (within a minute or so). Money is not an
object (within reason, No Million $ options please :-) )
Thank you.> I am looking for suggestions on creating a mirror site.
Try to read up on SQL Server's Mirroring feature.

> Primary goal is to make sure secondary site is always up to date (within a
> minute or so).
There are different modes the mirroring can operate in. One is a High
Availability mode, whichs offers guarantee that the mirror is ALWAYS 100%
up-to-date. That however produces some performance degrading. But read up on
the mirroring

> Money is not an object (within reason, No Million $ options please :-) )
The mirrorring funktionality is available within SQL Server 2005, so no
extra expenses is nessecary.
/Sjang|||Hi Henrik,
Just be careful at using the high availabilty mode when the servers
(partners) are not on the same data center and pay special attention to
network reliability, network throughput and performance.
Take a look at this paper
Database Mirroring Best Practices and Performance Considerations
http://www.microsoft.com/technet/pr...ct.
mspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Henrik Davidsen" wrote:

> Try to read up on SQL Server's Mirroring feature.
>
> There are different modes the mirroring can operate in. One is a High
> Availability mode, whichs offers guarantee that the mirror is ALWAYS 100%
> up-to-date. That however produces some performance degrading. But read up
on
> the mirroring
>
> The mirrorring funktionality is available within SQL Server 2005, so no
> extra expenses is nessecary.
> /Sjang
>
>|||Thanks Guys. I will look into mirroring for sure.
What about any other options?
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:25119072-74B9-4C84-BE55-042AFF85F735@.microsoft.com...[vbcol=seagreen]
> Hi Henrik,
> Just be careful at using the high availabilty mode when the servers
> (partners) are not on the same data center and pay special attention to
> network reliability, network throughput and performance.
> Take a look at this paper
> Database Mirroring Best Practices and Performance Considerations
> http://www.microsoft.com/technet/pr...c
t.mspx
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Henrik Davidsen" wrote:
>|||There are technologies available from SAN vendors. Although they probably
have million dollar + price tags. Call up EMC and tell them what you want to
do.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Dragon" <noSpam_baadil@.hotmail.com> wrote in message
news:unDj%23b4NIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Thanks Guys. I will look into mirroring for sure.
> What about any other options?
> Thanks.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:25119072-74B9-4C84-BE55-042AFF85F735@.microsoft.com...
>|||Thank you Jason.
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:72BEA593-F945-4A39-B413-E25B118AFA7B@.microsoft.com...
> There are technologies available from SAN vendors. Although they probably
> have million dollar + price tags. Call up EMC and tell them what you want
> to do.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "Dragon" <noSpam_baadil@.hotmail.com> wrote in message
> news:unDj%23b4NIHA.4656@.TK2MSFTNGP03.phx.gbl...
>

High Availability over Distance

Hi,
I am looking for suggestions on creating a mirror site. Currently we have a
SQL server in a MS Cluster Environment. We will be creating another
clustered environment which will be 5+ miles away from the primary site.
What is the best way to create a mirror of the primary site?
I am looking for the Best option available (MS or third party). Currently I
am looking at Double Take and Replistor. We have sued Replistor in the past
with some good and very bad experiences. Primary goal is to make sure
secondary site is always up to date (within a minute or so). Money is not an
object (within reason, No Million $ options please :-) )
Thank you.> I am looking for suggestions on creating a mirror site.
Try to read up on SQL Server's Mirroring feature.
> Primary goal is to make sure secondary site is always up to date (within a
> minute or so).
There are different modes the mirroring can operate in. One is a High
Availability mode, whichs offers guarantee that the mirror is ALWAYS 100%
up-to-date. That however produces some performance degrading. But read up on
the mirroring :)
> Money is not an object (within reason, No Million $ options please :-) )
The mirrorring funktionality is available within SQL Server 2005, so no
extra expenses is nessecary.
/Sjang|||Hi Henrik,
Just be careful at using the high availabilty mode when the servers
(partners) are not on the same data center and pay special attention to
network reliability, network throughput and performance.
Take a look at this paper
Database Mirroring Best Practices and Performance Considerations
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Henrik Davidsen" wrote:
> > I am looking for suggestions on creating a mirror site.
> Try to read up on SQL Server's Mirroring feature.
> > Primary goal is to make sure secondary site is always up to date (within a
> > minute or so).
> There are different modes the mirroring can operate in. One is a High
> Availability mode, whichs offers guarantee that the mirror is ALWAYS 100%
> up-to-date. That however produces some performance degrading. But read up on
> the mirroring :)
> > Money is not an object (within reason, No Million $ options please :-) )
> The mirrorring funktionality is available within SQL Server 2005, so no
> extra expenses is nessecary.
> /Sjang
>
>|||Thanks Guys. I will look into mirroring for sure.
What about any other options?
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:25119072-74B9-4C84-BE55-042AFF85F735@.microsoft.com...
> Hi Henrik,
> Just be careful at using the high availabilty mode when the servers
> (partners) are not on the same data center and pay special attention to
> network reliability, network throughput and performance.
> Take a look at this paper
> Database Mirroring Best Practices and Performance Considerations
> http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Henrik Davidsen" wrote:
>> > I am looking for suggestions on creating a mirror site.
>> Try to read up on SQL Server's Mirroring feature.
>> > Primary goal is to make sure secondary site is always up to date
>> > (within a
>> > minute or so).
>> There are different modes the mirroring can operate in. One is a High
>> Availability mode, whichs offers guarantee that the mirror is ALWAYS 100%
>> up-to-date. That however produces some performance degrading. But read up
>> on
>> the mirroring :)
>> > Money is not an object (within reason, No Million $ options please
>> > :-) )
>> The mirrorring funktionality is available within SQL Server 2005, so no
>> extra expenses is nessecary.
>> /Sjang
>>|||There are technologies available from SAN vendors. Although they probably
have million dollar + price tags. Call up EMC and tell them what you want to
do.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Dragon" <noSpam_baadil@.hotmail.com> wrote in message
news:unDj%23b4NIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Thanks Guys. I will look into mirroring for sure.
> What about any other options?
> Thanks.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:25119072-74B9-4C84-BE55-042AFF85F735@.microsoft.com...
>> Hi Henrik,
>> Just be careful at using the high availabilty mode when the servers
>> (partners) are not on the same data center and pay special attention to
>> network reliability, network throughput and performance.
>> Take a look at this paper
>> Database Mirroring Best Practices and Performance Considerations
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
>> Hope this helps,
>> Ben Nevarez
>> Senior Database Administrator
>> AIG SunAmerica
>>
>> "Henrik Davidsen" wrote:
>> > I am looking for suggestions on creating a mirror site.
>> Try to read up on SQL Server's Mirroring feature.
>> > Primary goal is to make sure secondary site is always up to date
>> > (within a
>> > minute or so).
>> There are different modes the mirroring can operate in. One is a High
>> Availability mode, whichs offers guarantee that the mirror is ALWAYS
>> 100%
>> up-to-date. That however produces some performance degrading. But read
>> up on
>> the mirroring :)
>> > Money is not an object (within reason, No Million $ options please
>> > :-) )
>> The mirrorring funktionality is available within SQL Server 2005, so no
>> extra expenses is nessecary.
>> /Sjang
>>
>|||Thank you Jason.
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:72BEA593-F945-4A39-B413-E25B118AFA7B@.microsoft.com...
> There are technologies available from SAN vendors. Although they probably
> have million dollar + price tags. Call up EMC and tell them what you want
> to do.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "Dragon" <noSpam_baadil@.hotmail.com> wrote in message
> news:unDj%23b4NIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Thanks Guys. I will look into mirroring for sure.
>> What about any other options?
>> Thanks.
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:25119072-74B9-4C84-BE55-042AFF85F735@.microsoft.com...
>> Hi Henrik,
>> Just be careful at using the high availabilty mode when the servers
>> (partners) are not on the same data center and pay special attention to
>> network reliability, network throughput and performance.
>> Take a look at this paper
>> Database Mirroring Best Practices and Performance Considerations
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
>> Hope this helps,
>> Ben Nevarez
>> Senior Database Administrator
>> AIG SunAmerica
>>
>> "Henrik Davidsen" wrote:
>> > I am looking for suggestions on creating a mirror site.
>> Try to read up on SQL Server's Mirroring feature.
>> > Primary goal is to make sure secondary site is always up to date
>> > (within a
>> > minute or so).
>> There are different modes the mirroring can operate in. One is a High
>> Availability mode, whichs offers guarantee that the mirror is ALWAYS
>> 100%
>> up-to-date. That however produces some performance degrading. But read
>> up on
>> the mirroring :)
>> > Money is not an object (within reason, No Million $ options please
>> > :-) )
>> The mirrorring funktionality is available within SQL Server 2005, so no
>> extra expenses is nessecary.
>> /Sjang
>>
>>
>

High "Disk Write Bytes/sec" in Mirror server

I have do some benchmark test between the "Principle", "Mirror" and "Witness", things seems normal, the System usage (CPU, Disk Read/Write) on the "Witness" is very small and the CPU usage on Principle and Mirror is similar, However....

For the "Disk Write Bytes/sec", the usage on Principle is 200,000 and the Mirror is 1,500,000, the mirror server is talking about x7 times Disk Write Bytes/sec on the Mirror.

I cannot think of any reason why the Disk Write Bytes /sec is so much higher than the Principle, can somebody help me?

Hi Benny,

Just curious - is the mirrored database the only thing that's running on the mirror machine, or you may have other workloads there?

Could you please run the sp_dbmmonitorresults stored proc on the principal and the mirror, and check whether the send and receive queues show something reasonable?

-- Kaloian.

|||

Hi Kaloian, in the mirror server, I find 19 row but only 1 row in the principle server, is it normal?

According to the result of sp_dbmmonitorresults, I find that the transaction delay on the mirror server is quite long. We are using hardware RAID-5 on the Principle server and normal disk (no RAID) on mirror server, will it increase the write byte /sec, because of the slower in "disk" speed, should be make it lower, am I right?

Thank you very much for your help.

This are the result for your reference:

On Principle Server:

all the counter are "0"

On Mirror Server:

database name	role	mirroring_state	witness_status	log_generation_rate	unsent_log	send_rate	unrestored_log	recovery_rate	transaction_delay	transactions_per_sec	average_delay	time_recorded	Time_behind	local_time
Mirror2	1	4	2	71	0	71	0	0	267	44	6	30:00.7	29:00.1	30:00.7
Mirror2	1	4	1	83	0	83	0	0	324	50	6	29:00.1	29:00.1	29:00.1
Mirror2	1	4	1	74	0	74	0	0	397	42	9	28:01.0	28:01.0	28:01.0
Mirror2	1	4	1	0	0	0	0	0	0	1	0	27:00.8	27:00.8	27:00.8
Mirror2	1	4	1	0	0	0	0	0	0	1	0	26:00.7	26:00.7	26:00.7
Mirror2	1	4	1	0	0	0	0	0	0	1	0	25:00.5	25:00.5	25:00.5
Mirror2	1	4	1	0	0	0	0	0	0	1	0	24:00.4	24:00.4	24:00.4
Mirror2	1	4	1	0	0	0	0	0	0	1	0	23:00.2	23:00.2	23:00.2
Mirror2	1	4	1	0	0	0	0	0	0	1	0	22:00.1	22:00.1	22:00.1
Mirror2	1	4	1	0	0	0	0	0	0	1	0	21:00.9	21:00.9	21:00.9
Mirror2	1	4	1	0	0	0	0	0	0	1	0	20:00.8	20:00.8	20:00.8
Mirror2	1	4	1	7	0	7	0	0	8	2	4	19:00.6	19:00.6	19:00.6
Mirror2	1	4	1	0	0	0	0	0	2	1	2	18:00.5	18:00.5	18:00.5
Mirror2	1	4	1	0	0	0	0	0	0	1	0	17:00.3	17:00.3	17:00.3
Mirror2	1	4	1	0	0	0	0	0	0	1	0	16:00.2	16:00.2	16:00.2
Mirror2	1	4	1	0	0	0	0	0	0	0	0	15:40.2	15:40.2	15:40.2
Mirror2	1	2	1	0	0	0	0	0	0	0	0	44:00.1	15:40.2	44:00.1
Mirror2	1	2	1	0	0	0	0	0	0	0	0	43:00.1	15:40.2	43:00.1
Mirror2	1	2	1	0	0	0	0	0	0	0	0	42:00.1	15:40.2	42:00.1

High "Disk Write Bytes/sec" in Mirror server

I have do some benchmark test between the "Principle", "Mirror" and "Witness", things seems normal, the System usage (CPU, Disk Read/Write) on the "Witness" is very small and the CPU usage on Principle and Mirror is similar, However....

For the "Disk Write Bytes/sec", the usage on Principle is 200,000 and the Mirror is 1,500,000, the mirror server is talking about x7 times Disk Write Bytes/sec on the Mirror.

I cannot think of any reason why the Disk Write Bytes /sec is so much higher than the Principle, can somebody help me?

Hi Benny,

Just curious - is the mirrored database the only thing that's running on the mirror machine, or you may have other workloads there?

Could you please run the sp_dbmmonitorresults stored proc on the principal and the mirror, and check whether the send and receive queues show something reasonable?

-- Kaloian.

|||

Hi Kaloian, in the mirror server, I find 19 row but only 1 row in the principle server, is it normal?

According to the result of sp_dbmmonitorresults, I find that the transaction delay on the mirror server is quite long. We are using hardware RAID-5 on the Principle server and normal disk (no RAID) on mirror server, will it increase the write byte /sec, because of the slower in "disk" speed, should be make it lower, am I right?

Thank you very much for your help.

This are the result for your reference:

On Principle Server:

all the counter are "0"

On Mirror Server:

database name	role	mirroring_state	witness_status	log_generation_rate	unsent_log	send_rate	unrestored_log	recovery_rate	transaction_delay	transactions_per_sec	average_delay	time_recorded	Time_behind	local_time
Mirror2	1	4	2	71	0	71	0	0	267	44	6	30:00.7	29:00.1	30:00.7
Mirror2	1	4	1	83	0	83	0	0	324	50	6	29:00.1	29:00.1	29:00.1
Mirror2	1	4	1	74	0	74	0	0	397	42	9	28:01.0	28:01.0	28:01.0
Mirror2	1	4	1	0	0	0	0	0	0	1	0	27:00.8	27:00.8	27:00.8
Mirror2	1	4	1	0	0	0	0	0	0	1	0	26:00.7	26:00.7	26:00.7
Mirror2	1	4	1	0	0	0	0	0	0	1	0	25:00.5	25:00.5	25:00.5
Mirror2	1	4	1	0	0	0	0	0	0	1	0	24:00.4	24:00.4	24:00.4
Mirror2	1	4	1	0	0	0	0	0	0	1	0	23:00.2	23:00.2	23:00.2
Mirror2	1	4	1	0	0	0	0	0	0	1	0	22:00.1	22:00.1	22:00.1
Mirror2	1	4	1	0	0	0	0	0	0	1	0	21:00.9	21:00.9	21:00.9
Mirror2	1	4	1	0	0	0	0	0	0	1	0	20:00.8	20:00.8	20:00.8
Mirror2	1	4	1	7	0	7	0	0	8	2	4	19:00.6	19:00.6	19:00.6
Mirror2	1	4	1	0	0	0	0	0	2	1	2	18:00.5	18:00.5	18:00.5
Mirror2	1	4	1	0	0	0	0	0	0	1	0	17:00.3	17:00.3	17:00.3
Mirror2	1	4	1	0	0	0	0	0	0	1	0	16:00.2	16:00.2	16:00.2
Mirror2	1	4	1	0	0	0	0	0	0	0	0	15:40.2	15:40.2	15:40.2
Mirror2	1	2	1	0	0	0	0	0	0	0	0	44:00.1	15:40.2	44:00.1
Mirror2	1	2	1	0	0	0	0	0	0	0	0	43:00.1	15:40.2	43:00.1
Mirror2	1	2	1	0	0	0	0	0	0	0	0	42:00.1	15:40.2	42:00.1