We are using upper case letters in the customer account numbers. But sometimes there are some human errors inputting the lower case in the account numbers. How can we identify them using T-SQL? It is quite simple. Just use the COLLATE Latin1_General_CS_AI criteria for the string comparison:

<span style="color: #606060" id="lnum1">   1:</span> <span style="color: #0000ff">SELECT</span> AccountNumber <span style="color: #0000ff">FROM</span> Customer 

<span style="color: #606060" id="lnum2">   2:</span> <span style="color: #0000ff">WHERE</span> AccountNumber != <span style="color: #0000ff">UPPER</span>(AccountNumber) <span style="color: #0000ff">COLLATE</span> Latin1_General_CS_AI

When you want to install additional components on a mission critical SQL server instance in production, you have to be very careful to find out which component requires SQL instance to be restarted and which does not. Otherwise, you will find your phone keep ringing. Here are two examples:

1) Database Replication doesn’t require SQL instance to be restarted. But it does need to restart the SQL Server Agent in order for it to work.

2) FULL Text Search does restart the SQL instance during the installation!!!

The new Microsoft Surface 2 has a new ad. Really like it. Especially the idea of “One” – One device for everything you need, One Microsoft

Microsoft Surface 2 Ad

If you are moving/restoring your TFS database from SQL Server Enterprise/Developer edition to SQL Server Standard edition, you have to disable SQL Server data compression first since this feature is only available in Enterprise/Developer edition. Here has an article on MSDN which shows how to do that: http://support.microsoft.com/kb/2712111

Following is a short excerpt from this article:

To disable SQL compression on a Team Foundation Server 2010 database you can:

1. Launch SQL Server 2008\2008 R2 Management Studio
2. Locate each Team Foundation Server 2010 database(Configuration and Collection databases), right click on the database and select new Query
3. Type: _EXEC [dbo].[prcEnablePrefixCompression] @online = 0, @disable = 1__
4. Run (!Execute) the query and verify success under messages
5. Repeat steps 1. to 4. for all required databases.

The below SQL query will provide you an estimate about the amount of additional disk space that would be required after compression has been disabled on a Team Foundation Server 2010 database.
_selectsum(used_page_count)* 8 * 2 /1024.0 fromsys.partitions p joinsys.dm_db_partition_stats s on s.partition_id = p.partition_id
and s.object_id= p.object_id and s.index_id = p.index_id
where p.data_compressiondesc =‘page’
Note:

1. The size returned by the above sql query is in Megabytes(MB).
2. You should run this query against each Team Foundation Server 2010 database(Configuration and Collection databases) before disabling data compression.
3. The SQL query should be run in the context of each Team Foundation Server 2010 database separately.

Some products install a hidden SQL server instance (usually express edition). If you check in Sql Server Configuration Manager, you can see SQL instance name like ‘MICROSOFT##SSEE’. But if you want to connect to it using SSMS (SQL Server Management Studio), you won’t be able to connect to it because the instance is set to hidden. Sometime, we have to connect to it to do some maintenance work as a DBA. So here is the way to unhide it. In SQL Server Configuration Manager, expand SQL Server Network Configuration, right click on “Protocols for MICROSOFT##SSEE”, choose Properties from the context menu. In the Flags tab of the Properties dialog, you can see the Hide Instance flag is “Yes”, just set it to “No”. Please see the following screen shot.

You have to restart the SQL server service for this instance to make this flag effective. You will be able to connect to it in SSMS successfully now. Cheers!

Scripting out all objects from a SQL server is a way of daily backup for a DBA’s daily job. Following are the scripts I use to take care of it using PowerShell script.

<span style="color: #008000;"><#</span><span style="color: #008000;">
.Synopsis
   Script out linked servers on a SQL server
.DESCRIPTION
   Script out linked servers on a SQL server
.EXAMPLE
   Script-SQLLinkedServers -server "Test" -outputpath "C:\Databases\Test\LinkedServers" 
</span><span style="color: #008000;">#>
</span><span style="color: #0000ff;">function </span><span style="color: #5f9ea0;">Script-SQLLinkedServers</span><span style="color: #000000;">
{    
    </span><span style="color: #0000ff;">Param</span><span style="color: #000000;">
    (
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> sql server name to script</span><span style="color: #000000;">        
        [</span><span style="color: #008080;">string</span><span style="color: #000000;">]
        </span><span style="color: #800080;">$server</span><span style="color: #000000;">,
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> path to store the sql scripts</span><span style="color: #000000;">        
        [</span><span style="color: #008080;">string</span><span style="color: #000000;">]
        </span><span style="color: #800080;">$outputpath</span><span style="color: #000000;">
    )

        [</span><span style="color: #008080;">reflection.assembly</span><span style="color: #000000;">]::</span><span style="color: #8b4513;">LoadWithPartialName</span><span style="color: #000000;">(</span><span style="color: #800000;">"</span><span style="color: #800000;">Microsoft.SqlServer.Smo</span><span style="color: #800000;">"</span><span style="color: #000000;">) | </span><span style="color: #5f9ea0; font-weight: bold;">Out-Null

</span><span style="color: #0000ff;">        if</span><span style="color: #000000;"> (</span><span style="color: #ff0000;">-not</span><span style="color: #000000;"> (</span><span style="color: #5f9ea0; font-weight: bold;">Test-Path </span><span style="color: #800080;">$outputpath</span><span style="color: #000000;">))
        {
            </span><span style="color: #5f9ea0; font-weight: bold;">New-Item </span><span style="color: #5f9ea0; font-style: italic;">-Path </span><span style="color: #800080;">$outputpath </span><span style="color: #5f9ea0; font-style: italic;">-ItemType </span><span style="color: #800000;">directory</span><span style="color: #000000;">
        }

        </span><span style="color: #800080;">$srv </span><span style="color: #ff0000;">= </span><span style="color: #5f9ea0; font-weight: bold;">New-Object </span><span style="color: #800000;">Microsoft.SqlServer.Management.Smo.Server</span><span style="color: #000000;">(</span><span style="color: #800000;">"</span><span style="color: #800000;">$server</span><span style="color: #800000;">"</span><span style="color: #000000;">) 
        </span><span style="color: #800080;">$db </span><span style="color: #ff0000;">= </span><span style="color: #5f9ea0; font-weight: bold;">New-Object </span><span style="color: #800000;">Microsoft.SqlServer.Management.Smo.Database
</span><span style="color: #800080;">        $scrp </span><span style="color: #ff0000;">= </span><span style="color: #5f9ea0; font-weight: bold;">New-Object </span><span style="color: #800000;">Microsoft.SqlServer.Management.Smo.Scripter</span><span style="color: #000000;">(</span><span style="color: #800080;">$srv</span><span style="color: #000000;">) 
        </span><span style="color: #800080;">$scrp</span><span style="color: #000000;">.Options.ScriptDrops </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$FALSE
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.WithDependencies </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$TRUE
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.IncludeHeaders </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.AppendToFile </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$False
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.ToFileOnly </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.ClusteredIndexes </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.DriAll </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.Indexes </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.Triggers </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $lsvrs </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$srv</span><span style="color: #000000;">.LinkedServers

        </span><span style="color: #0000ff;">foreach</span><span style="color: #000000;">(</span><span style="color: #800080;">$lsvr</span><span style="color: #0000ff;">in</span><span style="color: #800080;">$lsvrs</span><span style="color: #000000;">) 
        {
            </span><span style="color: #800080;">$lsvrname </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$lsvr</span><span style="color: #000000;">.Name </span><span style="color: #ff0000;">-replace </span><span style="color: #800000;">'</span><span style="color: #800000;">\s|,|\\|:|<|>|\(|\)|\[|\]|/</span><span style="color: #800000;">'
</span><span style="color: #800080;">            $filename </span><span style="color: #ff0000;">= </span><span style="color: #800000;">"</span><span style="color: #800000;">$outputpath\$lsvrname.sql</span><span style="color: #800000;">"
</span><span style="color: #5f9ea0; font-weight: bold;">            Write-Host </span><span style="color: #800080;">$filename 
</span><span style="color: #800080;">            $lsvr</span><span style="color: #000000;">.Script() | </span><span style="color: #5f9ea0; font-weight: bold;">Out-File </span><span style="color: #800080;">$filename</span><span style="color: #000000;"> 
        } 
}</span>

I have bunch of SQL Server backup files with name like this:

master_backup_201308172100.bak

It has date time as the string in it. When I need to run the database restore script, I have to trim off the date time to make the file name like this:

master_backup.bak

Otherwise I have to update the database restore script each time I restore a different day’s backup. A PowerShell script comes to help:

<span style="color: #5f9ea0; font-weight: bold;">dir</span><span style="color: #000000;"> | </span><span style="color: #5f9ea0; font-weight: bold;">rename-item</span><span style="color: #5f9ea0; font-style: italic;"> -newname</span><span style="color: #000000;"> {</span><span style="color: #800080;">$_</span><span style="color: #000000;">.Name.SubString(</span><span style="color: #000000;">0</span><span style="color: #000000;">, </span><span style="color: #800080;">$_</span><span style="color: #000000;">.Name.IndexOf(</span><span style="color: #800000;">"</span><span style="color: #800000;">2013</span><span style="color: #800000;">"</span><span style="color: #000000;">) </span><span style="color: #ff0000;">-</span><span style="color: #000000;">1</span><span style="color: #000000;">) </span><span style="color: #ff0000;">+</span><span style="color: #800080;">$_</span><span style="color: #000000;">.extension}</span>

Have fun!!

Our TFS 2010 server was on a 32-bit Windows Server 2003 R2 box with SQL Server 2008. We are trying to upgrade to TFS2012. But the requirements of upgrade requires all 64-bit OS. So we have to move it to another 64-bit box. Since all our servers are virtualized on VMware already, it makes the migration process less pains. I found this migration guide.aspx) very helpful. And I was using VM snapshots to help me do the testing. Following are the brief of the steps I went through:

1) Install 64-bit OS Windows Server 2008 R2, and SQL Server 2008. Note: the SQL Server version and service pack/patch level needs to be exactly matched to the old server.

2) Install SharePoint 3.0 wth SP2 on the new box since we have SharePoint with TFS.

3) Stop the TFS 2010 service on old server: TFSServiceControl quiesce.

4) Backup and Restore all related databases following the migration guide. Don’t forget the database in Analysis Service.

5) Repoint the Sharepoint content database on the new box.

6) Change SQL Reporting Service databases, restore security certificates, and update the data source on the new box.

7) Install TFS2010 on the new box. Don’t run the config wizard yet. And update the TFS2010 to the same patch level as the old server.

8) Use TFSConfig PrepSQL to prepare SQL Server to be ready for TFS2010 on the new box.

9) Use TFSConfig RemapDBs to remap databases for TFS on the new box.

10) Use TfsConfig Accounts Update service accounts and accounts permissions on the new box.

11) On the new box, use Team Foundation Administration Console to start the Application-Tier Only wizard. After that, update the URL for the Application-Tier Server, Configure SharePoint products, and Configure Reporting and Analysis Services.

12) Add the new server to client’s visual studio, everything shows up exactly where it left.

I moved all my blog posts to this new site hosting in Windows Azure. Welcome to this new home.

Now we come to the next version of SQL Server – SQL Server 2014. One of the really beauties of it is the In-memory OLTP (code name Hekaton). Can’t wait to get my hands on it once the CTP 1 is coming out. Following are the summary from this white paper about it.

In-Memory OLTP (formally known as code name “Hekaton”) is a new database engine component, fully integrated into SQL Server. It is optimized for OLTP workloads accessing memory resident data. In-Memory OLTP allows OLTP workloads to achieve remarkable improvements in performance and reduction in processing time. Tables can be declared as ‘memory optimized’ to take advantage of In-Memory OLTP’s capabilities. In-Memory OLTP tables are fully transactional and can be accessed using Transact-SQL. Transact-SQL stored procedures can be compiled into machine code for further performance improvements if all the tables referenced are In-Memory OLTP tables. The engine is designed for high concurrency and blocking is minimal.