Microsoft SQL Server is an enterprise solution that holds and secures your critical asset – your data. Microsoft provides you with Management Studio to configure and set up your database. If you’re new to databases, it can be a little overwhelming when you first look into your database setup. Once you get to know the layout and design, the structure is less intimidating. It’s important to know how to set up your database for security and performance to save yourself some headaches in the future.
An Overview of Your Database
When you first open SQL Management Studio, you’re asked to log in. You can use your Windows user name or the SA user name and password you set up when you installed the software. You can only log in with a SQL user name and password if you set up mixed mode during installation. The engine opens and you’re presented with a list of database configurations on the left and a center working area on the right. The database looks something like the below screenshot:
This server has some databases already configured. For instance, the first two database names are “antiscraper” and “Antispam.” The server’s name is “FAIL.” Next to the server’s name is the version of SQL it’s running.
When you click the plus sign next to the database name, a list of tables displays. Since this is a new database setup, you will only see system tables. You must configure and create new tables for each of your databases. Below the list of databases, you’ll see “Security,” Server Objects,” “Replication,” and “Management.” For the most part, as a new database administrator, you’ll only need to use Security and Management. Replication is for enterprise networks. Server Objects are for backups, triggers and other anonymous server items.
Creating a New Database
Right-click the “Databases” icon and select “New Database.” A window opens where you can create and configure a new database. The most important part of this screen is the database name text box. This is the name for your database. For most small businesses or even development, you can leave all other properties as the default and SQL Server will take care of the rest.
A new database is listed, which is all you really need to do to create a database. However, a basic database is useless unless you create tables. Tables are the elements that store your data. Each column in the database relates to one particular part of your data. For instance, if you customers, you probably need a customer table. Right-click the tables icon and select “New Table.” The new table creation tool is shown in the center workspace.
The table creation tool defaults with one column. The first part is the name for your column, the second is the data type and the third is a check box that indicates if you want to allow nulls. You can allow nulls in any column except primary keys. Primary keys are unique columns that identify a record among all your other records. To set a primary key, right-click the left side of the column and select “Set Primary Key.” The easiest way to set a primary key is to create an “ID” field and set it as an integer. In the bottom panel, scroll down and set the properties as a auto-incrementing field. This means that SQL Server will automatically increment this column by 1, which means that the column data will always be unique. Most database administrators use other types of primary keys, but this is sufficient for a new, small database and its tables.
Each time you add a column, SQL Server prompts you for the next one. If you don’t need any more columns, you can just click the “Save” button. You’re prompted for a table name. In this case, you can name your table “Customer.”
If you plan to use this database for an open, public-facing web application, you should add a unique user name and password specific for the database. The one security advantage for this is that the user name is limited to only one database. You never use the SA (system administrator) account for connecting from a web application. If the SA password is hacked, the hacker has total control of your entire database server. If you create a separate user name and password specific to one database, the hacker can only gain access to your one database. This means that any backups are protected provided this user does not have access to your backups.
Click the plus sign next to the “Security” database option. Click the plus sign next to “Logins.” This lists all of your current users. Since this is a new database server, you only have SA and a few generic Windows logins for setup purposes.
Right-click the “Login” icon and select “New Login.” A window displays where you configure your new user. Give the user a name related to the database, so it’s easily recognizable when you go back to view your users. Add a password in the password text box. Remove the check boxes below the password check box if you are using this user name for an application. The check boxes force the user to follow password guidelines, and it will ask the user for a new password the first time he logs in. This will cause issues with your application.
Next, click the icon labeled “User Mappings” in the left column. This screen lets you map users to specific databases. Without access, the user can log in but can’t access the database. Check off each database you want to give the user access too. If this is a development computer, check off “db_owner” to give the user full access to the database. This also lets the user connect to the default schema, which is usually dbo.
These are just the basics for setting up a database and configuring a basic, local server. Databases can be complex parts of your network, and they house critical parts of a company’s infrastructure. Take a class at Udemy.com to learn how to manage your SQL Server properly.