IT Nota

How to Use Custom Color in SSMS

With many mistakes made by developers and DBAs alike firing a query in a wrong environment, there is one easy thing that can be done to alleviate this problem. That is by using the color coding on the status bar and you can set this up in the server registration on your SQL Server Management Studio (SSMS).

This feature is so easy to overlook because many people just don’t bother to look at the second tab when editing the Properties in Server Registration (in fact, quite a few people don’t bother to use Registered Servers at all to connect to the databases).

On the Registered Servers pane on SQL Management Studio, right-click on the server you want to assign a color and select Properties…

SSMS Registered Server Properties

Click on the Connection Properties tab and check on Use custom color:

SSMS Edit Server Registration Connection Properties

Here are the color schemes I use for the example. Feel free to use any colors that suit you.
Environment (RGB values)
DEV (102, 133, 46)
STAGING (128, 128, 0)
QA (255, 128, 0)
PROD (190, 0, 0)

Do these steps for all database servers you want to color code.

Here is the final result with all windows opened.
SSMS with custom colors on different environment

Caveats

  • You need to use the registered servers in order to use the custom color.
  • For some reason, the custom color settings is not exportable. So you have to take note of your color settings and repeat the steps for any other computers you use.
  • If you use Change Connection button, the color will stay the same as the last one you connected to from the registered servers, which can be dangerous. This can be remedied by using connection coloring in SSMS Tools Pack. This issue was fixed in Windows 8.1, however SSMS Tools Pack still gives a bit more flexibility to place the color bar on one of the four sides of SSMS query window. If you have access to Redgate SQL Prompt, this might be the best option, and you can read on how to do it here.

Further Reading

How to Use Custom Color in SSMS Using Redgate SQL Prompt
Using Connection Coloring in SSMS Tools Pack

How to Change Default Database Driver in Visio

Other than creating a flowchart, Microsoft Visio is also a very good tool for data modeling. The default database driver set on the software though is for Microsoft Access.

Visio Default Database Driver (Microsoft Access)

Here are the steps to change the target database driver to SQL Server or any other available databases:

  1. Make sure you have other database driver you want to use installed on your computer first.

  2. Go to the Database menu, then click Database Drivers.

    Visio Menu - Database Drivers

  3. On Database Drivers window under Drivers tab, select Microsoft SQL Server as the default driver for Visio and click OK.

    Visio Database Drivers Selector

That’s it. Now when you open the column property (Edit button), you see the new default driver.
Visio New Default Database Driver (Microsoft SQL Server)

Upgrading SQL Express to Other Edition

In SQL Server 2012, upgrading edition is actually easier than ever since the data is stored under the same directory.

  1. Backup your data data. For default installation, the data can be found in the following directory:
    C:\Program Files\Microsoft SQL Server

  2. Have a local copy of SQL 2012 setup files as it will make the installation process much faster.

  3. Run setup.exe

  4. When SQL Server Installation Center launches, click on Maintenance on the left pane and select Edition Upgrade on the right.

    SQL Server Installation Center - Edition Upgrade

  5. Make sure after the Setup Support Rules shows nothing failed.

    SQL Server 2012 Setup - Setup Support Rules

    Click OK. It will run again one more time to make sure that the computer is not a domain controller. Click Next when done. You need to do this a couple times until you see the screen where you need to enter the product key.

  6. Enter the product key, and click Next.

    SQL Server 2012 - Enter product key

  7. Pick the SQL Server instance you want to upgrade.
    Click Next > until you reach the Ready to upgrade edition.

    SQL Server 2012 - Select Instance

  8. Check the summary on the Ready to upgrade edition.
    If everything looks correct, click on the Upgrade button.

    SQL Server 2012 - Ready to upgrade edition summary

  9. Once done, you should see all statuses as Succeeded in the Complete screen. Click Close.

    SQL Server 2012 - Edition Upgrade Complete

If you want to double check, connect to the database open a New Query window and run this SQL statement:

SELECT @@VERSION

 
It should display result similar to this:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
​Feb 10 2012 19:39:15
​Copyright (c) Microsoft Corporation
​Developer Edition (64-bit) on Windows NT 6.0 <X64> ...

 

SQL Server Edition Query
 
You might also want to remove the SQL Server 2012 setup files from the local hard drive.
 
 

Useful Scripts for SQL Server Performance Tuning

A couple of useful scripts of sql commands to aid performance tuning in SQL Server (T-SQL).

This handy T-SQL script was from SQL Authority shows all queries that are currently running on your server.

SELECT sqltext.TEXT,
  req.session_id,
  req.status,
  req.command,
  req.cpu_time,
  req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Any running query in the result can be killed by using the KILL command followed by the session id.

KILL [session_id]

Database I/O Information

This one is taken from the book:
Professional Microsoft SQL Server 2014 Administration

-- Database IO analysis.
WITH IOFORDATABASE AS 
(
  SELECT 
    DB_NAME(VFS.database_id) AS DatabaseName
    , CASE WHEN smf.type = 1 
      THEN 'LOG_FILE' ELSE 'DATA_FILE' END
	    AS DatabaseFile_Type
    , SUM(VFS.num_of_bytes_written) AS IO_Write
    , SUM(VFS.num_of_bytes_read) AS IO_Read
    , SUM(VFS.num_of_bytes_read + VFS.num_of_bytes_written) 
        AS Total_IO
    , SUM(VFS.io_stall) AS IO_STALL
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS
  JOIN sys.master_files AS smf
  ON VFS.database_id = smf.database_id
  AND VFS.file_id = smf.file_id
  GROUP BY DB_NAME(VFS.database_id), smf.type
)
SELECT
  ROW_NUMBER() OVER (ORDER BY io_stall DESC) AS RowNumber
  , DatabaseName
  , DatabaseFile_Type
  , CAST(1.0 * IO_Read / (1024 * 1024) AS DECIMAL(12,2)) 
      AS IO_Read_MB
  , CAST(1.0 * IO_Write / (1024 * 1024) AS DECIMAL(12,2)) 
      AS IO_Write_MB
  , CAST(1. * Total_IO / (1024 * 1024) AS DECIMAL(12,2)) 
      AS IO_TOTAL_MB
  , CAST(IO_STALL / 1000. AS DECIMAL(12, 2)) 
      AS IO_STALL_Seconds
  , CAST(100. * IO_STALL / SUM(IO_STALL) OVER() AS DECIMAL(10,2))
      AS IO_STALL_Pct
FROM IOFORDATABASE
ORDER BY IO_STALL_Seconds DESC

Further Reading

SQL SERVER – Find Currently Running Query – T-SQL

Recent Posts

Tags

.NET .NET Core AdSense ASP.NET Cdonts Dll Classic ASP Code Editor ETL FSharp Genesis Framework Git Google HP Asset Manager HTML5 Hugo IIS Information Security Internet Internet Information Services iOS JAMStack Linux macOS Microsoft Microsoft SQL Server MVC PHP PowerShell Python Simple Mail Transfer Protocol Smtp Server SQL SQL Server SSIS SSMS SSRS Sublime Text Visual Studio Visual Studio Code VPN Windows Windows 8 Windows 10 Windows 2012 Windows Server