This is my first #entrylevel blog post. The challenge identified by the tags #entrylevel and #Iwanttoshare was started by Tim Ford (t|b) to help new comers to SQL Server. With #entrylevel in mind I’ve decided to focus on SQL Server Management Studio (SSMS). I will start with what SSMS is for, how to install it and finally some personal configuration preferences that I think can help beginners.

SSMS is the go to tool for accessing SQL Server databases. It allows developers to write T-SQL code for their applications. Database administrators use SSMS to setup maintenance tasks, troubleshoot, and to configure their SQL Server installations.

The Install

SSMS can be installed when installing SQL Server by selecting Management Tools from the Feature Selection section of the SQL Server installer. It can also be installed in a stand alone fashion by downloading the SSMS installer from here. As of writing this link leads to the latest preview of SSMS. Check here for previous releases. This post will focus on SSMS 2016 CTP 3.2 as it is the new shiny.

Once installed, run the new to 2016 ‘Check for Updates’ feature (under the Tools menu) to be sure you have the latest version. I grabbed my installer from a file share and can see that it was a little old.

Unfortunately,  clicking the update button does not update your SSMS. It sends you to the link above to download the latest version. You will then have to remove the old version.

There are many sub windows in SSMS that you can open each time you need them or you can pin them to certain sections of the SSMS workspace by clicking the pin icon next to the X.

The windows I find the most useful, and tend to have pinned, are:

• Object Explorer
• Registered Server
• Properties
• Template Browser
• Find and Replace

Experiment with various windows and their layout by dragging them around until you find what is comfortable for you.

Registered Servers 

The Registered Servers window enables you to store and organise your connections in the Local Server Groups folder. Once a connection is stored, you just need to double click it to connect. You can also register Central Management Servers that store connections. These are useful for teams that access a lot of different instances of SQL Server. For more information see here.

Object Explorer  

Here you can navigate through the databases of the SQL instances you have connected to. I would recommend you to get used to disconnecting from instances you are not currently using to avoid accidentally running code against the wrong one.

Another way to avoid such mistakes is to set the status bar colour for your connections. This can easily be done in Registered Servers by right clicking a connection, clicking Properties and then setting a Custom Colour on the Connection Properties tab. I tend to choose red for production connections and leave the others as default. Unfortunately, you can’t set these colours for a whole team by setting them on a Central Management Server. The colour settings are only persisted locally.

Template explorer 

I am a recent convert to using the Template Explorer to access my script library. I started experimenting with it after I read Russ Thomas’ post A Sane T-SQL Script Library. I was using the solution Explorer before but found it frustrating. It’s still not perfect as you have to reload SSMS to see any new files you have added, but I feel it works quite well if you are using GitHub/GitLab to source control your scripts.

Properties

The Properties window will give you lots of detailed information on whatever you have selected in the Object Explorer. My main use for it is when I am looking through query execution plans. The property window can give you much more information about how SQL Server is processing your query than the graphical execution plan.

Find and Replace

This one is just a personal preference as a quick Ctrl + F opens the window but I like to have it out the way so it doesn’t obscure the code I am searching through.

If you look at the screenshot above you can see that the name of the query tab is quite long. This can be annoying when you have more than a few query tabs open. To fit more on the screen at the same time I like to make the following changes in the Tools -> Options dialogue. Setting all but file name to False frees up a lot of space.

Sometimes when using SSMS you will see a redline under a table or object name in your T-SQL. This means SSMS thinks the object doesn’t exist in the current database. Usually it’s right, but if you have just created the object, the query editor wont know as it’s local cache is not regularly refreshed. To force a refresh you can hit Ctrl + Shift + R but I always forget keyboard shortcuts. For this I like to add a button to the toolbar.

First click Tools, Customise and then the Commands tab.

Next select the SQL Editor in the Toolbar option and then click Add Command.

In the Edit category you will find Refresh Local Cache. OK that and use the Move Up and Down buttons to position your new icon.

If you see the redline again, and you know the object exists, click the new refresh button and after a couple of seconds the line should disappear.

So that’s the basics with a bit more thrown in. I plan to follow up with some more advanced options and the installation of 3rd party software and plugins in a future post. Until then keep experimenting and let me know how you get on.

The post SQL Server Management Studio Setup and Configuration appeared first on The Database Avenger.