Common uses of SQL Server on a SBS 2003 network [Windows Small Business Server 2003 Best Practices book excerpt]

G’day mates – it’s post-election Tuesday. It’s time to get back to work! Today’s virtual book reading is a passage about practical use of SQL Server on an Windows Small Business Server 2003 (SBS 2003) network. Enjoy!

 

Common Uses of SQL Server on SBS Networks

First and foremost, the greatest need I’ve seen for SQL Server 2000 on SBS networks is support for applications, such as Great Plains accounting software, and other narrow, vertical-market applications in the legal, medical, and instrument-repair industries (to name just a few).

Secondary uses for SQL Server 2000 on an SBS network include creating your own databases, importing databases from Microsoft Access, and learning SQL Server 2000 for MCSE certification purposes. (I’m just calling it like I see it.)

BEST PRACTICE: For more information on SQL Server 2000 specifics, I can send you to two free resources! First, visit Microsoft’s SQL Server Web site at http://www.microsoft.com/sql. Second, read the SQL Server Books Online (installed as part of the complete SQL Server installation process). The SQL Server Books Online are excellent!

Notes:

 Visit http://www.microsoft.com/technet for the latest updates for any Microsoft product.

SQL Server and SPRINGERS

No chapter on SQL Server, in any book, would be complete without having you create a database or a table or two, enter some data, and then display the data you’ve entered. This book is no exception. But again, understand that this exercise is almost an exception to the rule on SBS networks; you’re far more likely to work with third-party applications that sit on top of the SQL Server 2000 engine.

That said, revisit your friends at SPRINGERS. Because no third-party software exists that explicitly tracks dogs, you’ll create a database to accomplish this business purpose.

BEST PRACTICE: Whenever considering the creation of a database, be sure you understand the purpose for undertaking this project. Poorly considered database projects are like poorly written books: difficult to read and soon ignored.

First, you will need to install SQL Server 2000 in SBS 2003. Please use the readme text accessed by clicking the How to Install link when you insert Disc 5 from SBS 2003 premium edition to install SQL Server 2000.

In a nutshell, the SQL Server process is broken into three parts:

                      Installing SQL Server 2000

                      Installing SQL Server 2000 Service Pack 3a (again, current as of fall 2003)

                      Configuring the SQL Server Collation Settings

 

BEST PRACTICE: So here is a chance to do as I say and not as I’ll do for you. Heed the advice above and correctly install SQL Server 2000 based on the latest prescriptive guidance and return back to this spot to proceed with the chapter and create a database for SPRINGERS. See you back here in a little while.

Now get to work and create a database for SPRINGERS. Afterward, you will create a table and populate it with meaningful dog tracking information.

 Visit http://www.smbnation.com for additional SMB and SBS book, newsletter and conference resources.

To create the database for SPRINGERS, perform the following steps:

1                    Log on to the SBS server machine as Administrator with the pass­word Husky9999!

2                    Click Start, All Programs, Microsoft SQL Server, Enterprise Manager.

3                    Expand the following objects in the left pane: Microsoft SQL Serv­ers, SQL Server Group, SPRINGERS1 (Windows NT).

4                    Highlight SPRINGERS1 (Windows NT) and select View, Taskpad to display the taskpad in the right pane with the General tab ini­tially displayed. Notice server and configuration information is dis­played. The operating system is incorrectly identified as Microsoft Windows NT.

5                    Click the Wizards tab and the resulting screen should look like Fig­ure 14-3.

 

Figure 14-3

The Wizards page on the taskpage offers a wizard link to accomplish nearly any task.

Select the Create a Database link under Set up a Database on the Wizards page of the taskpad. The Create Database Wizard -SPRINGERS1 appears and the Welcome to the Create Database Wizard screen is displayed. Click Next.

 

 Visit http://www.microsoft.com/technet for the latest updates for any Microsoft product.

7.         The Name the Database and Specify its Location screen appears. In the Database name: field, type SSLDOG. Accept the settings for the database location in Database file location:. Accept the settings in the Transaction log file location: field. Your screen should look similar to Figure 14-4. Click Next.

Figure 14-4

Naming the database and accepting file locations on the Name the Database and Specify its Location screen.

 

8.         The Name the Database Files screen appears. Accept the default file name of SSLDOG_Data under File Name and type 10 under Initial Size (MB). Click Next.

 

9.         Accept the default settings on the Define the Database File Growth screen. Click Next.

 

10.       Accept the default settings on the Name the Transaction Log Files and click Next. Note the initial size of the transaction log file will be 1 MB.

 

Chapter 14 Database Management With SQL Server 2000

 

11.       Accept the defaults on the Define the Transaction Log File Growth page and click Next.

 

12.       Click Finish on the Completing the Create Database Wizard page.

 

BEST PRACTICE: Note because this is a native SQL Server 2000 wizard, it does not have a link to save, print, or e-mail your configuration settings (like you’ve seen in native SBS 2003 wizards).

But the completion configuration information can be selected via the mouse (hold down the right mouse button and drag across the text) and then copied by pressing CTRL-C. Open Notepad or some other text editor and paste this text. Then save the file as part of the SBS network notebook. With these simple steps, you will have the whole database configuration at a glance!

 

13.       Click OK when the Create Database Wizard dialog box notifies you that you have successfully created the database.

 

14.       You are asked whether you want to create a maintenance plan for the database SSLDOG on another Create Database Wizard dialog box. Click Yes.

 

BEST PRACTICE: It is critical that you set up a daily maintenance plan for your key databases (the master database and any databases you create). On more than one occasion, I’ve had to rely on the SQL Server-based database backups when a traditional backup tape failed me. A SQL Server-based database backup places a bona fide copy of your database on another part of the SBS server machine’s hard disk. It’s unbelievably valuable!

 

15.       The Database Maintenance Plan Wizard launches. Click Next after reading the Welcome to the Database Maintenance Plan Wizard screen. Note that the Welcome screen tells you which tasks the Data­base Maintenance Plan Wizard will run, including running database integrity checks, updating database statistics, and performing data­base backups (inside of SQL Server 2000).

 

16.       Select master and SSLDOG under the Database column on the

 

Select Databases screen, as seen in Figure 14-5. Click Next. Kindly note that Figure 14-5 displays the SSLDOG database as being

 Visit http://www.microsoft.com/technet for the latest updates for any Microsoft product.

selected in the Database column, but believe me that master is also selected. Given the constraints of the user interface for that dialog box, I can’t get both selections to appear in a single figure (master is selected at the top of the databases list and has been scrolled out of the figure).

Figure 14-5

Select the master and SSLDOG databases on the Select Databases screen.

17.       On the Update Data Optimization Information screen, select the Reorganize data and index pages check box. Select the Change free space per page percentage to: radio button and accept 10 in the numeric value field immediately to the right. Select the Remove unused space from database files check box and accept 50 MB for the When it grows beyond field:. Accept 10 % of data space in the Amount of free space to remain after shrink:. Accept the default schedule under Schedule: of Occurs every 1 week(s) on Sunday, at 1:00:00AM and click Next. Your screen should look similar to Fig­ure 14-6. The selections you have made relate to managing disk space

Chapter 14 Database Management With SQL Server 2000

from the SQL Server 2000 database perspective. And managing disk space on a computer is considered an important thing to do.

Figure 14-6

Your Update Data Optimization Information screen should look similar to this figure.

Notes:

 Visit http://www.microsoft.com/technet for the latest updates for any Microsoft product.

18.       On the Database Integrity Check screen, select the Check Data­base Integrity check box. Click the Include indexes radio button. Select the Attempt to repair any minor problems check box. Select the Perform these checks before doing backups check box. Your screen should look similar to Figure 14-7. Click Next. What you have just done is invoke the native SQL Server capability to maintain sound database integrity and to perform these actions before making an internal SQL Server backup.

Figure 14-7

The selections you make on the Database Integrity Check screen will allow you to keep your SSLDOG database healthy.

19.       On the Specify the Database Backup Plan screen, select the Back up the database as part of the maintenance plan check box. Select the Verify the integrity of the backup when complete check box. Click the Disk radio button. Click the Change button and select Daily in the Occurs column when the Edit Recurring Job Schedule dialog box appears and click OK. You are returned to the Specify the Database Backup Plan screen. Click Next.

 

 

20.       Accept the default select of Use the default backup directory on the Specify Backup Disk Directory screen. Click Next.

 

21.       Select Back up the transaction log as part of the maintenance plan check box on the Specify the Transaction Log Backup Plan screen. Accept the default settings of Verify the integrity of the backup when complete, Disk, and the Schedule: that backs up the transaction log every week on numerous evenings. Click Next.

 

22.       Accept the default selection of Use the default backup directory on the Specify Transaction Log Backup Disk Directory screen. Click Next.

 

23.       Select the Write Report to a text file in directory check box on the Reports to Generate screen. Accept the default report storage folder (c:\Program Files\Microsoft SQL Server\MSSQL\LOG\). Select Delete text report older than and accept the default time period of 4 Week(s). Your screen should look similar to Figure 14-8. Click Next.

 

Figure 14-8

You can configure the text-based reports you want to generate.

 Visit http://www.microsoft.com/technet for the latest updates for any Microsoft product.

 

 

24.       Accept the default settings of Write history to the msdb.dbo.sysdb­maintplan history table on this server and Limit rows in the table to: 1000 rows for this plan under Local Server on the Maintenance History screen and click Next.

 

25.       The Completing the Database Maintenance Wizard screen ap­pears. Accept the default plan name of DB Maintenance Plan1 under Plan name:. Note you could select all of the configuration information in the text box using your mouse (by dragging) to copy it to the clipboard with the CTRL-C command and then paste it into a text editor such as Notepad for inclusion in your SBS network notebook. Click Finish.

 

BEST PRACTICE: You might receive an error message via the SQL Server Enterprise Manager dialog box that informs you that the SQL Server Agent service is stopped and must be started to complete this processing. If you receive this message, simply click the Start button from your SBS server machine desktop (you might have to hold the CTRL-ESC keys to display the Start button). Click Start, All Programs, Microsoft SQL Server, Service Manager. When the SQL Server Service Manager appears, select SQL Server Agent in the Services: drop-down field. Click the green arrow button next to Start/Continue. Close the SQL Server Service Manager. Click OK on the SQL Server Enterprise Manager dialog box that appeared at the beginning of this note.

You have now successfully completed the creation of a database maintenance plan for SPRINGERS. You will receive no indication that this was successfully created (in SBS 4.5 with SQL Server 6.5, you received a notification dialog box that you had to click OK on). However, you can verify the database maintenance plan was successfully created by expanding the Management folder under the SPRINGERS1 (Windows NT) object in the SQL Server Enterprise Manager. Click the Jobs object in the left pane and the details for DB Maintenance Plan1 appear in the right pane.

 

cheers…harrybbbb

Harry Brelsford, CEO at smb nation www.smbnation.com

Microsoft Small Business Specialist SBSC, MBA, MCSE, MCT, MCP, CNE, CLSE, CNP

PS – did u know I host a technology conference in the New York City area each spring? Save the date for March 6-8, 2009 and watch “voice meet data” in the SMB space!

PPS – my SBS 2008 book will be out in mid-November 2008!

PPPS – my Microsoft Response Point Primer book is here NOW!

Advertisements

Leave a comment

Filed under Book

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