• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

SQL server 2005 mirror problem

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
marrowyung
Asked:
marrowyung
  • 15
  • 12
1 Solution
 
marrowyungAuthor Commented:
"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
 
marrowyungAuthor Commented:
it seem no update at all !
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
arnoldCommented:
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
 
marrowyungAuthor Commented:
"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
 
arnoldCommented:
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
 
marrowyungAuthor Commented:
"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
 
arnoldCommented:
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
 
marrowyungAuthor Commented:
"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
 
arnoldCommented:
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
 
marrowyungAuthor Commented:
"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
 
arnoldCommented:
Yes.

While changing the Vm, are you also changing the physical system on which the VM is running?
0
 
marrowyungAuthor Commented:
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
 
arnoldCommented:
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
 
marrowyungAuthor Commented:
"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
 
arnoldCommented:
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
 
marrowyungAuthor Commented:
"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
 
marrowyungAuthor Commented:
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
 
arnoldCommented:
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
 
marrowyungAuthor Commented:
"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
 
arnoldCommented:
Are you running both sql as VMs or just the DR?

See whether the resources you've allocated are sufficient within  vcenter.
0
 
marrowyungAuthor Commented:
"Are you running both sql as VMs or just the DR?"

just the DR .
0
 
arnoldCommented:
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
 
marrowyungAuthor Commented:
one thing interest me is that, during this few days, it don't have any error, this means it is just not stable.
0
 
arnoldCommented:
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
 
marrowyungAuthor Commented:
"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
 
arnoldCommented:
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
 
marrowyungAuthor Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 15
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now