Join Adam Wilbert for an in-depth discussion in this video Using SQL Server Management Studio, part of Migrating Access Databases to SQL Server.
- Just like how we use Microsoft Access to get into and manage the contents of our Access database files, we'll use a program called SQL Server Management Studio to control the inner workings of the database server. Let's go ahead and start it up and take a quick tour. I'm going to press my Windows key here and just do a search for SQL Server 2014 Management Studio. I'll go ahead and select it from the list here and it'll start up. Next, I need to log in to the server. During the installation process, we specified that we wanted to use Windows Authentication as one of our options, which is chosen from the list here.
Then we also specified that my current user account would be one of the administrators. So, that user name is already populated here. So I'm just going to accept these default options and press Connect. That'll connect me to the SQL Server instance that's already running on my system, and over here on the left, I can see its name here. So I first have the file path, the name of my computer here, and then the name of the SQL Server instance, which is what we named it when we installed it, which is called "SQLEXPRESS2014". Now the Object Explorer window over here on the left is just like the Navigation Pane that you're used to from inside of Access, where we see all the objects within the database file.
The Object Explorer in Management Studio allows us to see all the objects running on the server. We can drill down into each to see the child objects. For instance, I'll expand this Databases folder here, and then I'll expand this System Databases folder here to reveal the four system databases of master, model, msdb and tempdb. These are the databases that SQL Server uses to keep the system organized. If you've ever explored the hidden objects that live inside of your Access databases, then it might be helpful to know that these databases here perform similar tasks here in SQL Server that the hidden tables perform inside of an Access database.
In order to create a new database, what we could do is right-click on the Databases folder here, and choose New Database. On the New Database window, I get to give it a name, and I'm just going to call this "Temp". When I do that, I get two files down here that get created. The first one, which it took the name here that I just typed in, is called "Temp", and that's going to be the main database file. The second one is "Temp_log", and this is the log file. If I scroll over here to the right, we'll see the file path of where those are going to get stored on my system.
If I want to make a change to that, I'll scroll right a little bit more and press these buttons here. But I'm going to go ahead and accept these default locations, and just press OK at the bottom. That'll create a new temporary database over here in the Object Explorer. If you don't see it automatically, go ahead and just right-click on the word "Databases" here, and press Refresh to refresh the server, in which case you should now see that Temp database. If I expand it open, we can see the different objects that are inside of the Temp database. And this is starting to look a lot like the Access Navigation Pane. Here I see a folder for Tables.
Down below, I have one for Views. These are equivalent to queries inside of Access. Here's a folder for Programmability, and this would be just like the Macro objects inside of an Access database. Notice that you don't have folders for things like forms and reports. That's because SQL Server is just used for the storage and retrieval of your data. The formatting and user interface elements need to be provided by an outside source. Sometimes this is a website that's connected to the database. Sometimes it's a custom written application that provides the user interface to the data.
In our case, we're going to continue using Access to provide us with an easy way to create data entry forms and retrieve our data from the same report objects that we're already used to creating, but we'll do it by storing our data in SQL Server in the back end. Let's go ahead and continue poking around to get our bearings. I'm going to go ahead and expand this Tables group here, and you can see I've got two folders, but if I expand those, there's nothing inside. Currently the Temp database doesn't have any tables. So let's add one. I'll right-click on Tables and choose Table from the pop-up.
That'll open up this new table editing window here that's an almost exact copy of what we've seen inside of Access. At the top, I get to specify the different columns that'll make up the table, and then, down below, I have the Properties for each column. Let's go ahead and create a column called "TemporaryID". I'll go ahead and press Tab to move over here to the Data Type, and we'll select a data type from the list. Now the data types inside of SQL Server are a lot different than the ones that are available inside of Access. Normally, in Access, we might want to choose an Autonumber field to create an Autonumber sequence. In this case, if we scroll through the list, we're not going to find Autonumber.
But what I can do is scroll until I find the "int" data type, which stands for "integer", and then, in the Properties down below, let me go ahead and pull this up a little bit, we'll scroll down and we'll find one of the Properties that's called "Identity Specification" here. Let's go ahead and expand it open here to see some options underneath, and we can set the "Is Identity" property here, from "No" -- I'll just double-click on it -- to change it to "Yes". Then I get two additional properties: "Identity Increment", which is currently set to one, and "Identity Seed", which is also set to one.
Now in Access, when you create an Autonumber field, it automatically starts at number one and increments by one for each new record. Here, inside of SQL Server, though, we can change those options. For instance, if I wanted to start on number 100, I could do that. If I wanted to increment by two each time instead of one, I can just change that property there. Let's go ahead and add in another column, the second one I'll call "Name" here. I'll go ahead and press Tab to go over here to the Data Types, and the default data type is "nchar(10)". "Char" you're going to see a lot, that stands for "character", and this is one of the text data types.
The "n" indicates that it'll also store Unicode characters for international names, and then the "10" is just like the field length property that we have in Access. In fact, if you come down here to the Properties, there's a property for Length and it's set to 10. If I wanted to store more than 10 characters, like 50, I'll just type it in here and press Enter, and you'll see that that number updates up here in the data type up at the top. I can also change it up here, so if I wanted to store 100 instead, I'll just type in 100 in the parentheses, and when I press Enter, and then click on it again, you can see that the Length property has also changed to 100 here.
Now, in order to save my table, I'll press this icon up here on the Toolbar to save it, and I'm just going to accept the default name of "Table_1" and we'll say OK, and then I'll close this tab. Now I've created a new table, and in order to see it, I'll right-click on the Tables folder here, and choose Refresh, and I can see the table here. Now, unlike in Access, we can actually expand the table here in the Object Explorer. Inside of the table, we have objects for Columns, and if I expand that, we'll see the two columns we just created. I can expand the Keys here, and I can see that I don't have any Keys.
If we wanted to add a Key, just right-click on the table, go into the Designer, and we'll choose to add a Primary Key to that first field there. If I close it and save it, I can then go ahead and right-click on the Table, Refresh it, and then expand the Keys folder to see that we have a Primary Key assigned to this table as well. Let's go ahead and collapse this down a little bit, and we'll take a look at this Views option here. Views are just like Queries inside of Access. To add a View, I'll right-click on it, say New View, and we'll get this Add Table window, which is exactly the same as the Show Table window in Access.
I'll go ahead and double-click on this table here to add it to my Query Designer back here. Let's go ahead and close the Add Table window, and in this designer, we have the two panes that we're used to. This upper portion that shows me all the tables we're using, and the bottom portion that shows me the different criteria. I also have a SQL window here that shows me the code that's being generated, and on the very bottom, I can see a window here that'll show me the results of this query when I run it. Now, if I want to, I can adjust what windows are being shown on the screen. I'll just go ahead and click here to activate it, and then, on the Toolbar, I have these four buttons.
I can show the Diagram Pane or not, the Criteria Pane here, if I want to turn off the SQL Pane, I can just click right there, and I can also turn off the Results Pane here. Now I have an environment that's almost identical to the Query Designer from Access. Go ahead and pull this up a little bit. In order to add a field to our query, I'll just go ahead and put a checkbox here, and that adds it down here. I have a specific column for Alias. Let's go ahead and give this column an Alias of "First Name" with a space in it, and I'll go ahead and press Enter, and if I expand it, you'll notice that it puts in the square brackets, again, just like inside of Access.
I can also sort it using this option here; I can sort Ascending or Descending. I could specify a filter, which would be just like a Query in Access, and I also have the "Or" lines just like inside of Access. Now, the only real difference here, is that this design window is rotated 90 degrees from what you're used to inside of Access. But I find this to be a much more space-efficient layout, and I really like the way it works. When I'm ready to run my query, or my View, here inside of SQL Server, I'll press the same button you're used to inside of Access, this red exclamation mark.
That'll execute the SQL, and we'll get the results down here on the very bottom. Now, this table doesn't have any data, so that's why I'm seeing "Null". When I'm done creating my View, I'll just go ahead and close this tab, I'll go ahead and say "Yes" to save the changes to the following items, and I'll just accept this default name here of "View_1". We'll say OK; now if I right-click on my Views and Refresh it, and expand it, you'll see that we have a new View, which is equivalent to an Access query. If you want to delete an object, for instance, let's just go ahead and get rid of this whole Temp database, all you do is right-click on it and choose Delete from the pop-up menu.
I'll go ahead and Close existing connections so that there is no connections that are preventing it from being deleted, go ahead and say OK, and that database is removed from SQL Server. So I know that SQL Server, at first, can really feel intimidating. It's big and it's powerful and it's what the pros use, so it must be complicated, right? Well, as we've just seen, even if you have just a modest amount of experience with Access, then you already possess a wealth of knowledge that will be directly applicable to you in this new environment. As we dig further, you'll continue to see things for the very first time that will already feel familiar to you.
Access and SQL Server really are more similar than you might have thought, which makes transitioning to the new environment a little bit easier.
Watch this course to learn how to leverage what you already know to take that next step. Adam Wilbert reviews the basics of SQL Server Management Studio (SSMS) and introduces two workflows for migrating Access data: one using a simple export/import process and the other using the free SQL Server Migration Assistant (SSMA). He also shows how to convert Access to SQL data types, link Access to SQL data via an ODBC connection, and address special concerns for Access 2010 data.
- Identify three start modes available on the Service tab.
- Recognize the locations from which databases may be imported.
- Recall which databases are supported by the SQL Server Migration Assistant.
- Explain which types of Access column data are most likely to cause errors during the conversion process.
- Summarize how to create a backup.
- Determine where ODBC Data Sources are located.
- Recognize which table names the SQL Server will wrap by square brackets.