Installing SQL Server
By Richard Rost
3 days ago
Installing SQL Server Express and SSMS
In lesson 2, we will walk through downloading and installing Microsoft SQL Server Express and SQL Server Management Studio (SSMS) step by step, taking the time to explain important options and terminology along the way. I will show you how to choose the right components, set up your local instance, select authentication methods, and connect with SSMS so you have a fully working SQL Server setup ready for future lessons.
Prerequisites
Links
Up Next
Keywords
SQL Server for Access, download SQL Server Express, install SQL Server Express, install SQL Server Management Studio, SSMS, SQL Server instance, Windows authentication, database engine services, SQL Server local network, in-house SQL Server, firewall warning, Azure extension, server certificate trust, Access database migration, SQL Server service accounts, server name SQLExpress, pin SSMS to taskbar
Intro In lesson 2, we will walk through downloading and installing Microsoft SQL Server Express and SQL Server Management Studio (SSMS) step by step, taking the time to explain important options and terminology along the way. I will show you how to choose the right components, set up your local instance, select authentication methods, and connect with SSMS so you have a fully working SQL Server setup ready for future lessons.Transcript In lesson two, we are going to download and install SQL Server Express and then install SQL Server Management Studio so we can actually manage our databases. I will walk you through the setup step by step, explain the important options along the way, and make sure you end up with a working SQL Server.
We are going to start off by downloading and installing SQL Server. Here is the link. I will put a link down below. You can also click on it. Microsoft loves their really long links, so have fun typing it or just click on the link down below.
As with anything, this may change by the time you watch this, depending on when you are watching. Right now it is 2026. So we are looking for SQL Server 2025. There are a bunch of options here. We are looking for this one right there. We want SQL Server Express. Whatever your version happens to be, if you are watching this two years from now and they have a different version, download this guy.
There it is in my downloads folder. We are going to click on that. We are going to say, yep, and it is going to do its thing. Now, you have some options here. We are going to choose custom, not because we are doing anything fancy, but because I want you to actually see and understand what is being installed. The basic option just runs with the defaults and hides a lot of the important details, which is fine if you only care that SQL Server works, but since we are Access users and we are going to be connecting a database into this later, it is worth slowing down and seeing what is going on.
You are going to see throughout this lesson, and moving forward, I am going to stop and spend a lot of time explaining what you see on the screen because I know you are going to have a ton of questions and they will be posted in the forums and I will be answering them later. I am just going to take the time now slowly to answer all of the questions that I had the first time I ran through this years ago.
So yes, there will be a lot of chatter in this lesson because I am explaining things for you. Do not worry, we are going to accept most of the defaults, but this is about understanding, not just walking through the steps. Go ahead and click custom.
Now it is asking for the immediate download target location. This is just SQL Server asking where it is going to download and temporarily store its setup files, not where your databases are going to live. This is just the installer files. The default location is fine. Unless you have a specific reason to change it, I am going to hit install. And it is going to do its thing. Here it goes.
Close your browser now, by the way.
Next, we have the SQL Server installation center. OO, sounds fancy - installation center. Here is the actual database engine. So we are going to click on new SQL Server standalone installation right here. The other options are reporting tools, management utilities, upgrading older versions. We will install some of this stuff later, but for now, we are focused on getting the core SQL Server engine installed, so Access has something to connect to. Click on this guy.
Next, you get the license term screen where Microsoft asks for your first born and your eternal soul. Did all of this carefully word... I'm just kidding, no one ever reads this stuff. Click accept and next.
Express is free to use, but you still have to accept the terms. You can use it even free commercially. For what we are doing here, there is nothing unusual you need to worry about. Now, this screen is asking you if you want SQL Server to use Microsoft update.
This one is a personal preference. I turn automatic updates off, not because automatic updates are bad, but because I like to control when they happen. I do the same thing for Office, I do the same thing for Windows. I prefer to install updates manually when I know I have got time to deal with it, usually on a weekend, just in case something goes sideways and I need to roll back. If you are comfortable with automatic updates, that is totally fine too. There is no wrong answer here. Just be intentional about whichever option you choose. I leave them off and I am going to hit next. It is going to take a minute and do its thing.
Finally, you will get to this screen. This is just SQL Server running a quick pre-flight check to make sure nothing obvious is going to block the install. The green check marks mean we are good. Warnings are usually informational, not fatal. In this particular case, we got one here about Windows Firewall. That is normal and expected, especially on a local machine. We are not opening this computer up to the internet. We can adjust firewall rules later if we need remote connections, although I do not recommend it. As long as there are no red X errors, we are fine to continue.
One thing I want to be very clear about in this course is we are talking about running SQL Server inside your building in your local network for your own users. We are not talking about opening up SQL Server to the outside internet. Technically you can do that, and maybe in a future lesson I will show you how, but I strongly recommend against it.
Years ago, back in the 90s, I tried running my own web server. At first I ran it out of my house because I had cable internet - one of the first people to get it at the time. It was a nightmare. So then I rented Rackspace at a local company and put my actual physical server on their network. One day I had two gentlemen in full dress uniform from the United States Air Force show up at my door asking if I was Mr. Richard Rost. Yes, I said I was, of course. It turned out my server had been hacked and was being used in a denial of service attack against the Department of Defense. That was lovely.
Now, they knew I was not responsible, but they asked me to sign some paperwork so they could put some monitoring equipment on my server to track where it was coming from. That was the moment I decided I was done exposing my network to the internet. That was it for dealing with running my own web server. I am a database nerd. I am a programmer. I am not a security expert.
So my advice is focus on building your database, not defending it from hackers. If you need outside access, use professionals, get proper web hosting. You could set up an Azure database or I recommend services like Winhost - let them handle security. So this course is about in-house use database stuff. That is intentional.
Anyway, I said there were going to be a lot of tangents. So sit back and relax.
Ready to hit next. Now this screen is about the Azure extension for SQL Server, which lets you connect your SQL Server to Microsoft Azure for cloud-based management and monitoring. We are not doing that in this course. Our focus is SQL Server running on your own machine inside your building so you do not need Azure for that. We are going to turn this off and move on.
If you ever decide to use Azure in the future, you can always add this later, but right now it just adds complexity that we do not need. Click next again.
Just so we are clear on terminology, Azure is basically Microsoft's cloud platform. In simple terms, it means it gives you the ability to run SQL Server on Microsoft's machines instead of on your machines. They manage the hardware, the networking, the security, the maintenance, all that stuff over the internet. It is still SQL Server. You are just renting it instead of hosting it yourself. That can be a great option, but it is a different topic. For this course, we are staying local so we can focus on Access and database design, not cloud infrastructure.
Now this is the screen where we choose which parts of SQL Server get installed. For Access users, the only thing we truly need is the Database Engine Services. That is the core of SQL Server - it is what actually stores and serves the data.
The other options like replication, full text search and PolyBase are advanced features used in larger and more specialized setups. They are fine to leave checked if they are on here by default, but we are not going to use them in this course. The key takeaway is that as long as Database Engine Services is selected, you are good. For this one, we are just going to hit next.
Now this screen is where we name our SQL Server instance. Think of an instance as a specific copy of SQL Server running on this machine. SQL Server Express installs as a named instance called, guess what, SQL Server Express. That is the default and we are going to keep that. That is fine.
The important thing to know is that when Access connects to SQL Server, it needs both the computer name and the instance name. Using the default keeps things simple and consistent. Do it with me. Trust me. Run through this as I am doing it first to learn it before you try doing your own thing. Everyone always does that - they try doing their own thing first and they wonder why it does not work. Follow along with me first. You can always create a second one or install it later again. It is not hard to do. Just do what I do.
I can always tell when people post questions in the forums that they have not followed the videos closely. I can tell. Teacher knows.
Like I said earlier, we are going to spend a lot of time on terminology because there is a lot of different terminology between Access and SQL Server, so you do not confuse the terms. An instance is not the same thing as a database. One SQL Server instance can host multiple databases. Access connects to the server first, and then the instance, and then to a specific database inside the instance. If SQL Server is running on a different computer, you can still connect to that computer's name plus the instance name and then a different database. I am trying to anticipate the questions you are going to ask.
Like I said, this is my first time running through SQL Server as a course, but I know all the questions that I asked myself while I was learning this myself years ago. Can you have more than one instance on the same machine? Yes, you can. But most people never need to. For this course, we are going to stick with just one instance. Remember, an instance is a container and a container can have multiple databases inside it. If you want more databases later, do you need to create another instance? No, you just create multiple databases inside the same instance.
If you have any other terminology questions, post them in the comments down below.
We are going to leave the defaults here. Hit next. And like I said before, this just gives me a chance to run through all the options with you and explain what is going on.
Now, this screen controls which Windows services SQL Server runs under and how they start up. For almost everyone, especially for local in-house use, the defaults here are exactly what you want. SQL Server uses built-in Windows service accounts, starts the main database engine automatically when Windows starts, and it leaves things like the browser service off unless you explicitly need it. We are not going to change anything here. This is more about enterprise security and fine tuning. We are going to keep our focus on building databases, not managing Windows services. Again, we will hit next.
Now this screen controls how people authenticate to SQL Server, meaning how SQL Server decides who is allowed in. We are going to use Windows authentication, which means SQL Server trusts your Windows login. It is the simplest, safest option for in-house use, and it works very naturally with Access. You do not need a separate SQL username or password and you do not have to store credentials in your database.
For most Access users running on a local network, this is exactly what you want. We will talk about SQL logins and mixed mode later, but we do not need it to get started.
Another important note here is you do not need a Windows domain or a domain controller to use Windows authentication. This works perfectly in small offices with just a few computers networked together. The common setup is that each user has a Windows login and that same login exists on the machine running SQL Server. SQL Server trusts Windows to handle authentication even in a peer-to-peer network. This is how most small offices run and it works very well. In small shops, it is common to use the same username and password on each machine to keep things simple.
Let us say you have a small office with three computers and three users: Joe, Bob, and Sue. Each of them logs into their own PC with their own Windows username and password. So if you decide to install SQL Server on Bob's computer, let us say he is your database guy, that becomes your "server" machine. You just need to make sure that Joe and Sue also have Windows logons on Bob's machine, and those logons can match the usernames and passwords they already have on their own computers, which keeps things simple.
As long as Joe and Sue can log into Bob's computer with those credentials, Windows authentication will work and they will be able to connect to SQL Server using the same username and password they are already used to. We will walk through this later when we start connecting Access to SQL Server from different machines. The bottom line is you do not need fancy infrastructure to use SQL Server with Access.
Click next. It is going to do its thing. Now you are going to watch SQL Server do a whole bunch of different stuff installing itself. Go get some coffee and come back. I am going to pause it here while it is installing.
That was not too bad. What about six minutes? It is enough time to get coffee. That is it. Just like that, SQL Server is installed. The engine is now running. As long as you have green check marks on the list here, everything is good to go. Install successful. You can hit close. If install is not successful, I do not know what to do. I do not do tech support. Post something in the forums and maybe we can figure it out if you have problems.
Next up. Now that the SQL Server engine is installed and running, the next step is installing the SQL Server Management Studio or SSMS. This is the tool we are actually going to use to work with SQL Server: to see the databases, to create tables, to run queries, to manage security, all that stuff.
Years ago, SSMS was installed automatically with SQL Server, but now Microsoft ships the database engine and the management tool as separate installs. A lot of times what you do is you install the server engine on one machine (your server), and then you install SSMS on the administrator's machine and the two might be different machines. But for today, we are going to put them on the same machine. Think of SQL Server as the engine running in the background and SSMS is the dashboard or the control panel that we use to interact with it. It is all SSMS now because that is what we are going to be using.
So click on this guy right here. This will bring you to Microsoft's website. Click on the download SSMS installer. This happened to me earlier - this link here seems to not work. Scroll down a little bit and click on that guy. There it is. I do not know why, but that link on top does not seem to work.
Here is SSMS. Click on that. Again, we are going to say, oops, and let it do its thing. All right, hit continue. You can close your browser now too, by the way.
This screen is for installing SSMS. We do not need to customize anything here. SSMS already includes everything we need to manage SQL Server for this course. The extra options you see are tools for things like AI assistance, business intelligence, and migrations. We are not going to bother with any of that stuff right now. The defaults are fine. Leave everything as is and just click on install.
Again, it is going to do its thing. While it is installing, you might notice that there are tools to automatically migrate your Access database over to SQL Server. These tools are not great. They have improved over the years, but they are still best used as helpers, not magic buttons. For this course, we are going to do the migration intentionally and manually so you understand what is happening and can control the results.
It is just like in my Access course when I say there are good wizards and bad wizards. The migration tools are their bad wizards. They do not know. I do not like them. Later on, I will show you some of these tools, what they do well, what they do not do well, and what they might make sense for. But just like in Access, there are good wizards like the combo box wizard, which is great - they save you time and yet you still understand what is being built.
Other wizards do a lot of the work for you, but they do not do it very well. More importantly, you do not really learn what is going on behind the scenes. The upgrade assistant falls into that second category. It can move things over, but it hides a lot of the process and it does not always do what you want it to do. I am going to teach you how this stuff actually works, not just push a button and hope for the best.
Later on, when you are an expert with SQL Server and you decide you have another database you want to migrate quickly, sure, try the assistant. For now, we are going to take it slowly and do it manually.
That was actually pretty quick. I remember - I am old - I remember back in the day when downloading tools like this could take hours. Sometimes it was like an overnight thing if you were on dial-up or early cable modems. These days it is mostly just a sip of coffee or a lunch break. All right, we are done installing.
Now you might at this point get a window that says that the download has, the installation is completed, and you have to restart Windows. Do the restart. If this is the first time putting SQL Server on your machine, you are going to want a clean restart. Trust me. Otherwise, you are going to get weird stuff happening.
I have had SQL Server on this machine before. I just removed it so I could put it back on for class. So I have already got all those files installed, but you are going to want to reboot now if it tells you to reboot.
Since most of you are probably going to have to do that, I am going to close these guys down. We are done with those.
We are going to launch SSMS. Pretend you had to reboot your machine because I am betting most of you will have had to. I am going to click on my Start button. I am going to type in SS and that should - right there - SQL Server Management Studio. We are going to open that.
The first thing we are going to do is pin this guy to our taskbar. So right click, pin to taskbar because we are going to be using it a lot. I want to make sure it is right there.
Now, this screen is asking if you want to sign into SSMS with a Microsoft or a GitHub account to sync settings or use Azure-related features. You do not need to do this to use SSMS and it has nothing to do with connecting to your SQL Server. For this course, we are going to skip this and keep things simple. You can always sign in later if you decide you want to use those features. I am going to click on skip and add accounts later.
Now, this is the connect screen. This is where all the pieces we have been talking about finally come together. The most important field here is the server name. This is where you tell SSMS what machine and which SQL Server instance you want to connect to.
Since the SQL Server is installed on this machine and this machine is named Kirk - yes, that Kirk; I am a nerd, all my computers are named after Star Trek people. My workstation is Picard. This is Kirk. I got Spock. The only one that is not is server; my actual server that runs my business is called server because that is what I named it years ago.
Since we used the default SQL Express, the server name will be Kirk\\SQLExpress. So right here, we are going to type in Kirk\\SQLExpress.
We are using Windows authentication, so SSMS automatically uses your Windows logon - no username or password required. If SQL Server were on a different machine, this is where you would put that machine's name instead.
Leave everything else as it is and click connect.
I did this intentionally because this one always trips me up too. This error looks scary, but it is actually normal on a brand new local SQL Server install. SSMS is trying to encrypt the connection and SQL Server is presenting a "self-signed certificate" that Windows does not automatically trust. Nothing is broken for a local or in-house connection like we are doing here.
The quick fix is to simply tell SSMS to trust the server certificate. Go ahead and hit OK and click on this box here that says "Trust server certificate." Sometimes I do these mistakes intentionally so you see them because I know you are going to run into them and then you are going to post in the forums and be like, what is this all about? Those of you who have taken my Access course know exactly what I am talking about.
In a production environment, you would install a proper trusted certificate, but for local training and for most small office setups, trusting the server certificate is fine. In a nutshell, what it means is, because I know you are going to ask, when SSMS connects to SQL Server, it can encrypt the connection so data is not sent in plain text. To do that securely, the server presents a server certificate; think of it like an ID card that says, yes, I am the server that I claim to be.
On a brand new SQL Server install, that certificate is self-signed, meaning the server created it itself instead of getting it from a trusted certificate authority. Windows does not automatically trust self-signed certificates, so SSMS stops and says, hey, are you sure you trust this guy? Do you trust this server? You are basically saying, yeah, I know this server. This guy is cool. I installed it myself. I am okay with this.
For most local machines and small in-house networks, this is completely safe and normal. In larger environments - big corporations, internet-facing servers - you would use a certificate from a trusted authority instead. These are companies like DigiCert or, back in the day, it used to be Verisign. You purchase a certificate from them - you still have to do it with web servers.
For anything exposed - like I was saying before I was rudely interrupted by these tribbles on my transporter pad - for anything exposed to the internet, you would absolutely want a certificate from a trusted authority. For in-house databases, trusting the server certificate is normal and very common.
Now we are ready to hit connect. And there we go. We are in.
Now we have got SQL Server installed, and we have got SSMS running so we can manage it properly. Now we are ready to actually start building something. Let me know in the comments how you like today's video and, as always, live long and prosper. See you in the next video.Quiz Q1. What is the main reason for choosing the "Custom" installation option when installing SQL Server Express in this lesson?
A. It installs only the minimum required features.
B. It allows you to see and understand what is being installed.
C. It automatically configures SQL Server for remote access.
D. It is faster than the "Basic" installation option.
Q2. When SQL Server asks for the download target location during installation, what is being requested?
A. The location where your future databases will be stored.
B. The location for the SQL Server Management Studio files.
C. The temporary location for SQL Server installer files.
D. The network location for remote clients.
Q3. What should you do if you see a warning about Windows Firewall during the SQL Server installation pre-flight check?
A. Stop the installation and fix your firewall immediately.
B. Ignore it if you are installing locally and not opening the server to the internet.
C. Reinstall Windows before continuing.
D. Cancel the installation and choose a different SQL Server edition.
Q4. What is the recommended practice in this course regarding exposing your SQL Server to the outside internet?
A. Open ports to allow internet access for easy sharing.
B. Use only SQL logins and disable Windows Authentication.
C. Avoid exposing SQL Server to the internet and keep it for in-house use.
D. Install a firewall exception for every possible service.
Q5. What is an "instance" of SQL Server as described in the lesson?
A. A single user session connected to SQL Server.
B. A separate computer running SQL Server.
C. A specific installation or copy of the SQL Server engine on a machine.
D. A type of SQL Server license.
Q6. If you want to create more than one database, what do you need to do?
A. Install an additional SQL Server instance for each database.
B. Create multiple databases inside the same instance.
C. Purchase a separate license for every database.
D. Use Azure cloud for more than one database.
Q7. For Access users, which feature must be installed with SQL Server to meet the needs outlined in this course?
A. PolyBase
B. Replication Services
C. Database Engine Services
D. Reporting Services
Q8. What is the recommended authentication mode for connecting Access to SQL Server in a small office?
A. Mixed mode authentication
B. SQL Server authentication only
C. Windows authentication
D. Anonymous authentication
Q9. Do you need a Windows domain or domain controller to use Windows Authentication with SQL Server in this setup?
A. Yes, a domain controller is required.
B. No, it works perfectly with just a few networked computers.
C. Yes, only in large organizations.
D. No, but only if you use SQL logins.
Q10. What does SQL Server Management Studio (SSMS) provide in relation to SQL Server?
A. It is the database engine that stores data.
B. It is the control panel/interface to interact with SQL Server.
C. It is a cloud backup tool.
D. It is the licensing verification tool.
Q11. According to the lesson, what should you do if prompted to restart your machine after installing SQL Server or SSMS?
A. Ignore the prompt, it is unnecessary.
B. Only restart if you are using Windows 11.
C. Restart your machine for a clean setup.
D. Uninstall and reinstall the software instead.
Q12. On the SSMS Connect screen, what is the most important information to specify?
A. The language for queries
B. The Azure data center location
C. The server name (machine name and instance)
D. The license key
Q13. If you encounter a "self-signed certificate" warning when connecting to SQL Server through SSMS in this course, what should you do?
A. Cancel the connection and reinstall SQL Server.
B. Trust the server certificate for local/in-house use.
C. Buy a new certificate from DigiCert immediately.
D. Disable encryption entirely.
Q14. Why is it okay to trust a self-signed certificate for local or small in-house SQL Server setups?
A. Self-signed certificates are always secure, regardless of use.
B. Encryption is only required for cloud databases.
C. On local networks, you control both the server and clients, so trust is inherent.
D. Windows automatically trusts unknown certificates.
Q15. Why does the instructor recommend avoiding wizard-based database migration tools initially?
A. They never work at all.
B. They always lose your data.
C. They hide too much of the process and may not produce desired results.
D. They are not compatible with Access databases.
Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-B; 7-C; 8-C; 9-B; 10-B; 11-C; 12-C; 13-B; 14-C; 15-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers the process of downloading and installing SQL Server Express, followed by setting up SQL Server Management Studio, commonly called SSMS. My goal in this lesson is to guide you through the entire setup, explaining every important choice along the way and making sure that by the end, you have a functioning SQL Server environment.
We start by getting SQL Server Express. The exact version might differ depending on when you're watching, but the principles are the same. Right now, it's SQL Server 2025, but if a newer version is out, you should just choose the latest SQL Server Express edition. Once the installer is in your Downloads folder, I recommend running it with the custom install option. This is not because we're doing advanced configuration but rather to show you exactly what is being installed. The basic installation skips over important information, which is fine if you just want a working server, but since we are Access users planning to connect to our SQL Server from Access later, it's valuable to slow down and understand what is happening.
Throughout this lesson, you'll notice I explain every screen in detail. I do this because I know you'll have questions, and rather than deal with them all later in the forums, it is better to discuss them up front. We will accept most defaults, but my focus is on teaching you, not just walking you through.
The installer will first ask where to temporarily download setup files. The default location is fine unless you have a special reason to change it. Remember, this location is only for the installer's files, not your databases.
After launching the installation center, choose the new SQL Server standalone installation option. There are other options for business intelligence tools and upgrades, but for now, we just care about getting the database engine running so Access has a place to connect.
Next, accept Microsoft's license terms. SQL Server Express is free, even for commercial use. Then you'll see a prompt asking if you want to use Microsoft Update. Personally, I turn off automatic updates because I like to control when updates happen, just in case anything goes wrong and I need to roll back changes. It's up to you, but whichever you pick, just be intentional with your decision.
During installation, the server performs a pre-flight check. Green check marks mean all is well. Occasional warnings, like one about Windows Firewall, are normal on a local setup and aren't usually a problem at this point. Red Xs would indicate a fatal error, but as long as there are none, you're fine to proceed.
A critical point in this lesson is that we're working with SQL Server installed on your own local network for your users. I strongly advise against opening your database server up to the internet. I speak from experience here. Many years ago, I hosted a web server myself and ended up with some serious security incidents. It was enough to convince me to always let the professionals handle internet-facing services. So for this course, we are sticking with local in-house use on your own network.
Next comes the prompt about the Azure extension for SQL Server. We aren't using Azure in this course. If you ever move your databases into Microsoft's cloud later, you can always install this extension then. For now, leave it out to keep things simple.
The next decision is about which components to install. For Access work, all you truly need is Database Engine Services. This is the core of SQL Server and what actually stores your data. Other features like replication or full text search can remain checked if they're selected by default, but they aren't needed for what we're doing. As long as Database Engine Services is selected, you're good.
SQL Server organizes itself around the concept of instances. An instance is like a separate copy of SQL Server running on your machine. The default for Express is called SQLExpress. Go with the default. When you connect from Access, you'll use the computer name and the instance name together to identify your server. For learning purposes, it's best to follow along with me and stick to the default setup.
A lot of new terms show up here. Remember, an instance is not a database. One instance can host multiple databases. Access connects first to the server, then to the instance, and then to a particular database inside that instance. In the majority of cases, you'll only need one instance on one computer, and inside of that, you can create as many databases as you like.
Next, you'll set service accounts and startup options. For most uses, the default service settings are perfect. SQL Server will use built-in Windows accounts and start itself automatically. There's no need to change anything here.
Authentication is the next important setting. We're using Windows authentication, which means SQL Server trusts your Windows login. This is simple and secure for in-house use and works well with Access. You don't need a separate SQL Server username or password, so there's less to manage.
It's important to note that you don't need an official Windows domain to use Windows authentication. Even in a small office with only a few networked PCs, as long as each user has a Windows login, the system works. For example, if you set up SQL Server on one PC, just make sure the other users have Windows logons set up on that machine too, using the same usernames and passwords for ease of use. This approach is tried and true for small workgroups.
Once you're ready, let the installation begin. This part can take a few minutes - plenty of time to get some coffee. When it's finished, SQL Server is installed and ready. You should see a list of green check marks. If install isn't successful, your best bet is to seek help in the course forums.
With the engine running, the next step is to install SQL Server Management Studio, or SSMS. SSMS is the application you'll use day to day to manage your server, create tables, run queries, administer security, and more. Unlike early versions, the management tool is now a separate download.
Download and launch the SSMS installer. There are a few extra tool options offered, but for our purposes, the standard install is perfect. During setup, you might notice prompts about tools for migrating your Access databases. While these migration utilities have improved, they often hide much of the process and don't always deliver the results you want. Like with the good and bad wizards in Access, automatic migration tools are not ideal for learning or full control. I'll show you these later, but for this course, we'll handle the migration process manually, so you fully understand what's happening.
Once SSMS is installed, you might see a prompt to restart your computer. Be sure to do this to avoid any strange issues, especially if this is your first time installing SQL Server. After restarting, launch SSMS. I recommend pinning it to your taskbar for easy access.
When you first start SSMS, you'll be asked if you want to sign in with a Microsoft or GitHub account to sync settings or use Azure features. For what we are doing, this is unnecessary, so just skip this step. Now you're at the connect screen, where you specify which server and instance you want to connect to. If you've installed SQL Server on your own PC, enter your computer name, followed by backslash SQLExpress, which is the instance name. For example, if your machine is called Kirk, you'd type Kirk\\SQLExpress.
Because we're using Windows authentication, SSMS uses your Windows login credentials automatically. On a first connection, you may encounter an encryption warning about a self-signed certificate. This is totally normal for a new local SQL Server install. You just need to enable the "Trust server certificate" option and connect. For in-house servers, this approach is safe. In a larger or public setting, you'd install a certificate from a trusted provider, but that isn't necessary for our small office setting.
Once you've connected, you are now set up with SQL Server and SSMS and ready to start managing your databases. From here, we'll move forward with building tables, managing data, and connecting Access.
If you found this overview helpful, you can find a complete video tutorial with step-by-step instructions on everything we discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Downloading SQL Server Express from Microsoft
Installing SQL Server Express step by step
Selecting the custom installation option
Choosing download target location for setup files
Running SQL Server Installation Center
Starting a new SQL Server standalone installation
Accepting SQL Server license terms
Configuring Microsoft Update options
Understanding SQL Server pre-installation checks
Firewall warning during SQL Server installation
Explanation of local network vs internet SQL Server
Handling Azure extension prompt during SQL Server setup
Selecting SQL Server features to install
Database Engine Services as required component
Naming and understanding SQL Server instances
Instance vs database terminology in SQL Server
Default instance selection best practices
Configuring SQL Server services and accounts
Configuring authentication mode: Windows Authentication
Windows Authentication without a domain
Accessing SQL Server in small office setups
Monitoring SQL Server installation progress
Installing SQL Server Management Studio (SSMS)
Downloading and running SSMS installer
Accepting default SSMS installation options
Advice about Access to SQL Server migration tools
Restarting Windows after installation
Launching SSMS for the first time
Pinning SSMS to Windows taskbar
Skipping account sign-in during SSMS setup
Connecting to local SQL Server instance in SSMS
Dealing with SSMS self-signed certificate warning
Trusting the server certificate for local setup
Successful connection and first use of SSMSArticle In this lesson, I will guide you through the process of downloading and installing SQL Server Express and then setting up SQL Server Management Studio (SSMS). This setup will give you a full-featured SQL Server database engine running on your own computer, along with the management tools you will need to create and administer databases. I will explain each step so you understand exactly what is happening, which will make it much easier to manage your server and troubleshoot any issues in the future.
Start by downloading the latest version of SQL Server Express from Microsoft's official website. The specific links and version numbers may change, but you are looking for SQL Server Express, which is Microsoft's free edition of SQL Server. It is perfect for learning, development, and small-scale production use. Once you have downloaded the installer (it will appear in your downloads folder), run it. When prompted, choose the custom installation option instead of the basic one. The custom option lets you see exactly what is being installed and gives you a better understanding of how the components fit together. It does not require you to change anything fancy, but it is helpful to see the details as you learn.
When the installer asks where to save the setup files, just accept the default location unless you have a specific reason to change it. This location is only for temporary setup files, not your future databases.
Once the installer is finished copying files, you will see the SQL Server Installation Center. Here, select the option for a new SQL Server standalone installation. This is the main database engine. Other options in this menu are for tools like reporting or for upgrading older versions; you can ignore those for now.
Accept the license terms. SQL Server Express is free to use, even in commercial environments, as long as you are within its limits. Then you will be asked if you want to use Microsoft Update to keep SQL Server up to date. This is up to you. If you prefer to install updates manually (which some administrators do to avoid unexpected disruptions), turn this off. Otherwise, you can leave it on to get updates automatically. Either way works for our purposes.
Next, SQL Server will run a system check - sometimes called a "pre-flight check" - to make sure your computer is ready for installation. If you see green check marks, you are fine to proceed. Warnings are usually not fatal (for example, a Windows firewall warning is common, especially when installing on a local machine). If you see a red X, there is a blocking issue you must resolve, but assuming you do not, continue.
You may also see an option for the Azure extension for SQL Server. Azure is Microsoft's cloud platform, and this tool lets you connect your local SQL Server to Azure for cloud-based management and monitoring. We are focusing on running SQL Server on your own machine, not in the cloud, so you can skip or turn off this feature. You can always add it later if you decide to experiment with Azure.
Now it is time to choose which SQL Server components to install. For our purposes - especially if you are coming from Microsoft Access and just need a place to host your data - you only need "Database Engine Services." This is the core part of SQL Server that actually stores and serves your data. Other options, like full-text search, PolyBase, or replication, are advanced features you do not need right now. If these happen to be checked, leaving them on is not a problem, but you do not need to select extras for this course.
You will then choose an "instance" name. An instance is essentially a specific installation of SQL Server on your machine. SQL Server Express uses "SQLEXPRESS" as the default named instance. Stick with this standard unless you have a strong reason to choose something else. Keeping the default will make it easier to follow along and troubleshoot later.
It is important to understand what an instance is: one instance can host many separate databases. When you connect from Access, or another tool, you specify the server (the machine name), then the instance, then the database. Most people only need one instance. You can add more later if you want to experiment, but for now keep it simple.
The next screen manages how SQL Server runs as a Windows service. For personal or local setups, you should accept the defaults. The database engine will be set to start automatically with Windows, using built-in accounts. There is no need to tweak anything for most small or home office environments.
Next, you come to authentication mode, or how users will log into SQL Server. Choose Windows Authentication. This allows SQL Server to trust your regular Windows logon. For a small office or local setting, this is the most secure and seamless way to manage access. You do not need to create special SQL Server usernames and passwords at this stage; the Windows accounts on your machine will have access. Even in small peer-to-peer networks (not using a Windows domain), as long as matching usernames and passwords exist on both the server and the client machines, users will be able to connect with their usual credentials.
For example, say you have three users - Joe, Bob, and Sue - each with their own usernames on their own computers. If you install SQL Server on Bob's computer, just make sure that Joe and Sue also have accounts (with matching usernames and passwords) on Bob's machine. When they access SQL Server from their own computers, their Windows credentials will work seamlessly.
Continue through the remaining steps. SQL Server will install the selected components - a process that usually takes only a few minutes on modern computers. When you see all green check marks at the end, you are done. If you are told to restart Windows after installation, go ahead and do it.
With the SQL Server database engine installed, your next task is to install SQL Server Management Studio (SSMS). SSMS is the main tool you will use to interact with your databases: creating tables, running queries, managing security, and more. Microsoft now ships SSMS as a separate download, so go to the official Microsoft SSMS page and download the installer. If one download link does not work, try scrolling the page - sometimes there are multiple installer links and one may be more up-to-date.
When the SSMS setup program launches, you can accept all the default options and go straight to install. You do not need any of the optional extras like AI tools or migration wizards for this course. SSMS will give you everything you need.
Once installed, if prompted to restart your computer, do so before continuing. After the restart, find SQL Server Management Studio in your Start menu (search for "SSMS"), and launch it. You may want to pin it to your taskbar for quick access.
When SSMS opens for the first time, it may ask you to sign in with a Microsoft or GitHub account. You can safely skip this unless you plan to use Azure or sync your settings. It does not affect your ability to connect to your local SQL Server.
Now you are ready to connect to your SQL Server instance. The main thing you need is the server name, which is your computer's name followed by a backslash and the instance name - for example, "Kirk\\SQLEXPRESS" if your computer is named Kirk and you accepted the default instance name. SSMS defaults to Windows Authentication, which is what you want - there is no need to enter a username or password.
When you try to connect for the first time, you might see a message warning you about an untrusted server certificate. This is normal for new, local SQL Server installations. The server is using a self-signed certificate, and SSMS is prompting you to confirm that you trust it. On the connection screen, check the box for "Trust Server Certificate" and connect again. For a local database like this, that is completely safe.
At this point, you should be connected to your SQL Server instance and looking at the SSMS dashboard. You are now ready to start building databases, creating tables, and exploring SQL Server's features. From here, you can continue learning how to create and manage your own databases, and eventually migrate your Access data if that is your goal.
Remember, this setup is for local or in-house use only. If you want to make your database accessible from the internet, you will need to consider professional hosting and additional security, but for most Access users and small offices, keeping SQL Server local is the safest and simplest option. Now that your environment is ready, you can move on to creating your first database. |
