Installing SQL Server Management Studio

SQL Server Management Studio (or SSMS, for short) is the principal tool used in SQL Server administration, maintenance, and development. It can be used to administer relational databases hosted on the Database Engine, or analytical databases and solutions hosted in Analysis Services. It can be used to administer the SQL Server Integration Services and Database (SSISDB) catalog, used for SSIS project deployments. You can even use it to administer the legacy SSIS service, used for legacy SSIS package deployments.

Important note

While most SSIS development activities will take place in SQL Server Data Tools (SSDT), you should still install SSMS on the workstation. Its programming capabilities complement SSDT, whereas its administrative and maintenance capabilities far exceed those available in SSDT.

Of course, SSMS supports numerous programming languages and data management standards typically associated with data management: Transact-SQL (T-SQL, for short) to manipulate data in relational databases, Multidimensional Expressions (MDX) and Data Mining Extensions (DMX) for data analysis and knowledge extraction, and XML for Analysis (XMLA) to administer SQL Server's Analysis Services (SSAS) databases. You can even use SSMS to create and edit XML documents.

Getting ready

For several years now, SSMS has been delivered as a standalone tool, no longer closely associated with a particular version of SQL Server. This decision allows Microsoft to develop the tool in its own development cycle, pretty much completely independently of the development cycle they use for the SQL Server platform. It also grants the development team behind the tool more flexibility in terms of the features being deployed or upgraded in each version.

SSMS is, therefore, no longer included as part of the SQL Server installation, as it used to be in the past, and must be installed separately. It is available for download at https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.

The download site can also be accessed from SQL Server Installation Center.

Tip

It is recommended to always use the latest version of SSMS, as it typically contains the latest security, stability, and user-experience fixes. The latest features and capabilities are, naturally, also only available in the most recent version of SSMS.

In fact, this is true for every tool that you will be installing in this and later chapters.

How to do it…

Use your favorite internet browser to navigate to the SSMS download site listed previously, or start SQL Server Installation Center – it is located in the Start menu, in the Microsoft SQL Server 2019 folder – and then, on the Installation page, select the Install SQL Server Management Tools option.

You will start this recipe by installing SSMS and complete it by downloading a sample database from GitHub and deploying it to the SQL Server instance you installed in the previous recipe, Installing Microsoft SQL Server 2019 Integration Services:

1. In the Microsoft docs article entitled Download SQL Server Management Studio (SSMS), inspect the latest information about the download. Pay close attention to any warnings or considerations that the vendor might have provided.

   When ready, click the Download SQL Server Management Studio (SSMS) HTML link to initiate the download.

2. If prompted to save the executable to your drive, click Save, wait for the download to complete, and then click Run to start the installation.

   If the download is performed in the background, use Windows Explorer to locate the file in your downloads folder, and start the execution from there – for instance, by executing the SSMS-Setup-ENU.exe file.

3. On the first screen of the installation dialog, leave the default location unchanged, and click Install to continue.
4. The installation should complete within a few minutes.

   If the installation is interrupted by an error, investigate the cause based on the information provided in the installation dialog. Otherwise, click Close to finish the installation.

5. In the Windows Start menu, locate the Microsoft SQL Server Tools 18 folder, and in it the Microsoft SQL Server Management Studio 18 shortcut. Open it.
6. In the Connect to Server dialog, select the SQL Server instance you created in the previous recipe, Installing Microsoft SQL Server 2019 Integration Services.

   The instance should now be displayed in the SSMS Object Explorer.

7. Open SQL Server Installation Center, and on the Resources page, locate the SQL Samples Web Site link. Follow the link to navigate to the Microsoft SQL Server Samples site.

   In your internet browser, scroll down to the SQL Server Samples Repository section, and click on the link to the Wide World Importers sample database web page.

8. On the new page, locate the WideWorldImporters-Full.bak file and click the link to start the download.

   Depending on your internet connection, the download should complete within a few minutes.

9. In SSMS, in Object Explorer, right-click the Databases node, and then select Restore database… from the shortcut menu, as shown in the following screenshot:
   

   Figure 1.19 – Restoring a SQL Server database

10. In the Restore Database dialog, on the General page, select Device as the source, and open the Select backup devices dialog by clicking the ellipsis icon to the right of the Device text box, as shown in the following screenshot:
    

    Figure 1.20 – Selecting the backup device

11. In the Select backup devices dialog, click Add to open the Locate Backup File dialog, and then navigate the filesystem on the left to locate the file you downloaded in Step 8.

    Select the file and click OK to confirm the selection.

12. In the Select backup devices dialog, click OK to confirm the selection.
13. In the Restore Database dialog, click OK again to start the restore operation.

    It should take about a minute to restore the sample database.

    Once the message box opens with the Database 'WideWorldImporters' restored successfully message, the database should be available on the selected instance.

    If there are any errors, inspect the error messages, make the necessary corrections, and repeat the process accordingly.

14. After the database has been restored successfully, click OK to close the message box, which also closes the Restore Database dialog.

    Now that you have your first tool installed, you can change two more settings that you might need later.

15. In SSMS, open a new query window, connect to the master database, and then use the following T-SQL command to inspect the Hadoop connectivity instance settings:

    EXEC sp_configure
         @configname = 'hadoop connectivity';

    In the execution result, observe the run_value column; if its value is 0, change it to 7 by executing the following T-SQL commands:

    EXEC sp_configure
         @configname = 'hadoop connectivity',
         @configvalue = 7;
    reconfigure;

    By turning this configuration setting on, you allow your SQL Server instance to connect to remote Hadoop or Azure Blob storage locations. By using the configuration value of 7, you allow connections to all versions and both editions of Hortonworks, as well as to the Azure Blob storage.

    Tip

    More information about this setting is available in the vendor documentation, at https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/polybase-connectivity-configuration-transact-sql. You should be familiar with the setting and its possible values before using the feature in a production environment.

16. Use the following T-SQL command to check whether the PolyBase service is enabled on your SQL Server instance:

    EXEC sp_configure
         @configname = 'polybase enabled';

    If the value of the run_value column is 0, that means that PolyBase is not enabled; enable it by changing the configuration value to 1 by executing the following T-SQL commands:

    EXEC sp_configure
         @configname = 'polybase enabled',
         @configvalue = 1;
    RECONFIGURE;

    Tip

    More information about this setting, and the PolyBase services in general, is available in the vendor documentation, at https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-installation. You should be familiar with the setting and its possible states before using the feature in a production environment.

17. If all the commands complete successfully, close SSMS. This time, you do not have to save the script or the solution.
18. Open SQL Server 2019 Configuration Manager, and restart the MSSQLSERVER instance, as you did in Step 20 of the previous recipe, Installing Microsoft SQL Server 2019 Integration Services.

    Important note

    The SQL Server instance must be restarted in order for these two configuration settings to take effect, as they affect the dependent PolyBase services. Also, you cannot restart a SQL Server instance running PolyBase from within SSMS when Object Explorer is connected to the service and prevents the service from being stopped.

After the instance is restarted, you can close Configuration Manager.

How it works…

You have installed the principal development and administration tool for SQL Server. You will be using SSMS throughout this cookbook. Of course, if you have worked with SQL Server before, you should already be familiar with SSMS and its capabilities. If this is the first time you have used SSMS, do not worry – every feature used in the recipes in this book will be explained when needed.

You finished the recipe by deploying a sample database to the newly installed SQL Server instance. You will use this database in other recipes of this cookbook, and you are free to use it whenever you are exploring SQL Server and its capabilities.