SQL 2005+

Creating SQL Server Databases Part 1

by Richard Carr, published at http://www.blackwasp.co.uk/SQLCreateDatabase1.aspx

The third part of the SQL Server Programming Fundamentals tutorial investigates the creation of new databases. This first task in any new database project requires the careful consideration of several key configuration options.

SQL Server Management Studio

The Microsoft SQL Server relational database management system executes on a Windows-based computer as a group of Windows services. These background processes run continuously and process any database activities that are required. However, as these are services, they have no user interface that permits their configuration.

SQL Server Management Studio (SSMS) is a tool that is distributed with SQL Server. This powerful utility provides a graphical user interface that connects to one or more local or remote SQL Server installations and allows the user to perform configuration and design tasks. These include the creation of new databases and their constituent parts, and the execution of queries and other SQL statements.

NB: SSMS can be installed using the original SQL Server CD-ROM or DVD-ROM. If you are using SQL Server Express Edition, SQL Server Management Studio Express edition can be utilised instead. If this tool is not installed, you can download it from the Microsoft SQL Server web site.

Using SSMS, databases can be created in two ways. Firstly, SSMS provides screens that guide you through the process. Secondly, SSMS can execute commands to create a database in a textual format using a query window. In this article, we will use SSMS's graphical user interface tools to create a new database for the JoBS project. We will look at the command-based approach later.

This article assumes that you have access to a SQL Server installation and have the details and login credentials required to connect to it and create databases.

Starting SQL Server Management Studio

By default, SQL Server Management Studio is installed into a program group named "Microsoft SQL Server 2005" in the Start menu. To start SSMS, simply click the associated shortcut. After a few seconds, the "Connect to Server" dialog box is displayed.

Connecting to a SQL Server Instance

The Connect to Server dialog box allows you to specify the details of the SQL Server that you wish to connect to and the credentials that you wish to use to authenticate with that server. The information that must be completed is as follows:

• Server Type. SQL Server includes various options for server types, including Database Engine for core database functionality, Analysis Services for analytical data warehouses, Reporting Services for reporting purposes and Integration Services for integration work. Other options may also be available depending upon your particular workstation configuration.
• Server Name. This allows you to specify the name of a SQL Server to connect to. Each SQL Server on your network will have one or more instances. An instance is a named installation of the SQL Server product. If the server instance you wish to connect to is the default instance, you simply provide the server's name. If a specific, named instance is to be used, this is provided in the format <server-name>\<instance-name>. For example, the figure above shows connection to the VIRTUALXP server's default instance. An alternative option may be VIRTUALXP\Instance2.
• Authentication. SQL Server's database engine provides two forms of authentication. Windows authentication uses your current login name to determine your level of access. SQL Server authentication requires that you provide a user name and password.

To connect to SQL Server in readiness for creating the JoBS database, select the "Database Engine" server type and provide the details of the server name, and optional instance name, and your authentication details. Click the Connect button to make the connection. Once connected, the SSMS main screen will be populated.