SQL Server Database Administration and Design By Dave Peru, October 2011

SQL Server Database Administration and Design By Dave Peru, October 2011

Introduction Name

Handout Sheet

Developer’s Perspective - Story Outline

Create a Database

Create a New Data Model Data Model Diagrams Relationships and Indexes SQL Server Account Security SQL Scripts

Moving Database Files

Create Web Pages for Editing Database CSS Files and Data Entry Forms

Database Backups

Create a Database

Launch SQL Server Management Studio

Right click over Tables, select New Table from the popup menu:

Create Table1:

Set Primary Key:

Save Table 1 :

Right click over Tables, select New Table from the popup menu:

Create Table 2:

Save Table 2 :

Close workspace windows, <ctrl-f4> Expand Tables folder:

Create a New Data Model

Expand Database Diagrams folder:

Click Yes. Right click over Database Diagrams folder, add new diagram:

Select both tables, click Add:

Change view on Table 2, your diagram should look like the following:

Database Relationships

Graphical user interfaces and hidden handshakes:

Click OK, OK, adjust relationship object:

Reason for Relationships, Software Service Providers, Amazon S3, lost data VoltDB – massively large distributed databases with relationship integrity

Database Indexes – Increase Speed

Data Model Diagrams Right click over the spot, select menu item:

Click Add:

Click Close, Click :

Printing Data Model Diagrams

Print Problems, Select Copy Diagram to Clipboard:

Launch Word, expand margins, paste:

SQL Server Account Security

Expand Security folder in Object Explorer, right click over Logins, New Login:

Fill out the form for “user1”:

Right click Users folder under Security under MyDatabase:

Click on the eclipses:

Enter “user1”, click on the “Check Names” button, then click OK:

Expand the Schemas folder, right click over “dbo” and select “Properties” menu:

Go to the “Permissions” tab:

Click on the “Add” button, enter “user1”, click “Check Names”, click OK:

Working with SQL Scripts, Start in MyDatabase / Security / Users

You should see the following:

Select File Save As menu:

From MyDatabase Security Logins, script to clipboard the following:

You will see the following:

Click on the button to run the script. You should see the following result:

Select Script Action To Clipboard:

Paste into our script file:

Add “user1” as user of “MyDatabase”, script to clipboard, paste into script:

Right click over “dbo”, select “Properties” from popup menu:

Paste into our script file. Click on the button to run the script. You should see the following result:

Verify user1 exists and has the correct access rights:

Moving Database Files

Good Object Oriented Design for Code and Data Detach Database

Find database files, and cut to clipboard:

Navigate to “c:\demo1”, create “Database Files” folder, paste files to new location:

Attach Database:

Navigate to “c:\demo1\Database Files” and select MDB file:

Select “c:\demo1\sql\ DropCreateUser1.sql”:

Exit SQL Server Management Studio

Create Web Pages for Editing Our Database Launch Visual Studio:

Select New / Web Site… menu item:

Create a new solutions file for project settings. Select the solution root inside Solution Explorer:

In the “c:\demo1\sln” folder, save the website1.sln file:

Time to prepare to run a program generator called Mintiera.

Mintiera will create web pages we can use to edit records in our database. Delete App_Data folder:

Use Visual Studio Import Feature. Create App_Code folder:

Create “Admin” folder:

Mintiera web site:

Mintiera is code generator: Web pages, CSS friendly user controls, middle tier data access components, for editing records in a database

The way Mintiera works: 10 SQL data types cross 20 code snippets for CRUD Northwind Data Model:

Running Mintiera on MyDatabase:

Click on “Aready Registered, Login” link:

Mintiera uses Click-Once deployment, click button to launch program:

Enter Connection Parameters and click Next button:

Copy Connection String text into clipboard:

Return to Visual Studio. Edit web.config file:

Default values for SQL field types determined from data model. Use defaults, click Next:

Default values for related tables taken from data model, click Next button:

Mintiera supports 4 levels of data validation: Client-Side Javascript, web page presentation layer, middle tier data access component, and database validation. Default values for related tables taken from data model, click Next button:

You must specify a temporary folder for output (c:\tmp shown below):

Click Restart button and select Table2, click Next button:

Select the following for Table2 Primary and Foreign Key Settings, click Next:

Use Mintiera defaults for related tables, click Next:

Click “Generate Code” button for Output Settings:

Next, we need to import the generated Mintiera code into our Visual Studio project. Right click over Admin folder in Solution Explorer, and select Add Existing Item:

Select all the files in “c:\tmp\WebForms2” as shown:

Right click over the “App_Code” folder, select Add Existing Items…” from the popup:

Select all the files in “c:\tmp\App_code” folder to import all the middle tier data access components:

Again, right click over the “App_Code” folder, select Add Existing Items…” from the popup. And import all the App_Code_Support_Classes to Visual Studio:

Right click over the “css” folder, select Add Existing Items…” from the popup:

Import all the files from “c:\tmp\css”:

Import all the files from “c:\tmp\Javascript”:

Right click over the root folder, select Add Existing Items…” from the popup:

Import all the files from “c:\tmp\ GlobalApplicationCode”:

Select Build Solution from the Build menu:

Confirm Build succeeded:

Table1Script.sql and Table2Script.sql contain SQL script for stored procedures used by middle tier data access components for doing CRUD operations.

Navigate to “c:\tmp\sql”, select all files, click on the Open button:

Click <F5> to execute:

Click <F4> to close the window, exit the management studio, return to Visual Studio.

Set MyDatabase.aspx as the start page for Visual Studio. Hit <ctrl-F5> to launch brower:

CSS Files and Data Entry Forms

Mintiera uses a table-less web page design for page layout. No HTML-table tags. Table tags auto fill versus div-tag liquid fill.

This OuterDiv-InnerDiv CSS method works with the following browsers (no hacks): IE 6, IE 7, IE 8 regular mode, IE 8 compatibility mode

Firefox 3, Opera 9, Safari 3, and Google Chrome 4

The method uses only CSS to control layout. Content is separated from presentation. Remove the CSS file and you see the following:

CSS friendly controls can be modified for data entry forms. Restore CSS files and layout returns:

The idea of separating content from presentation is illustrated by CSSZenGarden.Com

Remove default backup file:

Click on Add button, select file and location, click on OK button:

Once completed, you will see the following:

SQL Server Agent

Script database backup to clipboard:

Create new SQL Server Agent Job:

Click on New button below:

File out Step 1 and click OK:

Job is now ready:

Click OK to complete. Test job: