SQL Server administration toolset

You will learn to use two tools for SQL Server administration. One tool is embedded in Visual Studio, while the other (SQL Server Management Studio) provides full-featured DDL, DML, and administration capabilities.

.

This section covers the database administration tools you can use with SQL Server.

You essentially have two database administration tool choices:

  1. Visual Studio database tools
  2. SQL Server Management Studio (Start > Run > ssms)

.

There are two other choices. You can use “SQL Server Management Studio Express Edition” (if it’s installed, then Start > Run > ssmsee), or you can run a command-line tool. The two “management studio” products enable you to administer any version of SQL Server, but the full (non-express) version offers additional capabilities.

.

Getting started with the Visual Studio database tools

This section enables you to get started with the database tools that are built into Visual Studio.

For best results, open your warp server-based website. On the View menu, choose Server Explorer. Your first task is to add a new connection. After you successfully finish this step, you will have an icon in the Server Explorer “Data Connections” list. Here is what Solution Explorer looks like before you add the new data connection:

Server Explorer fresh and new

.

Right-click Data Connections, and choose Add Connection. Complete the Add Connection dialog (as shown below).

Connection dialog

.

Make sure that you:

  • Enter the full host name of the database server (mssql.warp.senecac.on.ca)
  • Enter your own credentials
  • Enter the database name, which is the same as your account name

.

Before you click OK, click the Test Connection button. If successful, you will see:

Test Connection message

.

Then, when you click “OK” on the Add Connection dialog, the connection is created, and appears in the Data Connections list in Server Explorer:

Server Explorer showing the new connection

.

At this point, we suggest that you right-click the connection name, and then choose Rename to modify its name to something more meaningful to you. We suggest that you use a combination of the server name and your account name. Later, as you add more connections to different servers and/or databases, it will become important to quickly and easily identify each connection.

The next thing you can do is to click the tree/node expander (the plus + sign), to see the database objects, as shown below:

Server Explorer renamed and expanded

.

Please note the following:

If you try to expand the “Tables” node in your personal database, you will find that it’s empty. That’s because there are no tables in the database yet.

If you try to expand the “Database Diagrams” node in your personal database, you will be asked if you want to configure database diagramming objects. Answer “yes”.

Configure database diagramming objects

.

Finally, the following image shows three connections in Server Explorer, representing connections to the personal database, as well as to the two sample databases that we can use for read-only operations.

Server Explorer showing many data connections

.

Getting started with SQL Server Management Studio

As stated in the Books Online document:

Microsoft SQL Server Management Studio is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SQL Server Management Studio combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.

.

You can start Management Studio by locating its shortcut on the Start menu, or by using the run command:

Start > Run > ssms

When Management Studio loads, you must provide server and credential information, as shown below:

.

The initial view of Management Studio includes a left-side Object Explorer (navigator), and a right-side details pane, as shown below:

Management Studio initial view

.

Please note the following about YOUR management capabilities while using this tool to work with the mssql.warp.senecac.on.ca server:

  • Creating a new database – you cannot do this; you don’t have the permission
  • Expanding the Databases node in Object Explorer – you can do it, but you can then open only the databases in which you have permission
  • Although you can expand the Security node, you will see only your own login name
  • Your ability to work with other nodes will be limited

.

Opening your own database

Every BTI420 student has a personal database on the warp server cluster. As you know, the database name is the same as the account name used to access both the database server and the web server.

Please locate your personal database, and expand its node. Then, expand the Tables node. You will likely see only a “System Tables” node, because you have not created any tables yet.

.

Opening the Northwind sample database

You can also open one of the sample databases in the Object Explorer. You have read-only permissions, so some operations may not succeed, or you may be shown informational or warning messages.

Locate the Northwind database, and expand its node. Then, expand its Tables node, and you will see a list of tables; see the image to the right for details.

If you want to explore a specific table, click its name, and you will see Columns, Keys, Constraints, etc. Expand the Columns node to see the list of columns.

If you right-click a table name, you see a number of options, including these useful ones:

Open Table – will open the table, showing its data in the details pane

Design – will open the table designer, showing its column names, data types, column properties, and so on (read-only); as shown below:

.

Fully-qualified names for database objects

As a software developer, you are acutely aware that you need to understand object naming, and that different contexts have different naming schemes. For example, you know about file system names for paths, files, and folders. Citing another example, you know about .NET Framework class names, where a multipart dot scheme is used to identify classes and their members.

When working with database objects (including tables, views, and stored procedures), we need to be aware of the required naming scheme, known as the “database schema“. This will enable us to reference objects in a clear and unambiguous manner. Here are some descriptive points to consider for understanding database schema in the SQL Server environment:

  • A database schema is a distinct namespace of objects
  • You can think of a schema as a container for objects (objects include tables, views, etc.)
  • A database schema is typically used for 1) identifying or referencing objects, and 2) configuring and applying security
  • Schemas are identified with a schema name
  • The default schema name is “dbo”; this is automatically created by SQL Server
  • You will need to use the schema name whenever you have to enter a fully-qualified name/reference to an object.

.

Here is an example of how to use a schema name:

Assume the following:

  • The server name: mssql.warp.senecac.on.ca
  • Your database name: bti420_081a03
  • The default schema name: dbo
  • An example table name in your database: Users

.

When using SQL DDL or DDL statements, you may have to refer to the table in different ways, depending where your context is:

Users
dbo.Users
bti420_111a03.dbo.Users

.

Create a table by copying it from Northwind

In this section, you will create a table in your personal database by copying its definition AND data from the Northwind sample database.

Note: Use a prefix of nw on the table names you copy from Northwind. This will enable you to quickly and easily identify your database’s tables that were copied from Northwind. Later, if you use the same procedure to copy tables from the AdventureWorks database, use a prefix of aw for those tables.

.

First, click/select your database node in Object Explorer. Then click the toolbar’s New Query button (or choose it from the File > New menu). Enter the following DDL. Use the toolbar’s “Parse” (blue check mark) tool to parse and test your code, and the “Execute” (red exclamation mark) tool to execute your code. Then, right-click your Tables node in Object Explorer, and choose Refresh, to see the new table.

select *
into nwEmployees
from Northwind.dbo.Employees

.

We suggest that you copy the other Northwind tables to your own personal database. This will enable you to perform DDL and DML that modifies the tables and their contents. See the last section in this document for a script that will help you copy the Northwind tables, including all the relationships and constraints.

.

Create a table by using a graphical tool

In this section, you will create a table in your personal database by using a forms-driven graphical tool.

First, expand your database node in Object Explorer, and click/select the Tables node. Right-click and choose New Table. A form appears at the top of the details pane, and a properties browser appears at the bottom of the details pane. As appropriate, enter column names, data types, and properties for your table. The following shows a partly-defined “Users” table:

Table create

.

During or at the end of the definition, you can save your work. The first time you save, you are prompted for a name. Enter a name, and click to continue.

Table save

.

Create a table by entering your own DDL

You can create a table by entering your own data definition language (DDL). First, click/select your database node in Object Explorer. Then click the toolbar’s New Query button (or choose it from the File > New menu). Enter your DDL as required. Use the toolbar’s “Parse” (blue check mark) tool to parse and test your code, and the “Execute” (red exclamation mark) tool to execute your code.

SQL DDL example

.

Using a query window

At any time, you can have one or more query windows open. This enables you to do ad-hoc querying by specifying your own DDL and DML.

For best results, click/select the database name in Object Explorer. Then click the toolbar’s New Query button (or choose it from the File > New menu). Enter your DDL and/or DML as required. Use the toolbar’s “Parse” (blue check mark) tool to parse and test your code, and the “Execute” (red exclamation mark) tool to execute your code.

Please note that you CANNOT save your queries to the database server as query or “sql” file objects. You MUST discard your queries, or save them to a local file system store.

However, please also note that you CAN save some of your queries as View or Stored Procedure database objects. You use Management Studio’s tools to develop your View or Stored Procedure, and then they will be saved with the database.

.

Use a script to copy Northwind tables into your personal database

Your professor has created a script that will help you copy Northwind tables into your personal database. Get the following document, read its instructions, and implement them.

Copy Northwind Tables

.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: