I spent the day today in class, learning about how best to prepare for an upgrade to SQL Server 2005 (SS2K5). The class is being held about 25 minutes from the office, just outside Waterbury. I was unfamiliar with the company offering the class, but decided to give them a try.
The setting was remote, but the facilities were adequate. They seem to have a lot of space there, and they even go out of their way for you. For example, lunch was provided (and even catered). It wasn’t the best food, but it really is the thought that counts. Some places would not even think to go out of their way for you, and it made an impression on me. Then again, free food always does.
The instructor was knowledgeable, having been working with databases for about thirty years, all the way back to his days at DEC. If you have to ask what that is, don’t. And when he started to run down the outline for the day he made a comment about how “upgrading an existing server is the least desirable option”. It was at that moment I knew I was in the right place. With the exception of the power going out for my half of the room (twice), the day was smooth sailing. It is not often that I sit in a class and I am comfortable with everything being discussed, but after playing with SS2K5 for the past few months there were time I felt I could be leading the discussion as well.
Some of the quick facts I picked up during the day include:
- In SS2K5 the msdb database has a ‘suspect pages’ table, which is how you are able to recover a corrupted database, which is rather cool. I have not dived into the details of this, but the whole concept of a page level restore is interesting.
- We will need to purchase a few books for the DBA team in the near future, as we will have to upgrade our libraries with some new bibles.
- Query Analyzer (from SQL Server 2000) will connect to an SS2K5 instance. So, between that, ApexSQL, and Rapid SQL, our developers have no need to ever ask for SQL Server Management Studio (SSMS)
- Microsoft is big on the four letter acronyms for everything (SSMS, SSIS, SSRS, SSAS, etc.) OK, I learned that in Barcelona, but it came up again today.
- Named Pipes and VIA protocols are disabled by default. Only Shared Memory and TCP/IP are enabled.
- The SQL Server Configuration tool (not to be confused with the Surface Area Configuration tool) is where we would set our startup parameters along with other general server settings.
- We should download the latest SS2K5 books on-line (BOL), as there is a lot of valuable information there with regards to migration do’s and don’t’s.
We covered quite a few topics and scenarios on the day. There was some good news, some not so good news, and just some news. I will do my best to recap what was discussed, as well as just some general thoughts. If you were looking for a sleeping pill, the next few paragraphs may be just what you need.
Database snapshots should be a very useful tool for our group. We should outline a process where before migrating a change to production we take a snapshot of the database first. This would allow for an easy rollback should the change need to be backed out. The advantage is that the snapshot does not take up as much space as a full backup. I suppose we should already be in the habit of doing a backup before migrating a change, but in some cases that is just not feasible (QMetrix). Snapshots makes it feasible for almost every scenario, with the only exception being an upgrade that touches every object (QMetrix again). But if the change is not updating the data, it would still be faster and easier to do the snapshot, I believe. Even better than that, what about systems that want a database for reporting (hello Antares). We would not need to do that backup and restore every night, we could just rip off a snapshot for them to use.
Database mirroring is now available with the release of SP1 for SS2K5, and we should look to utilize this to replace our one and only cluster, Portia. We can save some money (always a positive thing), and possibly even increase our high availability for that system in the process. A win-win if ever there was one. We just need to get under the hood and see how it works.
When it comes to the actual upgrade, there are a few options available. In my opinion, the recommend practice that should be used is as follows:
- Rename all user stored procedures to be two-part names (dbo.proc_name). This is to assist with any possible problems with regards to SS2K5 use of schemas. By renaming the procs we can make certain they have the right schema from the start.
- Install SS2K5 on a new server.
- Run the upgrade advisor (UA), and correct as much as possible.
- Migrate databases from SQL 2000 (either backup/restore or detach/attach).
- Adjust compatibility mode to 90 (by default, it is left in 80 mode).
- Cross fingers (and toes).
Someone will need to explain to me why the databases are left in 80 mode after a migration. It makes no sense, except that it is consistent with an in-place upgrade. I can take a database, restore it to SS2K5, set the mode to 90, and get no errors until I try to run a statement that is no longer supported. So, what is the point to leaving it in 80 mode? Sure, the statement would then work, but would it not be better to only allow me to set the mode to 90 if the db was ready for that version? It just seems like either something is being missed, or I found a hole that cannot be covered. The instructor had no explanation for it either. Ok, I am babbling again. Back to the program.
There was a brief discussion about renaming the ‘sa’ account for some extra security. So, that got me thinking. Why not name it ‘su’? Or, better yet, go Greek and use ‘mu’ or even ‘nu’. Then, it hit me. We should rename the ‘sa” account to ‘Omega’, because it is the last thing we should ever be using to connect to the server. Not sure how that would affect other operations, especially internal ones, or even the Dedicated Admin Connection. But I do know how it would affect everyone that keeps trying to log into our servers as ‘sa’.
At this point, the instructor displayed some documents he had that related to SS2K5. I was about to email them to myself, even though they were 13mb in total size. I started the process only to stop and think ‘why use email when I can use USB?’ I then grabbed the USB chip given to me for being a PASS volunteer (how often has PASS been there for me? Too numerous to count), ripped the docs to the USB, and have already saved them to my laptop, as well as to the DBA Corner at work.
SS2K5 has many changes with regards to the database engine. There are a lot of changes to T-SQL, as well as some statements that are no longer supported (what will we ever do without the DBCC TEXTALL statement?) I am expecting the UA to catch 99.9% of these syntax issues including such things as some now reserved names (sys, bulkadmin, etc.), and unsupported syntax (no more outer joins with =*). These issues should be easily solved, as there is what appears to be sufficient documentation on replacement syntax for most items.
The ones that have no replacements are usually the undocumented things you were not supposed to be using anyway (sp_EventLog).
We also discussed how to upgrade an instance of Analysis services. This is where the bad news comes into play. You can follow the same upgrade scenarios as a SS2K5 server, but with SSAS you may just want to rebuild your cubes from scratch. Ouch. It is one of those things that will simply take some analysis to figure out which route is best. Get it? Analysis? Yeah, I may be drunk, but if you are using Analysis services right now, then a little more analysis is just what you deserve anyway.
The easiest upgrade discussed today would be reporting services. The only two items I saw as potential pitfalls were the use of custom extensions and the use of the ‘me.value’ syntax. If we have neither of those, then everything should be just fine. You can easily migrate your existing reports, and there was even a tool used to script out the report and migrate it over. Honestly, it really does look to be simple. So simple, I am thinking we could roll out SSRS before anything else. Maybe even next week.
That is enough for today. I will post more tomorrow after Day 2.
