SQL

SQL Server 2008 Certified

SQL Server is something that most people in IT roles will touch one way or another in the course of their working lives. It is the engine behind many line of business systems, SharePoint, ERP and CRM systems.

Having the basic skills to correctly install, configure, backup, recover and troubleshoot SQL Server issues is important and something that is often just assumed. Over the past 16 or so years I have seen many examples where this assumption is wrong. To make matters worse, troubleshooting is often focused in the wrong areas leading to time consuming and expense solutions to often simple problems.

A few things everyone should know about SQL Server:

  • How disks should be configured when performance is required
  • Databases and Log files should be on dedicated disks
  • How to setup a maintenance plan and why you should
  • SQL Agent service defaults to manual startup
  • How to backup
  • How to recover to a point in time
  • The difference between Full and Simple Recovery models
  • How to use SQL Profiler to identify poor SQL code
  • Basic SQL queries
  • Licensing options and SQL Server Editions

This is by no means a complete list, but is a good step in the right direction for anyone who manages SQL Server environments.

I first touched SQL Server back in the early 1990’s with version 4.21 and since then I have use ever version right up to the current 2008R2 edition. Recently I decided to formalise this experience and become a Microsoft Certified Technology Specialist: SQL Server 2008 Implementation and Maintenance. This was a rewarding experience and I learnt many new and useful things.

If are you are going to certify, I recommend reading “The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit” and a bit of hands on experience to pass this exam.

The 70-432 exam is oriented towards IT Pro’s, but I think it would look great on the CV of a developer, SharePoint consultant or specialist for any SQL Server based application.
70-432 Certification Home Page

Moving SQL Express or MSDE Databases

Many applications use Microsoft MSDE or SQL Express instances to store application specific data. The default file location is usually somewhere on the C: drive, which may seem harmless until they start consuming valuable disk space. It is possible to move the databases without reinstalling the applications using this method:

  • Install SQL Management Studio Express (or use SQL Management Studio if you have it)
  • Locate the physical files associated with the database by Right Clicking the database in SQL Management Studio and choosing properties. Note the database and log paths may be different.
  • Stop any services and websites associated with the database
  • Right Click the database and choose “Detach”
  • Move the database and associated log file to a new location (.MDF and .LDF)
  • In SQL Management Studio, right click Databases and choose “Attach” and choose the database file (.MDF)
  • Restart the services and websites stopped above

Because the application itself communicates with the database engine, moving the location of the data files doesn’t require any reconfiguration of the application itself. I have used this method to successfully move WSUS, SCE, BackupExec and WSS databases. This method works for all versions of SQL not just MSDE or SQL Express.

Note that it is a good idea to keep Databases and Logs on independent sets of disks for performance and recoverability. It is also a good idea to ensure busy databases are not stored on the system disk.

Download SQL Management Studio Express

EBS 2008 Premium Server Overview

The premium edition of Essential Business Server 2008 (EBS) includes an additional Windows 2008 Standard Server licence and SQL 2008 standard license. While the three servers that make up the core of EBS are 64bit, the forth server can be either 32bit or 64bit. Here are a few ways this server can be used to enhance the network environment.

Virtualisation

With the included Windows 2008 Standard server license you get rights to run a single Virtual Instance of Windows 2008 Standard. The physical server can be installed as a Hyper-V host for a virtual server running a second instance of Windows. Additional Windows licenses can be purchased to run additional virtual machines.

SQL

SQL 2008 Standard Edition is a powerful database management system that can be used to host a wide range of SQL applications e.g. Microsoft Dynamics, Sharepoint content databases, SAP etc. It also features reporting, data analysis and replication technology and is a great platform for managing data company wide and making that data accessible.

SQL 2008 Home Page

Compatibility

In some environments it might be desirable to have a 32bit Windows server e.g. to support legacy 32bit only applications or devices such as printers that don’t have a suitable 64bit driver. Either a 32bit physical operating system or a 64bit instance running Hyper-V with a 32bit virtual machine can be useful options to get around these issues.

Client Access Licenses

When adding new users to EBS 2008 via the EBS Management Console you can choose between standard and premium client access licenses. If you have users who don’t require access to the forth server you can assign a standard CAL to those users.

EBS 2008 Edition Comparision

DNS Aliasing

How many times have you had to upgrade a server and run around making changes to login scripts and group policies. How about migrating users to a new terminal server or migrating applications to a new SQL server. DNS aliases can be used to simplify these tasks and with a little bit of thought prevent the need to ever make those changes again.

File Server example

In this example we have a existing file server  FS-1 and a new file server FS-2. We will create an alias called FILESERVER

Disable Strict Name checking on both file servers (needed to allow connection to SMB shares):

  • Edit HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters
  • Add a REG_DWORD DisableStrictNameChecking = 1
  • Restart the server so the setting takes affect

In DNS create a CNAME record called FILESERVER and point the CNAME at the DNS A record for FS-1.

You can now change login scripts and GPO’s to connect to FS-1 in two ways. Note this works for file shares and shared printers too:

  • \\FS-1\share
  • \\FILESERVER\share

Now lets assume you want to replace FS-1 with a new server FS-2. Simply move the data and create shares on FS-2 and when your’re ready to swap servers change the FILESERVER CNAME to point to FS-2. No changes to login scripts or GPO’s.

Tips:

Before cutting over to FS-2 you can use a hosts file on a PC to connect to shares and test your shares.

The same concept can be used in many places including aliases for SQL databases, Sharepoint, mail servers, web servers and many applications that use TCP/IP to communicate.

This is a good way to present “friendly” server names to users while maintaining names that are meaningful to the IT team.

You can have multiple CNAME records pointing to the same server. Useful for application specific DNS aliases e.g. a SQL server running several databases could have CNAME for each databases so that if one is moved to another server in the future the CNAME can follow.