?
Solved

SQL server 2005 mirror problem

Posted on 2013-06-03
30
Medium Priority
?
433 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 79

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 79

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 79

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 79

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 79

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 79

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

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 79

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 79

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 79

Accepted Solution

by:
arnold earned 2000 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 79

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 79

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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