Basics of SQL Server Management Studio (SSMS). Part 2

This article further explains the basics of SQL Server Management Studio (SSMS) – one of the most widely used database development and administration tools. Also, it gives a conceptual understanding of some fundamental and essential options and components related to this tool.

Part-1 Recap

Before we proceed, we’d recommend referring to the first part of the article, as we’ll deal with some steps discussed there. As a recap, we touched on the following things in the previous chapter:

1. About SQL Server Management Studio.
2. Prerequisites.
3. First-time use.
4. Object Explorer.
5. Connect/Disconnect SQL Server.
6. View System Databases.
7. Find SQL Server version.
8. Find SQL Server Databases.
9. Find SQL Server Login and Database User.
10. Applying Database Filter by Name.
11. Applying Database Filter by Creation Date.
12. Removing Database Filter.

Here, we’ll learn more about SSMS and explore some of the already discussed things further.

About Connecting to the SQL Server

Let us begin with the basic steps. Use SSMS to get connected with the desired SQL Server (instance). We normally do it for one or all of the following goals:

1. Create a new database.
2. Query any database (by running scripts against it).
3. Adding new objects (such as tables, views) to an existing database.
4. Manage (monitor and administer) existing databases and servers.

Connecting to the desired SQL Server is the first thing to do once you have opened SSMS, and there are many options to avail.

Typical Connection (Local SQL Server instance)

Open SSMS and connect to the SQL Server instance by providing the details suited to your server:

It is an example of connecting to a locally installed server using Windows Authentication mode selected when you installed SQL Server.

Benefits of Windows Authentication Mode

One of the benefits of using Windows Authentication Mode is that you can connect to SQL Server using your Windows credentials. You won’t need to type any extra credentials. This method also works well for personal development, testing, and learning purposes.

However, ensure that you install a Developer edition. According to the Microsoft documentation, it is a full-featured free edition, licensed for use as a development and test database in a non-production environment. Thus, you can use it for testing, but it does not apply to the Production environment (commercial purposes).

Installing Multiple SQL Server Instances

We can install more SQL Server instances locally to our machine as long as we do not run out of space and use the recommended Developer edition and options of the SQL Server. For example, you can install two different versions of SQL Server to your machine.

However, install multiple instances of SQL Server if it is necessary only. Usually, one instance is enough.

Viewing All Locally Installed SQL Instances

To see all the locally installed SQL instances, click the drop-down menu containing the current Server name:

Click <Browse for more…>,then expand the Database Engine node to see all the SQL Server instances locally installed on your machine:

There, we can see three SQL Server instances installed locally.

Connecting to Multiple local SQL Server instances

To connect to more than one SQL Server local instance, you can use the same method as for connecting to the first instance. See the example of SQL Server 2014 and SQL Server 2016 instances connected successfully:

This scenario is suitable when you have different databases running on different SQL Server versions. Before you could migrate all of them to the newer version, you explore the options and view the limitations. Sometimes, you would like to keep them as they are. In other cases, you might want to manage them together by bringing them into the Object Explorer.

Connecting to the SQL Instance(s) on Network

You can connect to any SQL Server instance installed on your network provided you have access to it. For that, switch to the Network Servers tab and try to establish the connection:

As you can see, no SQL instance is available on the network.

Connecting to the Azure SQL Database

You can also connect to the cloud version of SQL database – the Azure SQL Database. You need only to provide the required credentials.

However, creating the Azure SQL Database requires an Azure account. Hence, after you create the server and the database on the cloud (Azure) resources, you will need to sign up.

Connecting to the Shared Web Hosting SQL Database

SSMS allows connecting to an SQL Server database of a remote server. It is available when you purchase the web hosting services, and that hosting company manages that remote server. Then, you can manage the database and website.

Words of Advice

In short, one locally installed SQL Server instance is fine for learning, exploring, and testing the skills of database development and management, unless you have a legitimate reason to install multiple instances.

More about Object Explorer

We talked about the Object Explorer in the previous article. The window visualizes all databases of the connected SQL Server, and its tree-like view simplifies creating and managing the databases.

To activate this Object Explorer window, we only need to click the shortcut key F8.

Now we know that we can connect to multiple SQL Server(s) and view their contents (databases) in the Object Explorer.

Connecting to Database Engine

To view all databases of the SQL Server instance, we connect to the Database Engine of that server.

However, we can also view, manage, and query some types of databases and objects using Object Explorer, provided those components are also installed and can be accessed.

Connecting Special Servers/Services

If we click on the Connect section in the Object Explorer window, we can see all types of available services/servers we can connect to:

Although the detailed discussion about these types is out of the scope now, let us quickly go through some of them to get a basic understanding.

Analysis Services

As the name indicates, Analysis Services connect us to the SQL Analysis Server locally installed (on-premises) or on the cloud (Azure Analysis Services).

This server contains specialized databases for analysis and reporting that are essential for business intelligence solutions. They give insights into the data and can also help decision-making in the business.

Integration Services

SQL Server Integration Services host (store and manage) and run an appropriate type of SSIS Packages written by developers. These packages mostly apply to collecting data from different databases and putting them into a single one to prepare for the Analysis Services databases.

Reporting Services

This connects us to SQL Server Reporting Services for managing report-related tasks.

Azure Storage

Cloud-based storage by Microsoft serves for storing and accessing things, including massive amounts of unstructured data.

Azure Storage can also address some specific data storage scenarios requiring lots of space. It takes place for the unstructured data (data aren’t in table-like form – it is in many different formats). For instance, we mean the data from web blogs, audio conferences, emails, and documents stored in one place.

Azure-SSIS Integration Runtime

It is a cloud-based version of Integration Services. Hence, you can host and run your SSIS Packages to collect and transfer data from different databases into one special-structure database. For that, you apply the services on the Microsoft-managed cloud.

Words of Advice

It is a good idea to install the SQL Server instance locally for learning and testing purposes. However, when it comes to full-fledged corporate level setup, cloud-based services are preferable. They save the upfront cost of installing, managing, and updating servers, and you won’t have to worry about maintenance tasks. Cloud-based services bring many other benefits, including the “pay what you use” business model.

Typical SQL Server installation

A typical SQL Server local/remote installation includes Analysis Services, Reporting Services, and Integration Services. Also, you can install them separately.

Databases Node (Object Explorer)

One of the first nodes in the Object Explorer is the Databases node. It offers several options for different purposes.

Expanding the Databases Node

Expanding the Databases node displays all databases in the associated SQL Server instance, including the system databases:

Right-Clicking the Databases Node

If you right-click on the Databases node, you will see the tasks related to the databases:

Creating a New Database

Right-click the New Databaseoption to start the process of creating a new SQL database.

Type the name of the database (School)and click OK,leaving the default settings as follows:

Filter the Database Node to View only the School Database

Filter the Databases Node to view only one database named School:

View the School Database in Object Explorer

Now, let’s view the newly-created database in Object Explorer:

Congratulations!

We’ve learned more about SQL Server Management Studio and understood different connection scenarios related to the SQL Server. Also, we’ve examined the processes of creating, filtering, and viewing a new SQL database with the SSMS Object Explorer.

Things to do

With the basic know-how of SQL Server Management Studio, you can perform the following tasks to improve your skills:

1. Create another database called College.
2. Try to locate all the locally installed SQL instances on your machine.
3. Find out if there are any available SQL Server network connections.
4. Explain why the cloud-based SQL Server setup is preferable over locally installed servers and related components in a commercial environment.
5. Define any differences between the Integration Services and Azure-SSIS Integration Runtime.
6. Briefly describe the differences between the SQL Server database and Analysis Services database.