Server Discovery

Need a quick and easy way to gather basic information about the SQL Server instance you are on?  Keep this script in your SSMS Template Explorer and the information is only a double-click away.  This script works for SQL Server 2005 and SQL 2008 only because it uses the DMV sys.dm_os_sys_info.  If you are on SQL Server 2000 just highlight the code down to the comment and execute.

SELECT
    @@ServerName                    AS ServerName
    ,Case Left(Convert(Varchar(128),SERVERPROPERTY(‘productversion’)),2)
        When ‘8.’ Then ‘SQL 2000’
        When ‘9.’ Then ‘SQL 2005′
        When ’10’ Then ‘SQL 2008’
        Else ‘Unknown’
        End                                As SQLVersion
    ,SERVERPROPERTY(‘productlevel’)        As ServicePackLevel
    ,SERVERPROPERTY(‘productversion’)        As BuildNumber
    ,SERVERPROPERTY(‘edition’)            As Edition
    ,SERVERPROPERTY(‘LicenseType’)        As LicenseType
    ,SERVERPROPERTY(‘NumLicenses’)        As NumLicenses   
    ,CASE SUBSTRING(@@VERSION,PATINDEX(‘%Windows NT%’,@@VERSION),15)
            WHEN ‘Windows NT 5.0’ THEN ‘Windows 2000’
            WHEN ‘Windows NT 5.1’ THEN ‘Windows XP’
            WHEN ‘Windows NT 5.2’ THEN ‘Windows Server 2003 or Windows XP (64-bit)’
            WHEN ‘Windows NT 6.0’ THEN ‘Windows Server 2008’
            WHEN ‘Windows NT 6.1’ THEN ‘Windows Server 2008 R2 or Windows 7’
            ELSE ‘Unknown’
        END                            AS WindowsVersion
    ,CASE WHEN SERVERPROPERTY(‘IsClustered’) = 1
            THEN ‘Yes’
            ELSE ‘No’
        END AS IsClustered
–For SQL Server 2K run to here
    ,cpu_count                            AS LogicalCPUs
    ,cpu_count / hyperthread_ratio            AS PhysicalCPUs
    ,max_workers_count                    AS Threads
    ,physical_memory_in_bytes/1048576        AS PhysicalMemory_MB
FROM  
    sys.dm_os_sys_info

Here is an example of the result set on SQL Server 2008:

Results

Advertisements

Philergia –

Philos (Love)  +   Ergos (Work)  =  Diligence

Not a popular virtue in our day but one needed for success.

“Diligent hands will rule but laziness ends in slave labor.”   –Proverbs 12:24

Spelunking for SQL Server Using PowerShell

In a new environment I find I waste a lot of time trying to figure out what everybody already knows. For example, what SQL Server instances are running on a given machine? Is Reporting Services running on this server?

Here’s a handy little script you can paste into a PowerShell window and see what’s on any computer. With a little tinkering you can get it to work on a list of servers — but that’s for another day. Here’s the script:

get-service -computername DWHSQL1`

-displayname “*sql*” `

sort-object -property DisplayName `

format-table -auto

And the results:

Status Name DisplayName
Stopped TSSQLPublisher$Default DM TS SQL Publisher [Default]
Stopped TSSQLListener$Default DM TS SQL TCP/IP Listener Service [Default]
Stopped MSSQL$DWHSQL2 SQL Server (DWHSQL2)
Running MSSQLSERVER SQL Server (MSSQLSERVER)
Stopped MSSQLServerADHelper SQL Server Active Directory Helper
Stopped SQLAgent$DWHSQL2 SQL Server Agent (DWHSQL2)
Running SQLSERVERAGENT SQL Server Agent (MSSQLSERVER)
Running SQLBrowser SQL Server Browser
Stopped msftesql$DWHSQL2 SQL Server FullText Search (DWHSQL2)

As you can see, there are two instances on the server but only one is running, the default instance, DWHSQL1.

If you need to look at another server simply change the computername. You can modify the filtered column (displayname) to suit your needs as well.

You could get the same information by remoting in to the box and looking at services, but this is quicker and easier, especially when you are dealing with several servers.