Solved

SQL server 2005 mirror problem

Posted on 2013-06-03
30
425 Views
Last Modified: 2013-06-18
Dear all,

right now we found the SQL server 2005 with SP2 mirror replication keep disconnect/pause without any reason, that DR server runs on a VM and we did change to a better VM.

before we switch to a new VM, all mirror will disconnect/pause at the same time and we have 20-30 DB doing mirror at the same time,

after we move the VM to a better hardwrae, the number of disconnect/pause mirror is much less, around 6-7 each time.

so it is the VM does matter.

May I know it could help to upgrade SQL server 2005 from SP2 to SP4?
0
Comment
Question by:marrowyung
  • 15
  • 12
30 Comments
 
LVL 22

Expert Comment

by:Haresh Nikumbh
ID: 39215774
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39215969
"There was one hotfix for this issue , check if its installed or not


http://support.microsoft.com/kb/937041

http://technet.microsoft.com/en-us/library/hh393563.aspx"

I am talking about MIrror but not repliation sorry.

"SQL update link

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/49db6ee5-6621-4c82-b7f7-2db8a3dc6563

http://www.microsoft.com/en-us/download/details.aspx?id=7218 "

I have all these but I need to know if SP4 will on the stability of mirroring ..
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39220938
it seem no update at all !
0
 
LVL 76

Expert Comment

by:arnold
ID: 39224432
sql is an IO intensive system.  Mirroring 20-30 DB and the issue you report suggests that the resources allocated to the VM, new VM are not sufficient to handle the amount of data and resources needed.

Look at the mirroring monitor to see what the rate of data(how far back) are the transaction in the more active database and the rate of change in 20-30. DBs that you have.

You could on the VM use perfmon to monitor sql/memory/"disk iO" to see what is going on.

What are the sizes of the Dbs, what is the cumulative size of the active databases?
How much memory/many processors and where the "disk" is?
I.e. is e VM's disk an external iscsi target? An allocated resource on the local system that is made up of RAID 10/6?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39224496
"sql is an IO intensive system.  Mirroring 20-30 DB and the issue you report suggests that the resources allocated to the VM, new VM are not sufficient to handle the amount of data and resources needed."

this what I told my team too ! hope to have a dedicated machine for the DR.

"Look at the mirroring monitor to see what the rate of data(how far back) are the transaction in the more active database and the rate of change in 20-30. DBs that you have."

forget how to do this, usually I use script but just one DB mirror lag behind. How to do it in your way ?

"You could on the VM use perfmon to monitor sql/memory/"disk iO" to see what is going on."

what we expected to see?

The size of DB only few hundreds MB. Already very small but these DB is not busy.

VM has only 4 core  and 4.1GB of RAM.

if you see the link someone post here, it is talking about the X86 problem and threading, do you think it is true ?

as we are using X86 edition of SQL server 2005 with sP2 and that one sure not designed for VM, as a result I am considering the upgrade of SP4 (least work than reinstall everything) does help.

What is your option on this ?
0
 
LVL 76

Expert Comment

by:arnold
ID: 39224513
The bit version of the application is not that important as how much memory and resources you have.

The GUI mirror monitor you need to add the mirrored DB.

You have several things going on. Network, VM resources,

Try the following given you have two VMs.  Mirror a set of the 20-30 DBS to one VM and the rest to the other VM presumably you have two separate hosts for each VM and see if the distribution resolves the issue you have.

What is the host system resources are?

V ~2GB is used by the OS. In an X86 basedS out of 4GB only 3GB are available.
Depending on the OS (standard or enterprise)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39224520
"What is the host system resources are?" what is this mena ?

"Try the following given you have two VMs.  Mirror a set of the 20-30 DBS to one VM and the rest to the other VM presumably you have two separate hosts for each VM and see if the distribution resolves the issue you have."

but a stronger VM or dedicated Machine also help?
0
 
LVL 76

Expert Comment

by:arnold
ID: 39224522
The Host system resources deals with the physical server where you are running the VM.
I.e. host has two processors quad core with 8GB of ram running OS/hyper-v
The VM has 1 processor quad core with 4GB.

From your prior experiment when you transition from VM where you had many paused to the newer where you have fewer, what resource changes did you make?
Processor, memory?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39224580
"From your prior experiment when you transition from VM where you had many paused to the newer where you have fewer, what resource changes did you make?
Processor, memory?"

yes.
0
 
LVL 76

Expert Comment

by:arnold
ID: 39224623
Difference 1 processor 2 core to 1 processor 4 core
memory 2GB to 4Gb?

A yes, only confirms that your VM is experiencing issues with resource allocation.
Once you properly allocate additional resources, the issue will be resolved.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39224657
"Difference 1 processor 2 core to 1 processor 4 core
memory 2GB to 4Gb?"

you mean is did we increase the CPU or RAM, and if it really is and the performance is better than this direction is correct and we should change a better VM to solve the problem ?
0
 
LVL 76

Expert Comment

by:arnold
ID: 39224675
Yes.

While changing the Vm, are you also changing the physical system on which the VM is running?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39224701
yeah, I know what you mean, our infra team know about this and they did.

right now the problme is we do not have budget !

you are talking about the physical hardware that host that VM, we will focus on this.

So no more change on SQLserver 2005 work threading for mirror ?
0
 
LVL 76

Expert Comment

by:arnold
ID: 39225325
Threading where? On the VM?

Instead of mirroring all 30, narrow down to the important ones and mirror them.

Make sure you have backups of databases going.
Mirroring is not really a backup given an errand delete/update alters the data on both.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:marrowyung
ID: 39227912
"Instead of mirroring all 30, narrow down to the important ones and mirror them."

what do you mean? you mean only mirror some but not all ?

Worker thread for mirror:

http://technet.microsoft.com/en-us/library/ms187024(v=sql.90).aspx
http://support.microsoft.com/kb/2001270:

"Database mirroring is limited by the number of threads available in SQL Server which in turn is directly dependent on the computing power of the machine. By default in SQL Server 2005 and above, the “max worker threads” is 0 which means a dynamic value. This value is automatically adjusted by SQL Server when the computing power changes.  SQL Server uses the following formula when calculating max number of worker threads"
0
 
LVL 76

Expert Comment

by:arnold
ID: 39228230
Yes, since you do not have enough resources, mirror the important/actively used ones, while your backup plan will deal with the backups.
You could try using log shipping for the others.(note the backup of transaction logs when doing log shipping)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39228311
"Yes, since you do not have enough resources, mirror the important/actively used ones"

but I think if we want to DR that, then it is hard to explain why we don't DR all.

The recoevery of DR server is much faster than backup and restore, you konw it ,right?

"You could try using log shipping for the others.(note the backup of transaction logs when doing log shipping)"

so do mirror and log shipping at hte smae time will be better than all mirror ? but I don't see difference from my point of view.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39228315
I think I might try to adjust the max thread first and then upgrade to SP4, then change hardware/VM as the final steps.
0
 
LVL 76

Expert Comment

by:arnold
ID: 39228349
Are you running VM under windows OS host or VMware?
The most current SP fixed several things, but as you've seen in your first VM transition, the issue is resources related.

Do you have mirroring with a witness?
Log shipping could provide DR transition with international not unlike one with mirroring without a witness.

Are the applications you use sql mirror aware? If not, the applications must also be manually reconfigured when transitioning from one sql server to another.

Log shipping is not synchronous as mirroring by default are.
I.e. the log shipping will usually have a 15 minutes or so window between replication/restore of transaction logs from the primary to the DR.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39228382
"Are you running VM under windows OS host or VMware?"
VMware,

"Do you have mirroring with a witness? "
no. This one provide auto failover feature for the mirror.

"Are the applications you use sql mirror aware? If not, the applications must also be manually reconfigured when transitioning from one sql server to another."
no need to concern on this, we have no problme on this .

"Log shipping is not synchronous as mirroring by default are.
I.e. the log shipping will usually have a 15 minutes or so window between replication/restore of transaction logs from the primary to the DR. "

yes, I know, Asyn ma..
0
 
LVL 76

Expert Comment

by:arnold
ID: 39228395
Are you running both sql as VMs or just the DR?

See whether the resources you've allocated are sufficient within  vcenter.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39228485
"Are you running both sql as VMs or just the DR?"

just the DR .
0
 
LVL 76

Accepted Solution

by:
arnold earned 500 total points
ID: 39228863
You could try using two VMs on the same host and have some databases on be while the others are on the other VM and see if that handles the mirroring better while providing the DR that you want.  

It might be that trying to maintain all 30 mirrors within a single VM requires more resources than the host system has where using two VMs distributes the load and fits within the confines of the resources available on the host system.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39250884
one thing interest me is that, during this few days, it don't have any error, this means it is just not stable.
0
 
LVL 76

Expert Comment

by:arnold
ID: 39250901
Add perfmon monitoring on the host as well as the VM and the principal to see whether the VM has adequate sec for one usage scenario, but not adequate every so often when the systems are seeing a heavier load.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39252142
"whether the VM has adequate sec for one usage scenario, but not adequate every so often when the systems are seeing a heavier load. "

What is this mean ?
0
 
LVL 76

Expert Comment

by:arnold
ID: 39252247
Right now you are not seeing issues with mirroring. You may have every so often a situation where there is a large amount of input. That overloads the VMs ability to keep up.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39258285
ok, yeah, we really focus on the VM itself...

you suggset me the create one more VM to load balance that out, but if we have $$ we will just optimize the VM. this is understandable.

But before that,  I think I will propose the upgrade of SP4 from SP2 in case we can do better BEFORE we spend money!

thanks for this.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now