Every year we have new people joining our ranks as database professionals. Many of these new people use systems at the places which they already work as the source of their learning when it comes to database design and data modeling. However this isn’t always the best idea unless the systems which they are referencing have been properly designed.
One system which I urge new people to the field to totally ignore as a reference source is SharePoint. Microsoft’s SharePoint has what we in the database field call a “really screwed up database design”. The SharePoint database design is so screwed up that the SQL Server Product team has had to add features to the SQL Server product so that the SharePoint team can use them in future versions of the SharePoint product.
Now I understand why people want to model their applications on the SharePoint database. It’s a very scalable application that can handle pretty much anything which you throw at it. However as a model for other applications it pretty much sucks. Being that scalable comes with a performance hit, as the database gets larger that performance hit just gets larger and larger. This is just one of the reasons that it is recommended that the SharePoint content databases be limited in size to 100 Gigs for SharePoint (MOSS) 2007 and 200 Gigs for SharePoint 2010.
The problem then becomes how are these new people supposed to know what a crappy database design looks like so that they know not to model other databases like it? That falls to the people that they work with to make sure that they have good solid systems to learn how to build applications properly. When they work in a small shop as the only developer there isn’t anyone to learn from. That’s where blogs and websites from known people in the field become key to learning how to do things correctly.
But what ever you do, don’t use SharePoint as the source of your database design knowledge. It is about the worst possible example you can use. I understand that Forefront Identity Manager is actually worse, but I haven’t dug into a FIM database yet. I will be looking into a FIM system that was setup so that I can see just how bad that database design is.
One of the questions that I see on the forums every once in a while is how to get a CNAME working with a network share. For example if you have a cluster where the server names are “SERVER1 and SERVER2″ and the virtual name is “VIRT1″ how can I allow users to connect to the network shares by using the server name “FILESERVER” so that they don’t need to change their mapped drives, etc. after the server FILESERVER has been migrated to the cluster.
The old way of doing this would have been to simple add a CNAME to the internal DNS server so that the name FILESERVER simply pointed to the name VIRT1. However in Windows 2008 and higher this doesn’t work any more without doing some registry hacking. While the registry hacking will work, this is a pain to maintain as every time you migrate another server to the cluster you need to go and edit the registry key on all the nodes of the cluster, then reboot all the nodes of the cluster. Not exactly the easiest solution to implement.
Fortunately there is a much easier technique which can be used. Simply add another network name to the cluster pointing to the network IP address of the virtual world. This will add another DNS entry so that your users can resolve the network name correctly and register the network name with the cluster so that it knows to accept this network name as a valid name for the cluster without needing to restart the nodes of the cluster or to edit the registry on all the nodes of the cluster.
To do this open the failover cluster manger and right click on the service or application and select “Add a Resource” from the context menu. Then select “1 – Client Access Point”. The wizard that opens will allow you to add a network name to the cluster resource group.
One of the really cool features of the new SEQUENCES which were introduced in SQL Server 2012 is that you can specify the size of the cache used for each sequence. However Microsoft is recommending that when creating sequences you should always specify the cache size of the sequence. This is because Microsoft has stated that they can and may change the default cache size at their whim between builds of SQL Server 2012 and in the future.
Because of this you should always specify the size of the cache so that you get consistent results from deployment to deployment, especially if you work for a software distributor (ISV, Consulting company, etc.).
Like all databases the VMware vCenter database needs to have the disks configured properly for best possible performance. Don’t get me wrong, if you’ve got a very small vCenter database that’s only a couple of gigs in size then the disk size isn’t all that big of a deal. However if you have a larger vCenter database with dozens of hosts and hundreds or thousands of guests you’ll want to spend a little time setting up your disk systems for the vCenter database correctly.
For a proper disk configuration you’ll want at least three different LUNs or local RAID arrays (I’ll assume you are SAN attached here, so if you are using local disk just replace LUN with RAID array). These three LUNs should be RAID 1 (or RAID 10, 0+1, whatever your storage provider calls this). One of these LUNs will host the Main Data File (the files that have a file extension of mdf). Another one of these LUNs will host the transaction log file (the files that have a file extension of ldf). The last LUN will be for the files for the tempdb database.
Now these three LUNs shouldn’t be sitting on the same physical disks (if you are using a 3PAR, IBM XIV, etc. that is shared everything you can probably ignore this part). You especially don’t want the data file and transaction log file on the same LUN. The reason for this is that both of these LUNs will be written to at the same time so having these on the same physical disks will cause contention at the disk level. You don’t want the tempdb on the same disks as either of these other LUNs either. Now if I had to pick one of these LUNs that I would put the tempdb LUN on the same physical disks with I could have it share spindles with the vCenter databases transaction log file. My reason for this is because most of the time that tempdb database will be used by the vCenter database it’ll be reading data from the main vCenter databases and putting it into the tempdb database for processing. So there should be less contention if the tempdb shares spindles with the transaction log file. If possible keep all three LUNs on separate sets of spindles.
When sizing the LUNs don’t forget that the vCenter database will be keeping at least a years worth of data, so make sure that you give the system plenty of room. At least 20 or 30 Gigs would be a good starting point. VMware should have some sizing estimates that you can use on their website. The bigger your VMware environment is the more disk space you’ll need.
When it comes to moving the vCenter databases to the new LUNs (assuming that you aren’t starting with a new vCenter database here) you’ll want to shutdown the vCenter services, then right click on the vCenter databases in SQL Server Management Studio and select all tasks, then detach. Then move the databases to the correct location. Then in SQL Server management studio right click on databases and select All Tasks then Attach database. Locate the database in the new location in the popup window and attach the database. Once the databases have been attached to the SQL Server instance you can restart the vCenter services to get vCenter up and running again. This whole process should only take an hour or so depending on how large your vCenter databases are.
Don’t forget about the update manager database and cache folder. You’ll probably want to put the update manager database on the same LUNs as the other vCenter databases, which is fine. The cache how ever should go on another LUN as it’ll take a lot of space, and we don’t want all that space causing space problems for the vCenter database files. Also you’ll probably want to use fast storage for the vCenter database as the data within that database is always changing and slower SATA storage for the update manager’s cache files as they are static are almost never written to or read from so you don’t want to waste expensive storage on storing gigs of update files.
Hopefully this information helps you build a more tuned VMware vCenter server. If you have questions feel free to contact me, we can probably get them resolved pretty quickly.
My Storage and Virtualization Class, which is available on demand, has started but you can still sign up for this great class and watch all the videos and get all the training to get you up to speed on Enterprise Class Storage and Virtualization so that you can get the best possible performance from your systems at work, and have an intelligent conversation with your storage and virtualization admins.
The course cost is $169 for full SSWUG.ORG members and $199 for non-members. The cost of the DVD with purchase of the course is an additional $199. The DVD costs $399 if you do not purchase a course seat. Course downloads, office hours and the ability to email questions to the instructor are included with course purchase only.
Class attendees will have access to me via a live chat daily (once I get back from Europe next week), through out the month of the class, to get all their questions answered.
This week I spoke at two great events. The first was SQL Zaterdag (SQL Saturday in Dutch) and the second was SQL Server Days. These were both great events that I am so happy to have been a part of. For those that attended SQL Zaterdag you’ll find my slides for those sessions on this other blog post. Below you’ll find the slides for the sessions I did at SQL Server Days.
I hope that everyone enjoyed these two events as much as I did. I look forward to seeing all my crazy new European friends at the SQL PASS summit or the next time I make it out to Europe.
Microsoft and Quick Start have teamed up to present a SQL Server 2008 MCM training class, which is being delivered online so you can view the classes from anywhere without the expense of flights, hotels, etc. There are 14 classes, two hours each which will be delivered on Tuesday and Thursday afternoons running from November 29th, 2011 through January 19, 2012.
Week 1 – November 29 and December 1, 2011 Recoverability and integrity
Week 2 –December 6 and 8, 2011 Manageability
Week 3 –December 13 and 15, 2011 Security
Week 4 –December 20 and 22, 2011 Performance
Week 5 – January 3 and 5, 2012 Availability
Week 6 – January 10 and 12, 2012 Developer support
Week 7 – January 17 and 19, 2012 Scalability
The presenters for these sessions are all top notch presenters. I will be delivering the Manageability and Security sessions and other presenters like Robert Davis (SQL Server MCM and MCM Project Manager) will be delivering the other sessions.
You can read the details of the various sessions on the QuickStart site which is setup for this very special set of classes. The cost for his great set of classes is only $2495 which includes access to tons of lab material. If you don’t want access to the lab material then you can register for the classes only you can sign up for just $1500. Personally I would recommend getting the classes and the labs to get the most benefit from the classes.
Hopefully I’ll see you December 6th.
I had a great time at SQL Zaterdag over in Amsterdam over the weekend. It was great seeing everyone make making some great new friends. It’s just amazing what the SQL Server community can do.
For those that requested copies of my slide decks you can download them here.
So as a followup to My SQL Server 2012 licensing blog post I wanted to follow up on what exactly is a core license good for.
When working in the physical world it’s pretty easy to figure out. However many cores there are on the physical chip is how many cores you need licenses for. If you’ve got one of the new 12 core Intel CPUs with Hyper Threading enabled you need 12 core licenses for that CPU.
When we get into the world of VMs that is where things get a little more iffy. We are no longer concerned about physical cores on the socket but now we are licensing based on the number of vCPUs which have been presented to the guest OS. If you have the same 12 core CPU that I just talked about, but you assign all 24 logical processors as vCPUs to the guest you have to license for 24 cores for that VM. For those of your doing the math, yes this means that you have to double license the cores when working in a VM.
While I’m sure that this isn’t the best news that you’ve ever heard, don’t forget that if you are running really large VMs like this you have a few options to help yourself out. First of all don’t forget that to run a 24 core SQL Server you have to be running Enterprise Edition (Standard edition only supports 16 cores in SQL Server 2012), so if you already have an Enterprise Agreement and Software Assurance you can probably license at the host level instead of at the guest level and save yourself some licensing money.
If you plan on running larger VMs running SQL Server you may also want to look at disabling Hyper Threading. You will probably be running the physical CPUs hot anyway and Hyper Threading may just get in your way anyway. This also reduces the number of cores that you can assign to the VM, which reduces your license costs for that VM.
Last week on Wednesday night I had the privilege of presenting a session to the Buena Park .NET User Group. The session that I gave was “Indexing for the .NET Developer”. The abstract for the session is:
“In this session we will be looking at the best and worse practices for indexing tables within your SQL Server 2008 databases. We will also be looking into the new indexing features that are available in SQL Server 2008 (and SQL Server 2005) and how you the .NET developer can make the best use of them to get your code running its best.”
If you would like to download the slide deck that I presented from you can download it here.
I hope that you enjoyed the presentation. I look forward to seeing you at my next presentation.