SQL server 2005 mirror problem

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


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

"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 ..

it seem no update at all !

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?

"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 ?

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)

"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?

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?

"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.

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.

"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 ?

Yes.

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

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 ?

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.

"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"

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)

"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.

I think I might try to adjust the max thread first and then upgrade to SP4, then change hardware/VM as the final steps.

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.

"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..

Are you running both sql as VMs or just the DR?

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

"Are you running both sql as VMs or just the DR?"

just the DR .

one thing interest me is that, during this few days, it don't have any error, this means it is just not stable.

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.

"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 ?

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.

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.