How Do You Determine What Operating System & Version of SQL Server You’ve Got Under Management?

As a new DBA, or if you’re consulting and you need to quickly determine what environments you’re working in, one of the first things you’re going to do is to document (that’s right, you’re going to record this somewhere! it’s one of those “best practices” things…) each server under management.

So how do you do that?

Windows Version: the how-to will vary from opsys to opsys, but essentially, from the start menu, select RUN and type in winver.  For newer versions of the opsys, just type winver into the search box directly above the Start button. The pop-up box shows you just about everything that you might want to know about the operating system except for one very important thing — is this a 32-bit system, or a 64-bit system?

32-Bit or 64-Bit: you’ll have to dig a little deeper, by going to the System report. From the start menu, select RUN and type in system, or type system into the search box just above the Start button. If you have to select your computer by name, you’ll see an option to View System Information — that’s what you want. Hint: if it doesn’t say x64 or 64 Processor or something like that, you’re dealing with a 32-bit system. Hint-hint: if you’re on a 64-bit machine, then the SQL Server instance(s) will also be 64-bit…the opsys and SQL Server have to match.

SQL Server, how to tell which version you’re dealing with: Open Management Studio (I’m going to pretend that everyone is now on SQL Server 2005, although I, myself, support one instance of 2000), connect to a SQL Server instance, open a query window, and type in one of these two queries:

select @@version  — the old standby, and yes, it’ll work on SQL Server 2000, also. The output looks something like this:

Or, you can run this query:
 SELECT SERVERPROPERTY(‘productversion’) AS ProductVersion,
          SERVERPROPERTY (‘productlevel’) AS ProductLevel,   
         SERVERPROPERTY (‘edition’) As ProductEdition

— by specifying various server properties and giving the output column names, you can produce something that looks more like a real report.

And there you have it!  Yes, there’s lots more to find out about your servers under management, but this is a place to start.