SQL 2008

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

SQL 2008 Report Builder 2.0 overview

SQL 2008 is a rich platform for building database solutions that is found in many businesses from the very small to the extremely large. SQL 2008 comes in a number of versions with differing levels of functionality and scalability depending on your needs. The premium editions of both SBS 2008 and EBS 2008 include a SQL 2008 Standard Edition license.

One of the more interesting features of SQL 2008 is Report Builder 2.0 an end-user report writer. Report Builder has an Office 2007 style interface providing a familiar environment for anyone with a basic understanding of databases to create reports.

Database Administrators can define data models and make them available to the report writers ensure users don’t have full access to browse data.

Report authors have access to a wide range of features for designing report layouts:

  • Rich formatting
  • Table designer
  • Charts
  • Multiple data sources
  • Export in Microsoft Office formats

Once created reports can be run locally or published via a Reporting Server.

Installation

The installation files are provided as part of the SQL 2008 Feature Pack
Minimum requirements are Windows XP SP3 or better and .NET Framework 3.5

Service Pack 1 for SQL 2008 includes improvements to Report Builder.

Essential Business Server 2008 overview

In November 2008 Microsoft released a new server software bundle aimed at businesses with up to 300 users. I recently deployed my first Essential Business Server 2008 (EBS 2008) and was instantly impressed. EBS 2008 mixes proven technologies like Windows 2008 Server and Exchange 2007 with new technologies Forefront and management tools like System Centre Essentials. The combination of products works well together and has the potential to save a lot of time both during the initial install and over the lifetime of the system.

What is EBS 2008?

EBS 2008 deploys onto 3 servers. It is supported on both Physical and Virtual environments. The Premium edition adds a forth Windows 2008 standard server (with 1 free Virtual License included) and SQL 2008 Standard Edition.

Following the installation you get environment with these roles / features:

  • 2 Domain Controllers (Management and Messaging servers)
  • System Centre Essentials 2007
  • Exchange 2007
  • Forefront for Exchange
  • Forefront TMG (next generation ISA)
  • Remote Web Workplace
  • Terminal Services Gateway
  • Windows 2008 standard edition (premium edition)
  • SQL 2008 (premium edition)
  • WSS 3.0 (free download)

The 3 standard servers require 64bit hardware. The premium server can be either 32bit or 64bit.

Installation

A preparation tool is provided to examine an existing environment or help you design a new one. Once this is complete it is simply a matter of putting the first DVD into your server (make sure the hardware meets the system requirements) and following the prompts until the 3 servers that make up the EBS 2008 environment are installed. The standardised installation removes many common configuration issues and helps build a core network that will perform well and work with very little tweaking.

Some of the more difficult parts of a typical network installation where positively simple with EBS 2008. Exchange 2007 installed perfectly with only a few simple questions, Forefront TMG (the replacement for ISA 2006) also installed perfectly and Remote Web Workplace’s TS Gateway options just worked.

Management

Once EBS 2008 is installed, System Centre Essentials agents can be deployed to other Windows based servers and PC’s in the domain giving enterprise style management of your network from a single point. Common tasks like installing Windows updates, ensuring antivirus software is install and up to date, deploying software and producing an inventory of hardware and software can be done with minimal effort.

The EBS Management Console supports third party plug-ins and provides a nice management dash board SysAdmins will love.

Managing licenses is simplified too. Microsoft sell two different EBS Client Access Licenses (CAL). The standard CAL includes Windows 2008 CAL and Exchange 2007 CAL. The premium CAL adds a SQL CAL. It is simple to assign either standard or premium licenses to specific users and report on usage. The CAL pricing also provides a good saving over purchasing individual user CAL’s.

Gripes

I don’t have many gripes about EBS 2008 but it does have some room for improvement. Forefront for Exchange seems a little bit ‘clunky’. Additional Forefront client licenses are required and while pricing is was difficult to get information from Microsoft about this (in New Zealand at least).  Microsoft don’t include a backup solution other than Windows Backup which doesn’t support Exchange or SQL.

I also found that many vendors either don’t know what EBS 2008 is or don’t have upgrade options for software from Small Business Server.

Conclusion

EBS 2008 is a excellent solution for those who have either out grown Small Business Server or are moving from Windows 2000 or 2003 server and have less than 300 users. The time savings for management alone make this bundle well worth considering.