Setting Up A Local SQL Server Developer Instance
As quite a few of the things I’d like to share with you require a working SQL Server instance. In this article I’ll be giving a quick step by step account of how to install SQL Server locally. In addition we’ll also look at the tooling and import some sample data, meaning you’ll have everything you might need to follow along with any of my other database focussed articles.
SQL Server Developer Edition
Since sometime in 2017 Microsoft began offering SQL Server Developer Edition free for non-commercial use. Originally starting with SQL Server 2014, and later in the same year SQL Server 2016 Developer Edition was also available free to use. Since then each new version of SQL Server has provided a Developer Edition available for free drastically increasing the accessibility of SQL Server to development teams.
Firstly navigate to the SQL Server downloads page: https://www.microsoft.com/en-gb/sql-server/sql-server-downloads
A short scroll down the page and we can see a download button for the Developer edition on the left of our screens:
Clicking this will cause the installer to be stored to your default downloads location.
Next when running the installer you will be prompted with a security warning asking: “Do you want to allow this app to make changes to your device?”. Now with all downloads and executable files there are inherit risks involved with granting this privilege, so feel free to take a moment to consider these risks, and disregard this step entirely if you have any concerns.
If you continued, you’ll next be faced with a screen asking which installation type, we will be proceeding with Basic:
Review the T&Cs that have to be accepted and then proceed with the default install location — for reference mine is going straight to
C:\Proram Files\Microsoft SQL ServerClicking Install on this screen will cause the installer to download the necessary artefacts and proceed with a default installation. Once completed you’ll be presented with a screen like this: (Note you’re instance name will be MSSQLSERVER instead of MSSQLSERVER01 — this was my second installation)
And that’s it! You’ve installed your very own SQL Server instance locally, you can click the Connect Now button to open up a quick console verifying the connection.
SQL Server Management Studio
Now installing SQL Server is half the battle, but next we will be installing an application that will allow us to visualise, manage and query our database server — SQL Server Management Studio (SSMS).
If you still have the above dialogue box open upon completing your install of SQL Server, then you can just click the button labelled Install SSMS, otherwise you can access the materials at the following page:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
Again a quick scroll down the page points to a link to Download SSMS highlighted below.
Again clicking this link will cause the installer to be downloaded to your local default downloads location.
Again running the installer will prompt “Do you want to allow this app to make changes to your device?”. Note my previous caution regarding this privilege still remains.
If you proceed you’ll be met with an installation landing page, for this again we will keep the default location selected by the installer:
Clicking install will start the process — this may take some time!
Once completed — that’s it! SSMS should now be available on your system and all that’s left to do is connect to our SQL Server instance and verify the installations.
When we run SSMS for the first time we will immediately be prompted with a window asking for the database details that we’d like to connect to. A really cool trick with our SQL Server Developer install is that it should already be accessible using a few different convenient shortcuts
1. Dot (.)
2. (local)
3. localhost
4. 127.0.0.1
Personally I always used the first option — a single full-stop (period for my American friends) in the Server Name:
And clicking connect does indeed connect us to our database so that we can begin using our SQL Server Installation via SSMS:
Restoring The AdventureWorks Db
The final step in this article will be restoring the AdventureWorks sample database that comes prepopulated with a rich schema and decent amount of seed data for learning purposes.
This database backup is available at the following location:
https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms
And if we scroll down slightly we come across a section annotated Download Backup Files.
Here we will be downloading the OLTP 2019 .bak file highlighted below:
Clicking this will again download the media to your default downloads location.
Next we will return to SSMS and right click on the Databases folder in the Object Explorer and select Restore Database…
Next on the general tab, set your backup source as Device and then click the triple dot button to the right:
The next screen will ask you to specify the backup media and it’s location… here we click Add:
And then we navigate to the location of our AdventureWorks backup that we’ve just downloaded — note you may have to move the backup to a different folder location due to folder sharing and accessibility issues, if the backup isn’t immediately visible just copy it over to your C:\ and it should be accessible from there.
Once the .bak file is selected you can click Ok on the next two dialog boxes confirming the backup media that is to be restored until you’re back at the original restore page:
Finally from here if you click the Ok button AdventureWorks should be installed and you will receive a popup confirming such:
If you’ve made it this far then we now have a fully functional local development environment that includes:
1. A working installation of SQL Server Developer Edition.
2. A configured and verified installation of SQL Server Management Studio.
3. A fresh installation of the AdventureWorks sample database.
Thanks for staying with it and I hope to see you again!
Kev
