Preparing for the SQL Summit 2008

I’ve started to produce my presentations for the “SQL Summit 2008”. It is always fun to dive down into the details about some certain feature. For instance one of my presentations is about policy based management, which will also include a few words on configuration servers and also news in general in SSMS. So I took the time to look around in SSMS for new stuff and I found surprisingly much. This is one of the upsides with speaking, you need to take your time to actually study a broader area about a subject, and then dive down into details.

The SQL Summit 2008 will take place from Oct 6 to Oct 10, over 4 cities in Sweden (Umeå, Malmö, Göteborg and Stockholm). I’m happy to be in good company among other speakers like Per Westerlund, Roger Cevung, George Thörngren and Patrik Löwendahl. And I’m really happy that the keynote (and other presentations) will be delivered by my good friend Kalen Delaney. The slightly sad part is that one my my presentations is scheduled at the same time as one of Kalen’s. But who knows, perhaps my room will be empty <g>?

Check it out at SQL Summit 2008.

Rebuilding msdb on SQL Server 2008

Because of the problems I had removing Data Collector I decided to rebuild msdb. You probably heard about instmsdb.sql, but it was a long time since I actually used it. I asked about rebuilding in the MVP group and Dan Jones (MS) pointed me to a Blog post from Paul Randal on how to do this on SQL Server 2005. Here’s Paul’s blog post:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx

Since above is for SQL Server 2005 I realized that it might not work smoothly on 2008. And It didn’t. Below are some of the things I discovered (also posted as a comment on Paul’s blog). Read below in light of Paul’s blog. I should also say that nowhere does Paul states that his instructions work on 2008. It was me taking a chance. 🙂

You need to add startup parameter -s <instancename> if it is a named instance. Now, this I knew, but for the sake of other potential readers…
I initially started the instance from the Windows services applet by adding -T3608. That didn’t allow for detaching msdb. So I started from an OS command prompt and also added -c. This allowed me to detach msdb.
I now ran instmsdb, but that produced a number of errors. Here are a few comments about some of them:
* Complaints on xp_cmdshell. I did try enabling this first and then ran instmsdb again but same result.
* Bunch of errors when creating various Data Collector objects. This wasn’t good, because cleaning up DC was the reason to rebuild msdb in the frist place.
* 3 errors about sp_configure and -1 wasn’t allowed value (two for Agent Xps and one for xp_cmdshell).
Just for the sake of trying, I now tried to connect to the instance using SSMS Object Explorer. But I now got some error regarding Agent Xp’s when connecting. I tried to explicitly enabling Agent XP’s using sp_configure but same error. When connected there’s no node in Objects Explorer for Agent.
I took this as an indication that Agent isn’t healthy. Whether it was me doing something fishy or it isn’t as easy as just running insmsdb.sql for SQL Server 2008 – I don’t know. But I’m in for a rebuild of system databases. This isn’t that bad since it is a just a test machine. But these issues might serve as example why you want to follow Paul’s initial advice: always backup msdb (also on test machines).

We’ve come a long way …

For various reasons I decided that I want virtual machines with older (pre-2008) SQL Server versions available on my various machines. For me, virtualization (in this case VPC) is great:

  • I rarely use these installs, most often I just boot it and check some detail.
  • I don’t have to litter the host OS.
  • I don’t pay anything (performance) in the host OS, except for some disk. The overhead for an extra XP install is some 1.5 GB which nowadays isn’t that bad.

So I did a several copies of my XP VPC folder (I don’t do diff drives for various reasons).

And then started with installing SQL Server 2000 (I already had VPCs with 2005). I do work with 2000 now and then, but I mainly use SSMS to connect to 2000. So it was a bit of flashback to play around with EM again.

Next was 7.0. OK, 7.0 didn’t look that different from 2000…

Installing 6.5 was more fun. I had forgot for instance that SQL Server Agent was called “SQL Executive” back then. Also, Enterprise Manager was a totally different tool compared to 7.0/2000.

I decided to skip 6.0 since the 6.5 BOL is basically 6.0 BOL with added “What’s new” section. So having above 6.5 VPC for me also covers 6.0.

The most interesting part was to 4.21a for NT:

I first realized I made a mistake when copying the files from diskettes to CD – I put all the files in same directory. Setup expects some folder structure like DISK1, DISK2 etc. And since I don’t have the diskettes anymore, how to know what files go in which folder? What I ended up doing was to copy the setup files locally (a whopping 4.4 MB !) and modify SETUP.INF. Interestingly enough I did figure out how to modify the INF file successfully. Imagine doing that today – without knowing anything about the installation…

Anyhow, installation was successful and I checked out what tools we had. Hehe, this is where nostalgia kicked in. I already have a OS/2 VPC with SQL Server 1.1, but I can barely navigate that OS nowadays. And there were no GUI’s at all with SQL Server 1.x. Since I hadn’t seen SQL Server 4.x for many many years now, I think this was more fun than re-living 1.1.

What strikes you are of course the tools. Looking at the engine (using “Books Online”) you mainly see that a lot of todays functionality wasn’t there of course. But using the GUI makes it so much more apparent what was there and what wasn’t. And of course the whole feel of the GUIs were totally different.

The help file start page has some 9 buttons, for various sections like Datatypes, Expressions, LIKE and Wildcards, Transact-SQL Statements etc. No tree-like structure…

The release notes explain for instance that Extended stored procedures are a new thing and with that came SQL Mail.

What we nowadays call SQL Server Agent was called “SQL Monitor”.

The “SQL Administrator Win32” tool had some very rudimentary dialogs for looking at “devices”, “DB”, “Login” etc. There are some dialogs available from the menus like “Database Consistency Check”, “configure SQL Server”. I could not find for instance where to schedule backups with SQL Monitor…

The “SQL Object Manager Win32” tool wasn’t actually that bad. The “Objects” window list one row per object in the database and you can double-click it to “edit” it. Interesting enough I believe this is the first version where we had “Generate Script” functionality in the tools, for instance. Hehe, there’s even a GUI for assisting in creating a SELECT statement with rows allowing you to type text for the WHERE clause, the ORDER BY clause etc.

There’s a separate tool called “SQL Transfer Manager” which functionality over the years have been exposed in various places (EM, DTS, SSIS, DMO, SMO etc).

Back to reality. Firing up SSMS 2008 I realize how much has changed… The engine has so much more functionality. Perhaps only, say, 10-15% of what we have today we also had in, say, 4.x – if even that. Not to mention things like SSAS, SSIS, RS, etc. So, even though it was fun nostalgia to fire up an old version, I really enjoy being where we are today. 🙂

Code page backgrounder, courtesy of Erland Sommarskog

While browsing through the programming newsgroup today, I came across a post from Erland Sommarskog – a short backgrounder about code pages and collations. I’ve never seen code pages described so coherent and with so few words, so I asked Erland if I could quote his text in my blog (no, Erland doesn’t blog 🙂 ). So below quoted text is with Erland’s kind permission.

For those of you who want to know more about Erland or read some of his great deep-dive articles, check out http://www.sommarskog.se/.

“To start with, if we should be picky, there are no ASCII characters >= 128.
There are however lot of other character sets that defines this area.

Way back in the 80s vendors started to explore the area 128-255, and
about each vendor come with its character set(s). The contribution
from the IBM/Microsoft combo that ran MS-DOS was a number of code
pages, of which 437 was of their oldest. Later, they realized that
they did not support all languages in Western Europe, and they defined
CP850 which served Western Europe better.

Meanwhile, HP had Roman-8 and Digital had their DEC Multinational Character
Set. Eventually, ISO settled on composing a standard, and they worked
from DEC MCS – or DEC were smart to work from the ISO drafts, I don’t know
which. This resulted in ISO-8859 a family or originally eight 8-bit
character sets, which recently evolved into 15 sets.

By the time Microsoft divorced from IBM, they abandoned CP437 and
CP850 as the character set for Windows, and went with ISO-8859, at
least for Western Europe. Except that they added some printable
characters in the range 128-159 where Latin-1 has only control characters.
This became CodePage 1252, and CP1252 is the code page typically
used for 8-bit Windows applications on a computer installed in Western
Europe or the Americas. However, CP437/CP850 still lives on Windows
today; the command-line windows uses a so-called OEM character set which
is one of these.

If you have a Windows program that uses CP1252, and the server collation
is CP437, the client API will convert the data for you, so if you pass
for instance Ö which has character code 216 in CP1252, the byte that
gets stored in SQL Server will be another. When you retrieve data,
data will be converted in the other direction. However, since CP1252
and CP437 does not include the same characters, the conversion may
not be roundtrip. For instance, Å may not be in CP437, so an Å from
CP1252 will become A, and will remain A when you retrieve it.

<TiborComment>Here I removed a section which was only relevant for the newsgroup thread in question</TiborComment>

Finally, all collations have 255 characters for varchar, and at least
65535 characters for nvarchar.”

For those of you who want to dive deep in collations and such topics, check out http://msdn.microsoft.com/en-us/library/bb330962.aspx.

Analogy between SQL Server and operating systems

With SQL Server 2008 released, I was thinking back of earlier versions of SQL Server. And I decided to compare them to the MS operating systems. Not a point-in-time comparsion, like “SQL Server version x was released year a, which was the same year that OS y was released.”. I’m thinking more of the feel you have for the product. Why would anyone want to do that? I don’t know – for fun, perhaps? While writing below I realized that the comparsions/analogies worked better the older the product is. Perhaps a product need to be obsolete for us to have the sentimental feeling required for this type of comparsion? Anyhow, here goes:

SQL Server 1.x <-> DOS
(I do know it ran on OS/2, but again this is more about how you feel for the product.)
I know, perhaps not all fair, but think about it. We are talking about command-line environments, or at the best some full-screen character based applications (like edit.exe or saf.exe). And installation was floppy based where the product did fit on a couple of floppies.

SQL Server 4.x <-> OS/2 or Windows pre-95
I can’t decide here.
OS/2 had the merit that it wasn’t a bad OS, but almost no apps were developed for it (think back to version 1.2 and 1.3 and what it was at the time – and what it could have been), and it wasn’t a fun environment to work in. Windows pre-95 had the merit of being a GUI which, sort of, brought multitasking to the desktop – but what about robustness?
Same goes for SQL server 4.x. It was revolutionary in some sense, like: Imagine fitting a real RDBMS in a PC? Now smaller businesses can start using “real” RDBMSs. But OTOH, it was very unpolished. Remember the GUI tools? They were really Windows apps where some conversion tool converted them for OS/2.
So, I think it is a draw between OS/2 and Windows pre-95.

SQL Server 6.x <-> Windows NT 3.x or Windows 9x
Again, I can’t decide.
In one way, SQL Server 6.x was MS first “own” release. But OTOH, the Sybase code base was still there. MS mainly did tool stuff, along with some engine stuff (like ANSI SQL compliance). But it wasn’t a re-write of the engine.
This can compare to Windows 9x – the DOS heritage was still there, in some sense.
If you compare SQL Server 6.x to Windows NT 3.x you can also see similarities. NT 3.x was the first versions of the new revolutionary OS from MS. But it still looked like … old Windows – something you might compare with SQL Server 6.x enterprise Manager.

SQL Server 7.0 <-> NT 4
I was originally going to put Windows 2000 here, but after thinking a while, I decide for NT 4.
7.0 was the first version of the new architecture. A lot happened, where the engine was all re-written. New stuff was introduced (Profiler, DTS, Olap server). So, at the engine level, we basically got a more modern look-and-feel.
To some extent NT 4 was similar. You got a new GUI (adopted from Windows 9x). The revolution was that you now had an *stable* OS which you also could run as your desktop OS. I bet that many of you (computer nerds)/readers preferred NT 4 instead of Windows 9x at that time. I did. There were some architectural news in the OS as well, like the device driver model (some stuff were moved to kernel mode – if my memory serves me).

SQL Server 2000 <-> Windows 2000
Seems too easy, but think about it.
SQL Server 2000 was when the new architecture matured. IMO, a great release at that time. OK, some would argue that it didn’t happened that much between 7.0 and 2000, but maturing and polish of the new architecture is a major thing to me.
Windows 2000 can also be seen as becoming mature – ready to be used in masses. OK, there were some revolutionary new stuff like AD, but you can’t expect the analogy to fit 100%. 😉

SQL Server 2005 <-> Vista
Hmm, is my analogy breaking down here?
I was originally going to put Windows 2003 here. But that was a bit too much going chronologically hand-in-hand.
And I think that XP is a bit unfair (perhaps XP would be a better fit for SQL Server 2000?).
But 2005 did have lots and lots of changes and new features. And so did Vista. Vista has a rather slow adaption rate, and I have the same feeling for SQL Server 2005. Many people seems to wait for Vista+, a perhaps more cleaned-up OS? And some seem to be waiting for SQL Server 2008, even though perhaps not for the same reasons.

SQL Server 2008 <-> Vista +
This was unavoidable, considering how we got here. I won’t dwell into this, since it is too early to say how we feel about these releases in 10 years from now… 

Now why on earth did I write this post? Well, I have been doing some 6 full installations and some 12 database engine installations of SQL Server 2008 the last two days – so I’ve had a lot of time on my hands. 🙂

 

Endpoints, Netlibs, IPC and stuff…

Its been a while since my last post. No special reason, just a combination of lot of work and I didn’t feel I had something pressing to say…

This topic is basically on how the client app communicates with SQL Server. Not the API level (like ADO or ODBC), or the packet level (TDS), but in between. Basicaly we’re talking IPC, Inter Process Communication – in a SQL Server context:

Here’s how I understand it (I probably gonna get some points wrong and you are all welcome to correct me):

There are network protocols, such as:

  • TCP/IP (has routing functionality of course)
  • NetBEUI (very limited, if any, routing functionality)
  • IPX (the original protocol for Novell networks)
  • SNA (mainly used in IBM mainframe and such environments)

A network protocol is of little use if we can’t send data back and fort between application over that network protocol. So, there are APIs to facilitate IPC:

  • NETBIOS (originally developed for NetBEUI, but is also supported over IP (requires WINS or LMHOST for name resolution))
  • Sockets (not available for NetBEUI AFAIK, only TCP/IP)
  • Named Pipes (built on top of NETBIOS)
  • RPC (implemented and available over both NetBEUI and IP)
  • SPX (as I understand it, the API over IPX)
  • APPC (program-to-program protocol over SNA)

When MS released “their” SQL Server, they needed a way for the client app to communicate to the server. They decided to go for Named Pipes and developed what we call “netlib”. I.e., the MS deveopers used the Named Pipes API (which is similar to reading and writing to a file from the programmers perspective) when developing the Named Pipes netlib.

Over time, new netlibs were developed, where in SQL Server 2000, this culminated in below list:

  • Shared Memory (only for local connections, obviously) 
  • Named Pipes
  • Sockets
  • RPC
  • VIA
  • SPX

There was never a netlib deveoped directly on top of NETBIOS, but indirectly through Named Pipes. Named Pipes uses NETBIOS, which available over IP, and hence is routable. In 2005, the list has shrunk to:

  • Shared Memory 
  • Named Pipes
  • Sockets
  • VIA

And I have a feeling that in the end Named Pipes will go away. I don’t have any experience with VIA, but I believe that it is closer to the metal than Sockets so it might stick around for dedicated AppServer-to-SqlServer networks.

So, what does above have to do with Endpoints? Well, MS are categorizing netlibs as endpoint nowadays. This makes sense since the netlibs are a “way in” to SQL Server, as are HTTP, Service Broker and Database Mirroring endpoints.

No Notification Services in SQL Server 2008

If you have been wondering where Notification Services (NS) is in the prior CTPs of SQL Server 2008, you now have the answer. NS will not be carried forward to SQL Server 2008. Here’s a quote from July CTP readme:

“SQL Server Notification Services will not be included as a component of SQL Server 2008, but will continue to be supported as part of the SQL Server 2005 product support life-cycle. Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases. ”

So, now we know.

I’ve been delivering training of SQL Server 2005 for a while now, and one of the courses has a module on NS. Over time, I gradually adapted to the fact that almost no-one was interested in NS and nowadays I just give a brief overwiew of what NS is, and the basics of creating an NS solution. Funny thing is that when you “get” NS, you realize that it is a neat piece of infrastructural software, taking writing some code off your hands. I guess that there hasn’t been enough interest in NS to carry it forward to next release, but that it pure speculation from my side…

Leaking roof and file shrinking

Shrinking and growing database files cost you resources and have other side effects as well. I have a technical description  http://karaszi.com/why-you-want-to-be-restrictive-with-shrink-of-database-files if you want to get into details about it. Sure, you can have valid reasons to shrink, after archiving lots of data and needing to free some disk space for example. But even after pointing to my article I see people doing this at a regular basis, hence this analogy. The analogy uses transaction log file as example, but the same basic principles applies to data files:

Say you have a leaking roof at the office. You need to put a bucket for the water under the leak (the bucket is your database file, a log file, .ldf, for this analogy).

Ever so often, you need to empty the bucket (backup log if in full or bulk logged recovery model, or when checkpoint occurs if you are in simple recovery model). Emptying the bucket doesn’t make the bucket smaller. The bucket still consumes the same amount of cubic cm of your office space.

You can of course have one of the office workers (the machine resources) take an angle grinder and make the bucker physically smaller when you empty it. This consumes resources. But you would now have an office worker to use a welder, perhaps every 10 minutes or every hour to make the bucket larger, as you have more and more water in the bucket.

I don’t know anyone who had a leaking roof, but if I were in that situation, I sure wouldn’t make this bucket larger and smaller all the time. I’d use a bucket with a size necessary to accommodate the water that builds up between when I empty the bucket. This is how I handle my database files as well.