So a two-for for the first day.
One thing I find going in as a DBA to smaller companies is that the SQL versioning across the site is very rarely uniform. I think the reason for this, is that there are quite a few different versions of SQL, and unless you delve deeply, they all look the friggin same. They all install, have management studio, and run database stuff. What more do you need?
Well as you progress, you learn the important differences between the versioning, the reasons for needing to know, and how you can use the knowledge to educate your development team and get everyone on the same page.
The list of differences is quite significant from top to bottom, but for the general admin, the important breakdowns as I see them are as follows :
Enterprise Edition
The daddy of them all. Does everything SQL is capable of doing, no features are disabled, and can take more RAM than I’m able to count. However, the cost can be prohibitive for a smaller company as it is licenced either per CPU core, or using CALS. For a database tracking the fantasy football league, you could probably go for the cheaper version. However if you are implementing high availability clustering, large volume databases, and operating a DR site using SQL mirroring, this is the one to go for.
Key additional features
- Supports file partitioning within a database (for performance and storage across multiple volumes). If you want to read more check out the MSDN Resource on it right here
- Support asynchronous mirroring. Important for high volume databases to prevent your log files/replication getting stuck. MSDN goes into more depth again right here
Standard Edition
The slightly scaled down enterprise version. This is the one the majority of projects would use. It has limitations on RAM/CPU, and doesn’t have some of the cool features of Enterprise, but it does support synchronous mirroring, full replication features, and no other major restrictions.
Developer Edition
This is where you need to be careful. Developer Edition is EXACTLY the same as Enterprise Edition, however it is NOT supported for production use. What this basically means is that a development team can implement all of the Enterprise Edition specific features without telling you, and when you try to set it up on Standard Edition, as you are the good cost saving admin, it will bomb. Take note!
There are many other versions available, but if you’re using them, I’m sure you’ve done your research. But in a standard office environment, they are the 3 most likely to appear. If you have to choose between learning what each version does, or what each version doesn’t do, I’d go with the latter. It will allow you to interact with the development team at an early stage to decide exactly what functionality they are planning to use, and whether your system is actually equipped to handle it. This will save everyone a lot of time, and potentially a lot of money. You won’t get the money you saved, but it might go towards that 70″ “Monitoring Station” you’ve been denied in the last 4 budgets.
My second script for the day is a simple one again, but is infinitely useful. It is used if you have built a server, installed SQL on it, and then realised that you can’t really put a server named “Awesome-tron” into your production network, and have to rename it.
The problem is that while Windows will update itself with the new name etc. SQL will not. Which means if you try to set up replication, or any service where SQL needs to talk to itself via its host name, it will fail with an error along the lines of “Cannot find server ‘Awesome-tron’, could not complete command”, leaving you screaming at your newly named “Professional-Bot” server. SQL also requires that you rename it properly, and it can be done with the following script. Just make sure to run each line individually.
Select @@Servername (shows you current servername, should be ‘Awesome-Tron’
Sp_dropserver ‘Awesome-tron’ (Or Equivalent)
Sp_addserver ‘Professional-Bot’, ‘local’
Select @@Servername (Should now be ‘Professional-Bot’)
