SnapManager for SQL Sizing Case Study
Posted: July 10, 2013 Filed under: NetApp, SQL Server | Tags: netapp smsql, netapp smsql sizing, smsql, smsql sizing, snapmanager, snapmanager for sql, sql sizing 2 CommentsThis SnapManager for SQL case study was conducted for a real world client. Anything in this study that could identify the client has been removed from the article to protect their business. I wanted to take this opportunity to document the procedures and explanations for sizing such an environment.
This particular implementation involved a three node SQL Server 2012 AlwaysOn Availability Group running on Server 2008R2 physical servers. The databases are new and haven’t been populated with data, yet, so the sizing had to take these “known unknowns” into account. SnapManager for SQL 6.0 and SnapDrive for Windows 6.5 were used. The NetApp system includes a FAS3220 in an HA pair running Data ONTAP 8.1.2 7-mode.
Typical best practices were used such as using volume autogrow, letting SnapManager take care of Snapshot deletions, etc. I don’t address thin provisioning, deduplication, or space reservations in this document beyond saying that Fractional Reserve is kept at its default 0% and SnapReserve is changed to 0%. I suggested the LUNs and volumes be thinly provisioned because the client has a trained and dedicated NetApp Administrator on staff with the tools and alerts necessary to manage aggregate capacity properly. The storage deployment is a new, mid-size deployment and capacity is already at a premium. Thin provisioning now, monitoring, and growing or shrinking volumes and LUNs as actual growth is observed was advised so as not to waste space. Deduplication was used on the database volumes and CIFS shares – not the transaction logs, SnapInfo, TempDB, or System Databases.
A logical diagram of a SQL Server replication scheme is show below. There is an OLTP database that is relatively large compared to the many smaller databases that comprise the Data Warehouse (DW). Each Primary Replica will be at Site A hosted, under normal conditions, on separate nodes. These nodes will then synchronously replicate within the same site to the other node. Asynchronous replication will happen across sites to Site B and a third node.
Performance Analysis for a SQL Cluster
Posted: July 23, 2012 Filed under: SQL Server, Windows | Tags: analysis, cluster metrics, cluster performance, metrics, performance, performance analysis, sql, sql cluster, sql cluster metrics, sql cluster performance, sql metrics, sql performance 5 CommentsI was recently asked to pull the performance metrics for a new SQL cluster at work. In an effort to finally get back to blogging, I thought I’d share my results and how someone else may be able to look at their clusters for ways to improve. I should start by saying that although this analysis was performed on a two-node Windows Server Failover Cluster using Windows Server 2008 R2 x64 (WSFC, formerly MSCS) and SQL Server 2008, SQL-specific metrics are not pulled. Rather, I looked at the Big Four: CPU, Memory, Disk, and Network. The second node in the cluster, Node B, was analyzed because the application using the first node was not in production yet, so we knew that node would barely be utilized.
Using Microsoft System Center Operations Manager (a behemoth in its own right!), I was able to pull the previous six days’ worth of performance data. What I included in my analysis were graphs of performance for seven days, an explanation of what the data was measuring, and somewhat of an average baseline against which to measure.
The original work was presented in PowerPoint. I’ve taken screenshots of the presentation and included them here.
Posted: February 5, 2012 Filed under: Site Surveys, SQL Server, VMware, Windows | Tags: srm, vcenter, vcenter design, vmware, vmware update manager, vmware vcenter, vum 3 Comments
In response to Miguel’s post, here are my thoughts:
I’m sure at least one of the VMware dudes Miguel was talking to was once a Windows System Administrator. I’m also sure that that same VMware dude cringed at the thought of needlessly putting multiple services on a single VM. He probably thought that as long as the customer had enough money for Windows Server licenses, compute and disk resources, that one should obviously separate each service into their own server. Now, to take a step back, let us say that, yes, it certainly is possible to put all the services you mentioned, vCenter, SQL 2008, VUM, and maybe even SRM on the same box, whether virtual or physical. But of course, whether this is possible or not is not in question. It’s whether it should or should not be done in the first place. I’m going to pull out the age old consultant’s answer and say, “It depends.”
It depends on if the customer has the budget for more Windows or SQL licenses. Does the customer have the compute and disk resources for several more servers? Is there already an existing SQL box or cluster that could be used? Is a DBA on staff, or at least a competent Windows Server admin? Does the customer’s environment even need a full blown SQL installation or would SQL Express do fine?
Now I’m coming from a background of government contracting where money is usually thrown at such projects. Resources for such an implementation are little thought about because they’re going to be there no matter what. This question could impact SMBs more, but probably not large corporations.
I think there are certainly right and wrong ways to implement based on circumstances. On the one hand, if you have the licenses, compute, disk, and administrative resources, I say absolutely, put each service on it’s own separate box. In more constrained environments, you may need to double up two or more services.
That’s not the least of it. Recovering from a failed VM will cost you less in time, effort, and hopefully, money. With an “all your eggs in one basket” approach, if one VM goes down, is somehow unrecoverable, then you’ve lost a lot of data. Separating your services reduces the liklihood that any one VM failure/loss will result in mutlitple services lost.
So I was having a discussion with a few fellow VMware dudes, and we were discussing the vCenter installation methods. One train of thought is to install vCenter, VUM, SQL 2008,, and SRM on 1 VM with 2 vCPUs, 4 GB of memory an a 100 GB drive, Monitor for performance and adjust as required by analyzing the performance data. I have alwbeen doing installations this way lately without issue. I have also done installations on dedicated SQL boxes \ VMs. I have gotten good performance out of the environment with having all services on a single VM. In larger environments of 20 or more hosts and 300 + VMs, I have used a dedicated SQL server. The SRM documetation recommends a separate server for the SRM installation, but I have not seen any issues with it on the same box, and there was not any performance degradation in an…
View original post 147 more words
SQL Server 2008 backups for VMware databases
Posted: October 14, 2011 Filed under: SQL Server, VMware, Windows | Tags: 2008, backups, server, sql, sql 2008, sql server, vcenter, vcenter server, vmware Leave a commentAt a minimum, you’ll want to perform regular backups of your vCenter, Update Manager, and System databases. You don’t have to be a DBA to perform simple backups. You don’t need to know T-SQL or database programming to perform these steps. There’s an easy wizard that walks you through a standard Windows Next-Next-Finish set up.
There are a couple things to note in the walkthrough below. We’re using SQL Server 2008 Enterprise Edition 64-bit on a 64-bit Windows Server 2008 SP2 Enterprise Edition. The SQL server is also a virtual machine in a vSphere 4.1 environment.
Restoring vCenter Database on SQL Server 2008
Posted: October 13, 2011 Filed under: SQL Server, Windows | Tags: database, restore, restore database, server, sql, sql server, vcenter, vcenter database Leave a commentHopefully you’ll never have a disaster hit your datacenter, but if you do, this guide can show you how to restore your vCenter database to your latest backup. Your vCenter database holds all the information you see in the vSphere Client and more. Although your VMs will still run your ESXi hosts without vCenter and its associated database, you lose a lot of enterprise functionality.
In my testing, I found the SQL server to which you recover can have a different name. I did not change the name of my vCenter server. All machines involved had different IP addresses and resided in a different domain. All domain service accounts were recreated in the test domain. I’m leaning towards the possibility that the vCenter server can have a different name, as well.
Trouble installing SQL Server 2008 on Windows Server 2008?
Posted: October 13, 2011 Filed under: SQL Server, Windows | Tags: 2008, highly secured, install failed, installation failed, security, server, sql, sql server, Windows, windows server 2008 Leave a commentA highly secured Windows installation can make your SQL installation fail
There are some highly modified default installations of both Windows desktops and servers that certain institutions use to increase the security of their networks. These versions of Windows are focused on security and are locked down from the ground up, which is a good thing. But all these security settings can give an IT guy headaches if you’re trying to get things accomplished. One such feature can make your SQL install fail. I happened to come across this recently in a test lab.
If you’re driving along with a standard SQL install, everything will be going fine until, towards the end of the installation process, you see the gem below. And SQL installs take a little time to complete. Having to reinstall can be a real pain.