Installing SQL Server and the AdventureWorks Database

Microsoft SQL Server is a popular relational database management system (RDBMS). Database software products such as SQL Server have the primary function of storing and retrieving data as requested by other software applications, which may run either on the same computer or another computer across a network.

SQL Server often serves as the source of data for Power BI reports. In addition, some of the recipes and examples in this book center around accessing the Microsoft AdventureWorks sample database hosted on SQL Server. This recipe will help you get this environment created so that you can follow along in those recipes.

Getting ready

To prepare for this recipe, download the Developer edition of SQL Server from the following link: https://www.microsoft.com/en-us/sql-server/sql-server-downloads. The downloaded file should be named SQL2022-SSEI-Dev.exe.

Also, download SQL Server Management Studio, which is used to view and configure Microsoft SQL Server. Download the SSMS-Setup-ENU.exe file from this link: https://aka.ms/ssmsfullsetup.

How to install SQL Server

First, we will install the Developer Edition of SQL Server, and then, SQL Server Management Studio.

Installing Developer Edition of SQL Server

To install SQL Server, use the following steps:

1. Run the file SQL2022-SSEI-Dev.exe.
2. If prompted by User Account Control, select Yes.
3. On the installation splash page, choose Basic.
4. Press the Accept button to agree to the terms and conditions.
5. Either click the Install button or choose a different folder by clicking the Browse button, and then click Install. The default installation location is C:\Program Files\Microsoft SQL Server.
6. When the installation completes, you will be presented with a screen similar to Figure 1.13:

Figure 1.13: Successful installation of SQL Server

7. Click the Close button.

Installing SQL Server Management Studio

1. Run the SSMS-Setup-ENU.exe file.
2. On the installation splash screen, click the Install button.
3. If prompted by User Account Control, select Yes.
4. If the installation fails, reboot your computer, and then run the SSMS-Setup-ENU.exe file again.
5. After the installation completes, click the Restart button to restart your computer and finish the installation.

Microsoft SQL Server as well as SQL Server Management Studio are now installed and ready for use.

How to install the AdventureWorks database

The AdventureWorks database is a sample database provided by Microsoft that is often used for demonstration and learning purposes. The database is designed to showcase the capabilities and features of Microsoft SQL Server. The AdventureWorks database has evolved over different versions of SQL Server, with the latest version at the time of this book’s publication being 2022.

The database represents a fictional company called AdventureWorks Cycles, a manufacturing company that produces and sells bicycles and related products. The AdventureWorks database includes various tables that model different aspects of the company’s operations, such as customers, sales, products, employees, and more.

Each version of the AdventureWorks database comes in three different backup (.bak) files:

• Online Transaction Processing (OLTP): The full dataset
• Data Warehouse (DW): A summarized version of the dataset for reporting purposes
• Lightweight (LT): A trimmed-down version of the OLTP dataset

For our purposes, we will use the data warehouse (DW) version of the AdventureWorks database.

To install and configure the AdventureWorks DW database, follow these steps:

1. Download the backup (.bak) file from GitHub using this link: https://bit.ly/3QTnCXI. The downloaded file is called AdventureWorksDW2022.bak.
2. Open Windows File Explorer and move the AdventureWorksDW2022.bak file from the Downloads directory to the C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup directory.
3. If not already open, open SQL Server Management Studio and connect to your local instance of SQL Server, as described in the How it works section of this recipe.
4. Expand the instance of SQL Server in the Object Explorer, and then right-click Databases and choose Restore Database…
5. On the General page, select Device, and then the ellipsis (…). In the Select backup devices window, click the Add button. Select the AdventureWorksDW2022.bak file and click the OK button. Click the OK button in the Select backup devices window, and finally, click the OK button on the General page. Use Figure 1.14 for guidance on executing these steps in sequence.

Figure 1.14: Restoring the AdventureWorksDW2022 backup file

6. Change to the Files page and select the checkbox for Relocate all files to folder, as shown in Figure 1.15:

Figure 1.15: Relocate all files to folder

7. Click the OK button once the database is restored successfully.

The AdventureWorksDW2022 database is now available in the Object Explorer under Databases, as shown in Figure 1.16:

Figure 1.16: Restored AdventureWorksDW2022 database

We will use this database in future recipes within this book to demonstrate some of the amazing capabilities of Power BI.

How it works

Microsoft SQL Server runs as a set of services on your computer. These services can be seen using the Windows Services application and include the following:

• SQL Server
• SQL Server Agent
• SQL Server Browser
• SQL Server CEIP service
• SQL Server VSS Writer

Multiple instances of SQL Server can run on the same computer. The default is to create an instance called MSSQLSERVER.

We can connect to this service using SQL Server Management Studio. To do this, start typing SQL Server Management Studio in the Windows search bar, and then open the application when it appears, as shown in Figure 1.17:

Figure 1.17: Running SQL Server Management Studio

On the Connect to Server window, ensure that Database Engine is selected for Server type, that the name of your local computer appears for Server name, and that the Authentication is set to Windows Authentication. Click the Connect button to connect to the local instance of SQL Server, as shown in Figure 1.18:

Figure 1.18: SQL Server Management Studio

See also

For additional information on the topics covered in this recipe, refer to the following links:

• Microsoft SQL Server documentation: https://bit.ly/3FVerji
• SQL Server Management Studio documentation: https://bit.ly/47x3nV8
• AdventureWorks sample databases: https://bit.ly/3Ue8y8J