SQL Server Virtualization Advantages and Disadvantages (Part 2 of 5)

By: Scott Shaw

Share

Related Tips: More

Problem

Your previous tip helped define the VM environment and discussed some of the advantages and disadvantages of virtualization. What about virtualizing SQL Server? Is this always a good idea? What are some things I need to think about?  Check out this tip to learn about the advantages and disadvantages of virtualizing SQL Server.

Solution

You will need to consider at least two paths before deciding to virtualize any of your SQL Servers. The first path is to think about why you are virtualizing your systems; consider the advantages and disadvantages a virtual environment provides. Some of these advantages and disadvantages are systemic to a specific vendor and others are systemic to virtualization. The second path is whether or not your SQL Server is capable of being virtualized; is it feasible to virtualize your SQL Server? You’ll base the decision to virtualize both on how the SQL Server is being used and the size of your SQL Server. For either one of these paths you’ll most likely need to argue against virtualization as opposed to arguing for virtualization. This is a general observation, but I’ve found that virtualization provides so many benefits outside the SQL Server environment that your company has already decided to implement the technology. I have witnessed one large company refusing to virtualize but, since their competitors are virtualizing, it won’t be long before they begin implementation.

I’m going to purposely ignore the advantages of virtualization as they pertain to the datacenter and instead focus on the advantages for the SQL Server DBA. There is some overlap, but I want to address specifically how virtualization helps you and your SQL Server environment. I won’t be able to cover everything, your mileage may vary depending on what features you implement and what vendor you use. Still, I want to provide a decent overview so let’s take a look.

Advantages of Virtualizing SQL Server

Advantage #1: Provisioning (Hot-Swapping)

By provisioning I’m referring to the capability of dynamically assigning additional resources to a VM instance. I cannot overstate the beauty of being able to add 100GB to a dying LUN without downtime and without the user even knowing its happened. You want another 3 GB of memory – no problem. The process really is a thing of beauty. It helps us DBAs focus on other things and it gives me a sense of confidence that I can provide additional resources to the instance on a moment’s notice.

Advantage #2: Resource Management

This is what virtualization was created to do and there are advantages. I’m a big recycler and I hate waste. I guess that’s why I gain weight because I always have to finish what’s on my plate. I just can’t stand throwing away food. I apply this same OCD to my servers. I can’t stand a server consistently showing 95% CPU idle time – it’s a waste of CPU. On the flip-side a physical server showing 95% CPU utilization drives me crazy when I don’t have another slot available. Virtualization frees me by allowing me to think about this less often (notice I didn’t say ever). Ballooning is the method by which VM moves around memory. Ballooning will add memory to your VM if you need it and take it away if it doesn’t (assuming you are not using lock pages in memory). vCPU’s can be ramped up or ramped down as necessary. VM is especially helpful for those once-a-year-I-demand-everything budget applications.

Advantage #3: Cloning

We recently had a request for 14 new SQL Servers (seriously!). They all ran the same application, used the same databases, and had the same configuration. The only difference was they were in different geographic locations. In the past this might have been a nightmare manual process subject to countless possible misconfigurations. Instead, in the virtual world, the VM team cloned one system 13 times. All I had to do was go to each system and update the @@servername to reflect the new name. Voila! 14 new SQL Servers to manage and, especially, license. More about that next.

Disadvantages of Virtualizing SQL Server

Disadvantage #1: Provisioning (New Servers)

Provisioning is the double-edged sword of virtualization. Since we’ve virtualized our environment, our server count has increased over 20%. We build anywhere from 4 to 10 new servers every month. We decommission some servers, but most are brand new and many still including test and development environments. Server sprawl and how to handle, manage and license the sprawl will be a serious issue going into 2011 and beyond. We do try to consolidate on a shared server when possible, but most systems don’t fit into that model. The business has little incentive to consolidate SQL Server because virtualization has already consolidated most of the environment. Virtualization consolidates all the physical server resources and drastically shrinks the footprint in the datacenter. After virtualization a company tends to lose the driving momentum to consolidate databases. Make sure you have clear SQL Server install and configuration processes in place prior to virtualization, because you’ll be doing a lot more installs. I recommend trying to use an automated install application such FineBuild.

Disadvantage #2: Division of Duties

After virtualization you are most likely going to have 5 groups managing (and learning) the virtual environment: VM Team, Storage Team, Server Team, Backup and Recovery Team and the DBA Team. This will depend on the size of your organization. A large organization will separate the normal operational server team from the VM team. The VM team is the group of folks who implemented the virtualization and they may slowly hand over duties to the operational server team, but still retain authoritative control over the environment. You will need to interface with all of them at a more intense level than in the past. In our shop the SQL DBAs have full access to all the SQL Servers and read access to vSphere and we still do not have insight into the full details of our SAN environments or what is shared on our hosts. Backup and recovery are constant struggles because many backup errors are due to problems with vMotion or our NetApp infrastructure. The point here is you’ll need to ramp up communication at the same time you ramp up virtualization.

Disadvantage #3: Performance Tuning

I’ll address this more later in the series, but how you monitor performance will change after virtualization. It becomes a bit more complicated and you’ll need to learn some new terminology. You can’t simply rely on performance monitor. In a VMWare environment you’ll need to start using vSphere or add VMWare performance counters to your performance monitor logs. This is where the deep dive comes in because in order to understand performance in a virtual environment you’ll need to understand how virtual environments manage resources.

Disadvantage #4: Licensing

Along with server sprawl comes the issue of licensing. Add on top of that the virtual environment and its time to call a shrink, and the bank. You’ll need to work out licensing with your company and Microsoft to make sure you’re compliant in a virtual environment. Basically when you run SQL Server 2008 R2 in a virtual environment you license for each virtual processor used by the virtual operating system. You do not license based on the physical processors. Keep in mind though the vCPU is considered to have the same number of cores as the pCPU. This means you cannot license a vCPU by core (for example, 2 core pCPU but only 1 licensed vCPU). This scenario changes based on the version of SQL Server you use. Let’s say you purchase the Datacenter edition of SQL Server 2008 R2. Once you license it for all of the physical processors on the host, you can run an unlimited number of SQL Server instances.

When Not to Virtualize SQL Server

Let’s start with a basic limitation in VMWare. ESX 4.1 limits the total vCPU to 8 (I believe Hyper-V limit is 4). Granted, you get a lot more bang for the buck per vCPU in a virtual environment, but I would suggest running a tool like VMWare’s Capacity Planner prior to virtualizing any heavy-hitters. I wouldn’t necessarily consider virtualizing large data warehouses, but not because of size. We successfully virtualized our 800 GB SharePoint server so size is not a problem. The primary point for virtualization is consolidation, so I tend to think if you virtualize a high octane system and it’s the only system that can run on a host than you aren’t gaining much. There is DR and HA to consider, but that’s a call unique to your business. I guarantee things will change soon. A new version of ESX is due out soon and I’m sure they’ll up the vCPU limit.

It’s also possible there may be some systems, mostly older systems, which may just not virtualize. This isn’t a SQL Server problem though. We’ve virtualized everything from SQL 7 to SQL 2008 R2. There may just be some odd applications that don’t work well in a virtual environment. We have one critical Windows cluster that would not survive the P2V. We decided to just leave it physical instead of pushing the issue. As a DBA I suggest closely monitoring the migration effort and be ready to test the system post-virtualization.

The conclusion is for most organizations there will be very few systems that you can’t virtualize. If you have your doubts, then be sure to test prior to migration or prepare an effective rollback plan.

In Conclusion

If you looked closely you’ll notice the disadvantages outweigh the advantages. Still, I’m a big fan of virtualization. We can overcome all the disadvantages. The advantages are too good to pass up. As the virtual software gets better and better we’ll see larger and larger systems virtualized. As more and more companies experience successful virtualizations others will continue to make the leap. One purpose of this series is to both provide my own experiences and consolidate my research. DBA’s need to work together to make the virtualization process transparent to themselves and to others.

Next Steps

• Microsoft Licensing in a Virtual Environment.
• I’ve always liked this post by Gavin Payne. It’s very appropriate in a virtual environment Infrastructure DBA.
• Brent Ozar post on deploying SQL Server on VMWare Top 10 Keys to Deploying SQL Server on VMware.
• If this tip completely lost you then refer back to Part 1 – SQL Server Virtualization Overview (Part 1 of 5).
• My next installment will discuss the virtualization planning phase. I’ll discuss IO resources and licensing. I’ll talk about whether to create a new VM or P2V and whether Windows clusters are still a viable HA solution.

http://www.mssqltips.com/tip.asp?tip=2441

Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process.

Most well-designed applications, after receiving this message, will resubmit the transaction, which most likely can now run successfully. This process, if it happens often on your server, can drag down performance. Here are some tips on how to avoid deadlocking on your SQL Server:

• Have the application access server objects in the same order each time.
• During transactions, don’t allow any user input. Collect it before the transaction begins.
• Keep transactions short and within a single batch.
• If appropriate, use as low of an isolation level as possible for the user connection running the transaction.  [6.5, 7.0, 2000]

*****

To help identify deadlock problems, use the SQL Server Profiler’s Create Trace Wizard to run the “Identify The Cause of a Deadlock” trace. This will provide you with the raw data you need to help isolate the causes of deadlocks in your databases.  [7.0, 2000]

*****

If you are unable to eliminate all deadlocks in your application, be sure to include program logic that will automatically resubmit a transaction after a random about of time after a deadlock appears and terminates a user transaction. It is important that there is a random waiting period because it is possible that another contending transaction could also be waiting, and you don’t want both contending transactions to wait the same amount of time and then both try to execute at the same time, causing another deadlock.  [6.5, 7.0, 2000]

*****

Keep All Transact-SQL transactions as short as possible. This helps to reduce the number of locks (of all types), helping to speed up the overall performance of your SQL Server application. If practical, you may want to break down long transactions into groups of smaller transactions.  [6.5, 7.0, 2000]

*****

Any conditional logic, variable assignment, and other related preliminary setup should be done outside of transactions, not inside them. Don’t ever pause a transaction to wait for user input. User input should always be done outside of a transaction.  [6.5, 7.0, 2000]

*****

Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure. This provides two benefits that help to reduce blocking locks. First, it limits the client application and SQL Server to communications before and after when the transaction runs, thus forcing any messages between them to occur at a time other than when the transaction is running (reducing transaction time).  Second, It prevents the user from leaving an open transaction (holding locks open) because the stored procedure forces any transactions that it starts to complete or abort.  [6.5, 7.0, 2000]

*****

If you have a client application that needs to “check-out” data for awhile, then perhaps update it later, or maybe not, you don’t want the records locked during the entire time the record is being viewed. Assuming “viewing” the data is much more common that “updating” the data, then one way to handle this particular circumstance is to have the application select the record (not using UPDATE, which will put a share lock on the record) and send it to the client.

If the user just “views” the record and never updates it, then nothing has to be done. But if the user decides to update the record, then the application can perform an UPDATE by adding a WHERE clause that checks to see whether the values in the current data are the same as those that were retrieved.

Similarly, you can check a timestamp column in the record, if it exists. If the data is the same, the the UPDATE can be made. If the record has changed, then the application must include code to notify the user so he or she can decide how to proceed. While this requires extra coding, it reduces locking and can increase overall application performance.  [6.5, 7.0, 2000]

*****

Use the least restrictive transaction isolation level possible for your user connection, instead of always using the default READ COMMITTED. In order to do this without causing other problems, the nature of the transaction must be carefully analyzed as to what the effect of a different isolation will be.  [6.5, 7.0, 2000] More information from Microsoft

*****

Using cursors can reduce concurrency. To help avoid this, use the READ_ONLY cursor option if applicable, or if you need to perform updates, try to use the OPTIMISTIC cursor option to reduce locking. Try to avoid the SCROLL_LOCKS cursor option, which can increase locking problems. [6.5, 7.0, 2000]

*****

If your users are complaining that they have to wait for their transactions to complete, you may want to find out if object locking on the server is contributing to this problem. To do this, use the SQL Server Locks Object: Average Wait Time (ms). You can use this counter to measure the average wait time of a variety of locks, including: database, extent, Key, Page, RID, and table.

If you can identify one or more types of locks causing transaction delays, then you will want to investigate further to see if you can identify what specific transactions are causing the locking. The Profiler is the best tool for this detailed analysis. [7.0, 2000]

*****

Use sp_who and sp_who2 (this stored procedure is not documented in the SQL Server Books Online, but offers more detail than sp_who) to identify which users may be blocking what other users. [6.5, 7.0, 2000] More info from Microsoft.

*****

Try one or more of the following suggestions to help avoid blocking locks: 1) Use clustered indexes on heavily used tables; 2) Try to avoid Transact-SQL statements that affect large numbers of rows at once, especially the INSERT and UPDATE statements; 3) Try to have your UPDATE and DELETE statements use an index; and 4) When using nested transactions, avoid commit and rollback conflicts. [6.5, 7.0, 2000]

Senin, 16 Februari 2009 | 16:02 WIB