[ad]
So over a month down the line, our SQL2005 upgrade project should now be in the workable prototype stage. But as with all things that “should” be(More security in IE, Great Britain ruling the world and my kitchen being fitted), it’s not, it’s not even close. On top of this our company is currently undergoing some “painful but neccessary steps to streamline our profitiablility in the european market”. In other words, lots of people are about to get the chop. Anyhow, on with the analysis.
SQL Server 2000 -> 2005 upgrade tool.
Overall I’m impressed with the upgrade tool, it made a fine job of upgrading our code and data, with almost everything going straight into 2005. All our DTS’s were wiped as expected, and our custom written security mod was discarded as a “fault” in the 2000 install(Not a big deal), but everything else looked fine. Little were we to know a shitstorm was about to start when we released the 2005 run site to a small group of testers. As a constant piece of self-evaluation we allow some users to run there own SQL code, it’s nothing major, just simple “Get this from here” stuff, but it allows us to monitor what users can access and when we have to change security or file flow we can be sure that normal users cannot access sensitive data. Unfortunately 2005 didn’t have the same notion of security that we do, and decided that encrypted fields that were created using our custom mod weren’t really that important, so it unencrypted them all using our mod(Hang on, I thought our mod was a “Fault”?) and then removed the permissions, allowing users to get direct access to the data. That’s a bad thing. So we pulled the plug immediately and scrapped the whole server, experiment over.
We learnt a couple of important lessons there, the main one being, dont trust the update tool. It un-encrypted the data without informing us, and removed permissions without raising an error(Allthough the permissions removal was later found buried in the upgrade log).
Initial impressions
There was some fairly impressive(From an MS point of view) changes to how SQL installs that caught our eye, namely the large number of components and features that were disabled by default. Not least XP_cmdshell, that is generally used to execute external programs or hack into sql databases. About fucking time too.
If your an MSSQL2000 regular you’ll be hoping to just boot up 2005 and have your permissions all working, but unfortunately its not that simple. The security model has changed radically, and your going to have to work a lot harder to keep things secure, but the means to do so have actually been provided this time. With principals and securables being included this time around, you will have to be a lot more careful, but once your in the know your a lot more secure. As always the best place to read up on this stuff is the MSDN, particularly this section on the changes between 2000 and 2005.
Enterprise Server Pricing
While I’m harping on about how great MSSQL2005 is, a lot of you are sat there wondering why were not using Oracle. Well the price is the the main reason, and I was going to have a detailed breakdown of the difference in costs between MSSQL2005 and Oracle with our current setup. But as a friend of mine quite rightly pointed out our setup could be radically changed by deploying Oracle, with us maybe needing less servers and therefore less licenses. So I’ll work on the principle that were upgrading to an identical network, but its not a 100% accurate comparison.
MSSQL2005 has a fairly simple licensing scheme, with no issues involving DC or HT chips, and a clear definition of what a “user” is and where that user can access the data from. On average a 1 processor license of SQL Server standard will set you back £4500GBP($8300USD), which is a tiny cost for any medium to large company. If your a fairly small company you can get a 5 CLT(Not to sure what the acronym is, but its a Client Access License) for around £600GBP($1100USD). Now for us we would be looking at per processor, and we have 23 processors running SQL2000, with the rest of the boxes using MSDN versions for development. So in total for our entire setup to go 2005 it would cost us £103500GBP($192000USD), which is again a fairly small amount of money for us to spend on replacing our entire database setup.
Now, Oracle. Its a little bit harder to find out what Oracles charges, and I’m not going to go into the details, you can find all the relevant info on there website if you wish to check what I’ve come up with. I’ve used the price offered by oracle themselves for a perpetual processor license(£23236GBP($42996USD)), but oracles pricing is per core for there enterprise product, and considering nearly all our servers run on xeons, were looking at a hefty bill. In total we have 43 “Oracle” processors, giving us a total bill of £999148GBP($1900000USD). Yes, thats almost one million pounds. Again thats not an enormous amount of money for a company our size, but when your compairing the two side by side, you have to wonder where all that extra cost comes from.
For next time
Round 3 will involve us upgrading one of our smaller and less mission critical databases(IT Support) and trying to switch over. Then we can have a bash at breaking it.