Highlighted
ZianPaul
2 Iron

Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Hi,

I got a Dell PowerEdge server R420 connected to an EQL. Installed is ESXi 5.1 U1 (free version). There is only one data store.

VM runs Windows 2008 R2 STD with MS SQL 2008 R2 STD. The change in database size per day is around 2 to 3 MB base on my daily full backups via MS SQL backup.

However, when I initiate a replication session between my PROD EQL and DR EQL that delta size is ranging from 1 GB to 50 GB each time I executed a replication ? Interval is around 5 to 10 minutes. I tried 20 mins also the size to be replicated is erratic.

Why is the replica so huge its more than 1000 MB per replication.

 

 

Hope someone can help me on this.

Thanks,

Paul 

0 Kudos
1 Solution

Accepted Solutions

RE: Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Re: Storage Direct.  Yes, if you configure replication of those SQL volumes, only that changed data will be included.  That VM won't see the Datastore files at all.   However, you will still want to replicate your VMFS datastore, just not as often as SQL.

Re: Optimze.  The KB article I included has the Registry setting you need.  It works for all NTFS formatted storage.

Also, when you format the SQL data / log volumes, set the NTFS cluster size to 64K.   The default is typically 4 or 8K.  This will align all the writes and reads on the 64K stripe size in the EQL array.   This improves IO performance.

 

Social Media and Community Professional
#IWork4Dell
Get Support on Twitter - @dellcarespro

0 Kudos
9 Replies

RE: Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Hello,

With Virtual Datastores, you are replicating everything.  C: drive IO, OS pagefile writes, VMware temp, swap, logs.

For applications like SQL, Exchange or Sharepoint, I would suggest you create dedicated volumes on the EQL array.  Use the MS iSCSI initiator to connect to those volumes directly, then install the Host Integration Software (HIT/ME) .    This will allow you to get better performance and create more consistent snapshots/replciation of your SQL databases.   This is known as "Storage Direct". 

Then you can use Dell's Virtual Storage Manager (VSM) to create more consistent snapshots and replicas of your VMFS datastores on a slower schedule.

 HIT/ME and VSM are downloadable from the Equallogic support website.  They are included at no cost as part of your support contract.

This link has a document on how to configure ESXi with EQL storage.

http://en.community.dell.com/techcenter/extras/m/white_papers/20434601/download.aspx

Regards,

 

 

Social Media and Community Professional
#IWork4Dell
Get Support on Twitter - @dellcarespro

0 Kudos
ZianPaul
2 Iron

RE: Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Hi,

Will try out storage direct as soon as possible and install HIT/ME. Will this greatly reduce the replica volumes?

However, Im using the free version of VMware without VCenter...

Will I be still able to use VSM ?

 

Thanks,

Paul

0 Kudos

RE: Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Re: VSM.  Sorry, no.  That specifically has to work with VCS so VCS will create VMware snapshots.

Re: Replication.  Yes. You can also put VMware swapfiles on a different datastore and don't replicate that.    By default even READS cause a small write to occur.  Since OS's update the last date/time accessed.   Windows has a registry key to disable that.  

EQL breaks up storage in to 15MB pages,  once any part of that page is allocated, the entire 15MB page is set for replication.  However, at replication time a finer filter is used on each page to only actually transfer the data changed in that page. 

This is a KB on the Equallogic website that talks about Snapshots.  It's the same process for replication.

Solution Title
ARRAY: Snapshot/Replica space getting used up too quickly
Solution Details
If you find that your snapshot reserve space is filling up faster than you expect it to, or your replication jobs are much larger than you expected them to be, it really means that your volume is being written to more than you think it is. There are a number of reasons why your filesystems may be getting modified much more than you think. This list is representative rather than exhaustive - there are probably many more packages that make quiet but significant changes to a volume.

o Microsoft Exchange Server has an option called mailbox management that can rearrange the location of mailboxes on the volume, causing lots of unexpected modifications to a volume. You can disable it in the SETOP Properties dialog.

Here is an article explaining how to set up mailbox management for Exchange 2003: http://searchexchange.techtarget.com/news/article/0,289142,sid43_gci1119801,00.html#

For Exchange 2007, the operation is to do daily defragmentation. http://technet.microsoft.com/en-us/library/aa997972.aspx

o Defragmenting a disk will essentially modify every block on the volume that has data on it. Any process that defragments in the background will cause you to fill up your snapshot reserve (and increase the size of your replication jobs) at an alarming rate. Some Logical Volume Managers (LVMs) do this. However, defragmenting occasionally can actually shrink average snapshot size over time, at the cost of a spike in snapshot size at the time the defrag happens. A single defragmentation job when your snapshots seem to large can help you decide if a regular defragmentation regimen is appropriate.

o We have found that on Windows file servers, the system automatically updates the "Last Access Time" field of the directory entry for each file touched, which can prove to have a very relevant impact on snapshot and replication utilization.

To disable Last Access Time handling on an NTFS filesystem, add the following key to the Registry on your server:

HKeyLocalMachine\SYSTEM\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate
REG_DWORD
And set it to 1

It requires a reboot of the host to take effect. This article in the Microsoft KB describes this parameter for Windows 2003:

http://technet2.microsoft.com/WindowsServer/en/library/80dc5066-7f13-4ac3-8da8-48ebd60b44471033.mspx...

For Windows 2008: http://technet.microsoft.com/en-us/library/cc959914.aspx

Snapshots and replications taken after the next reboot of that host may be noticeably smaller, and you may see increased performance.

o W2indows VSS snapshots could be utilized without the EqualLogic VSS hardware provider software installed. The Microsoft VSS provider uses a snapshot repository file to store the copy-on-write data, and by default this repository lives on the disk itself. So if you are using the MS VSS provider for Previous Versions functionality, or for VSS backups, that will cause unexpectedly large writes to your disk even though the actual data in use is not growing. Two options to continue using VSS but not have the cost in writes are:

If you are using VSS for backups, install the Host Integration Tools to enable array-based snapshots for VSS instead of local repository files.

If you are using VSS for Previous Versions functionality, create a separate volume to hold the VSS repository file, so that activity in the VSS repository does not cause additional I/O to your original volume

o Other operating systems also implement snapshotting on local disks using software. Similarly to Windows VSS, other operating systems that implement snapshots in software will create significant additional writes while the snapshots exist. This exists, for instance, in VMWare ESX. Whether or not you can adjust that effect is dependent on the operating system. However you may not have the option of not using OS-based snapshots.

o Some virus scanners and backup programs modify the directory entries of every file they touch, usually setting the "Last Access" time for each file and/or the "archive bit" in the directory entries. Although these scans aren't modifying the files themselves, directories occupy disk space as well, and modifying them causes snapshot reserve to be used. Because directories are normally distributed randomly across the disk, any task that routinely accesses every file in a filesystem, and is not careful about how it does it, will use up a lot more snapshot reserve than might be expected.

For backup software, you should have the backup job not modify the Archive Bit, but instead use a database catalog or modification time to track file changes, if the software supports such an option.

One possible approach to minimizing the impact of directory scans like that is to rearrange the filesystem to put all the directories in the same area of the disk. Some defragmenting tools can do this, though you will need to specifically look for this feature. Windows defrag may not support this option.

Remember that disk defragmentation can cause a spike in snapshot utilization because of the number of blocks it writes to as it moves data around. Directory defragmentation is not normally something that would need to be run often, but the operator will need to find a balance between the impact of the defragmentation itself, and the impact of the directory scan.

Snapshots only occupy space when data is modified. The space occupied by a snapshot is the space on the original volume that has been modified since the snapshot. So the moment a snapshot is made, it occupies very little space, since it's just a pointer to the original volume. As modifications to the original volume are made, snapshot space is used up keeping a copy of the original data.

When a second snapshot is made, it is made relative to the first snapshot. Space is allocated to the second snapshot whenever the first snapshot is changed - the second snapshot contains a copy of the first snapshot's data before the change. A third snapshot is made relative to the second. Deleting a snapshot doesn't release all of its reserve space, since snapshot reserve can be a part of multiple snapshots, depending on when the data was modified.

Regards,

 

 

Social Media and Community Professional
#IWork4Dell
Get Support on Twitter - @dellcarespro

0 Kudos
ZianPaul
2 Iron

RE: Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Hi,

How about for MS SQL, is there any guide to optimize that, so I won't be replicating none important bits ?

 

As I understand when I use storage direct and present a  EQL volume directly to my new VM, when a snapshot or replica is created

shouldn't that exclude unnecessary VMware files located on its data store ? Since, the SQL database is now located on a different volumes?

 

How can we optimize MS SQL so that it wont update the bits if a read operation is executed ? My database is read extensive.... So most of the bit are always being read and updated....

 

 

Thanks,

Paul

0 Kudos

RE: Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Re: Storage Direct.  Yes, if you configure replication of those SQL volumes, only that changed data will be included.  That VM won't see the Datastore files at all.   However, you will still want to replicate your VMFS datastore, just not as often as SQL.

Re: Optimze.  The KB article I included has the Registry setting you need.  It works for all NTFS formatted storage.

Also, when you format the SQL data / log volumes, set the NTFS cluster size to 64K.   The default is typically 4 or 8K.  This will align all the writes and reads on the 64K stripe size in the EQL array.   This improves IO performance.

 

Social Media and Community Professional
#IWork4Dell
Get Support on Twitter - @dellcarespro

0 Kudos
ZianPaul
2 Iron

RE: Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Hi,

Last Question, can VSM be used with the free version of ESXi 5.1 ?

Someone advice me to use RDM from VMware to mount a LUN....

Can I get your comment on this. I would really appreciate any inputs..

 

Thanks,

Paul

0 Kudos

RE: Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Re: VSM.  No.  VSM requires Virtual Center. 

Re: RDM.  There a pros/cons to it.   If you can leverage one of Dell's HIT kits, then without a doubt use Storage Direct not RDMs.   RDMs are useful when you are migrating existing physical servers that had EQL volumes and won't take advantage of EQL software. 

This is covered in some detail in the Best Practices doc I referenced earlier.

 

Social Media and Community Professional
#IWork4Dell
Get Support on Twitter - @dellcarespro

0 Kudos
ZianPaul
2 Iron

RE: Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Hi,

I just tested the RDM, I still get large replication data.. I transferred the temp db, transaction logs and other to a different volume but still the data being replicated is huge...

I also set NtfsDisableLastAccessUpdate  = 1.... 

the RDM volumes just contain the database file.

Can the Microsoft Auto Snapshot manager detect only changes in files and not the blocks... Per review the change in database size per day is only around 10 mb... but my replication per 3 hours reaches 2 GB...

Thanks,

Paul

 

0 Kudos

RE: Dell EQL PS4100xv problem with MS SQL replication

Jump to solution

Re: Files.  No.  The array doesn't know about "files" just blocks of data if there's a write on a new page, the entire page gets allocated.

re: Registry.  Did you reboot after setting it?  

 

Social Media and Community Professional
#IWork4Dell
Get Support on Twitter - @dellcarespro

0 Kudos