Recently, the IT department at the office undertook a hardware technology refresh cycle. To facilitate the IT department, we were required to send out a list of all installed programs & supporting components so that they could provide us with new computer systems up-to-date with the right versions and patches.

That was the time when I discovered something interesting. I found a quick way to get the following information about SQL Server client tools right from SSMS:

• SSMS Build Number
• Analysis Services client tools build number (if applicable)
• MDAC version
• MSXML version
• IE version
• .NET framework in use
• Operating System version

I simply browsed out to Help –> About:

Once there, I noticed that all the information is now available on screen. So, I clicked on “Copy Info”, pasted it to Excel and sent it over to my IT department. They were surprised as to how fast I got this information for them. Well, it’s our little secret now, isn’t it?

Until we meet next time,

Be courteous. Drive responsibly.

Today I move away from SQL Server for one day, and instead share with you a productivity enhancement that you can use in your day-to-day work.

Some time ago, we were working on fixing a defect, but were unable to reproduce it. The QA team who had found the defect was sitting at a different building on the other side of the city, so moving over for a quick stop-over was not quite feasible. It turned out that while the end result was the same, the two teams were using slightly different steps to reach to a particular window. That day, we wished to have some sort of a built-in (i.e. “free”) recorder that the QA can use to record the steps they follow and then share it with us so that we could follow exactly the same steps and attempt to reproduce the defect.

Well, Windows 7 does come with such a feature/utility built-in. It’s called the Problem Steps Recorder – PSR.

To get an overview of the PSR, you can visit the Microsoft site: http://windows.microsoft.com/en-US/windows7/How-do-I-use-Problem-Steps-Recorder

There is also a very thorough, quick demo (just 3 minutes long) on TechNet at: http://technet.microsoft.com/en-us/windows/dd320286

The immediate uses that I see of the Problem Steps Recorder (PSR) are:

1. Helps generate workflow-related documentation
2. Helps in exchanging information between geographically remote teams
3. In a Quality Assurance setting, using the PSR eliminates the need and resources to review the “steps to reproduce” a particular bug because these would be automatically generated by the PSR
4. PSR allows saving to universally acceptable document formats – allowing easier exchange

In addition to the PSR, there is often the need to snap a portion of the desktop screen and send it over in an E-mail. This can very easily be done using the Snipping Tool (available since Windows Vista), a demo for which can be found here – http://windows.microsoft.com/en-US/windows7/products/features/snipping-tool

So, these are the two productivity enhancements that my team & I use at the workplace. I trust that they would help you exchange information across multiple teams at your workplace as well.

Until we meet next time,

Be courteous. Drive responsibly.

I am sure that when preparing installers for your products, you would have come across a requirement to filter out the system databases deployed on a SQL Server instance when presenting a list of available SQL Servers to the user.

System databases, per Books On Line (http://msdn.microsoft.com/en-us/library/ms178028.aspx) are the following:

• master
• msdb
• model
• tempdb
• resource

In addition, the distribution database in a replication topology is also a system database (It appears within the “System Database” node within object explorer in SSMS).

It appears that there is always a lot of confusion around the question – “How to identify system databases programmatically?”. The unfortunate answer to this question is simply that Microsoft SQL Server does not have any documented method of identifying system databases besides filtering on the database name.

I use the following query (even in production code) and it has served me well. I therefore share it with you today:

--*********************************************************
--WARNING:
--These queries are provided "as-is" and without warranty
--The author, BeyondRelational.com and Microsoft are not
--responsible for damage caused by misuse of this query
--*********************************************************

--Provides a list of system databases
SELECT sdb.database_id,
       sdb.name
FROM sys.databases sdb 
WHERE CAST((CASE WHEN SUBSTRING(sdb.name,0,14) IN ('master','msdb','model','tempdb','ReportServer$') 
                      THEN 1 
                 ELSE sdb.is_distributor 
            END) AS BIT) = 1

--Provides a list of user databases
select sdb.database_id,
       sdb.name
from sys.databases sdb 
WHERE CAST((CASE WHEN SUBSTRING(sdb.name,0,14) IN ('master','msdb','model','tempdb','ReportServer$') 
                      THEN 1 
                 ELSE sdb.is_distributor 
            END) AS BIT) = 0

Note that this list does not contain the Resource database. That’s simply because it is not directly accessible as a separate database! The resource database, as you know manifests itself as the “sys” schema in any database and therefore cannot be queried outside of this environment.

I hope that the above mentioned queries are helpful to you.

Until we meet next time,

Be courteous. Drive responsibly.

If you work for a product based organization, you would know that databases created in versions as old as SQL 7 are still around. The only thing is that over time, they have evolved via upgrade programs which have upgraded them to the latest and greatest levels – running on latest editions such as Microsoft SQL Server 2008 R2.

Whenever you work with a production database for troubleshooting or for an upgrade, have you ever wondered about the SQL Server version in which a particular database created? Answer to this question would answer questions such as:

1. How old is this database?
2. Has this database ever been reconstructed? (Reconstruction meaning data from an existing database pumped to a new database)

Getting answers to these questions is not that difficult, provided you can run a DBCC command against a database. All that needs to be done is:

DBCC DBINFO('AdventureWorks2008R2') WITH TABLERESULTS

That’s it! Well, let’s dissect the DBCC command that we used:

DBINFO: This, to the best of my knowledge, is an undocumented DBCC procedure. This means that it does not come with any sort of warranty from Microsoft (and also none from this site and me). It is therefore advised not to use this in production environments.

Next up, as the name suggests, DBINFO provides “insider” information about the database – something that is not available regularly via viewing the database options or via any DMV. The details of each and every result option is not in scope of this discussion. You may want to follow posts on SQLSkills [Paul Randall (blog)] for insights into DBCC from the guru himself.

TABLERESULTS: Most of us would have used DBCC CHECKDB to verify the integrity of our database. We get a whole lot of information as the output. However, not all DBCC commands return an output. The various output formats for DBCC that I know of are:

1. Output to client (i.e. – display “on screen” as is the case with DBCC CHECKDB)
2. Log to SQL Server Log
3. Provide output via the use of TRACE flag – 3604 (When researching this post, I could not find documentation on MSDN for this flag. In such cases, please follow the assumption I take – it’s undocumented, and therefore not covered by warranty)
4. In addition, we have a 4th output format – output as a table. The WITH TABLERESULTS clause specifies the results in a tabular format that can be loaded into a table.

By default DBCC DBINFO does not return any results. Therefore, we either need to use option #3 (TRACE Flags) or #4 to display the output of the DBINFO command. In the query I have mentioned above, we used the TABLERESULTS option.

The result

When I ran the above mentioned query against the AdventureWorks2008R2 sample database for Denali CTP03, the following was the output:

If we look at the fields – dbi_createVersion and dbi_crdate, we can see that they indicate the SQL Server version in which the database was created and creation date/time respectively.

For the AdventureWorks2008R2 for SQL 11 (“Denali”) CTP03, the values are:

• dbi_createVersion = 679
• dbi_crdate = 2010-08-02 17:26:22.770

So, now we know that this database was upgraded from another version of Microsoft SQL Server (dbi_createVersion is different when compared to dbi_version). Also, we know that the database was created first in August, 2010.

I trust that the above information will be helpful to you sometime in the future.

Until we meet next time,

Be courteous. Drive responsibly.

For anyone who has run Microsoft SQL Server under a domain account, the first thing that comes to mind upon thinking about changing the account or the account password is planning for downtime. However, starting Microsoft SQL Server 2008, this does not have to be the case.

Microsoft SQL Server 2005 and below

In Microsoft SQL Server 2005 and below, changing the account password for the Microsoft SQL Server service normally involved:

1. Launching Microsoft Management Console (MMC)
2. Adding the Services snap-in (services.mmc)
3. Navigate out to the SQL Server service
4. Change the domain account and/or the account password
5. Repeat the same for all dependent services (like the Full Text Search service, etc)
6. Restart the SQL Server & dependent services

Restarting the SQL Server means downtime for your applications – a message which no administrator wants to take to the rest of the organization.

Microsoft SQL Server 2008 and above

Starting Microsoft SQL Server 2008, this is no longer the case. If the above steps are followed, then one might end up with at least one of the following issues:

1. Windows Registry permissions are not properly – this prevents the service running under the new account from reading SQL server settings
2. A service restart is required for the changes to take effect

If the following steps are followed, then the Microsoft SQL Server service does not need to be restarted and the appropriate Windows Registry permissions also set automatically.

The right way…

1. Go to Start –> All programs –> Microsoft SQL Server <version>
2. Go to Configuration Tools
3. Click on “SQL Server Configuration Manager”
4.  
   • Alternatively, you can run the following on the “Run” prompt:
     • SQLServerManager11.msc – For Denali CTP03
     • SQLServerManager10.msc – For SQL Server 2008
5. Within the SQL Server Configuration Manager, go to the “SQL Server Services” node
6. In the right-hand side pane, you will see all the SQL Server services listed
   1. Simply right-click on the required SQL Server Service and go to “Properties”
   2. 
   3. Next, change the domain service password and click OK
   4. 
7. Once the password has been changed – there is no need for the SQL Server service to be restarted

If ever you need a service restart

Let’s say you need a service restart because of a configuration change. Instead of heading over to the Services.msc console, one should be using one of the following options:

Using SQL Server Configuration Manager

1. Right click on the SQL Server Service
2. Click on “Restart”
3. 

Using SQL Server Management Studio

1. In the Object Explorer pane, connect to the server in question
2. Right-click the server name and click “Restart”
3. 
4. Click on “Yes” on the following Message Box that comes up (beware, this message box might appear in the background)
5. 

My observation

Old habits die hard. I have seen a lot of professionals (developers, especially) who still use the Services snap-in to manipulate SQL Server permissions and to restart these services. It is my recommendation to them to make a conscious effort to use the SQL Server Configuration Manager for these tasks.

For more reference on how to setup Windows Service accounts for Microsoft SQL Server, please refer: http://msdn.microsoft.com/en-us/library/ms143504.aspx

Until we meet next time,

Be courteous. Drive responsibly.