General SQL Server (RSS)

Favorite BI Features in SQL Server 2008

If you haven’t heard yet, CTP 3, which is a public beta of SQL Server 2008 has now been released and can be downloaded at the Connect website. After a bit of unscientific research, here’s my top 5 BI features in SQL Server 2008. It’s ordered in my priority order. It’s important to note that some of these features have not been introduced in CTP 3 yet but will be coming soon.

  1. No more IIS requirement for SSRS! – Finally, there’s not the overhead of having to install IIS as part of the popular reporting tool.
  2. CDC and Merge – While this is a database engine improvement, it will tremendously help your BI effort in SSIS. The ability to do Change Data Capture on a SQL Server table will help warehousing scenarios and reduce your load time by a factor of 9x.
  3. SSRS scalability – The reporting engine has been significantly improved to scale to million row reports. Also introduced is a best of both worlds table and matrix style report called tablix.
  4. SSIS thread scheduler improvements – In some early testing, the thread improvements have netted a 20-70% improvement in data flow performance. The main improvement is going to be with a long list of synchronous transforms.
  5. Authoring Reports in Excel and Word – Thanks to some smart licensing of Soft Artisians’ product suites, you’ll now be able to author a report in Word or Excel. I would expect this to be a pretty big blow to competing reporting platforms since users will be able to write reports in where they spend most their time in.

-- Brian Knight

Free Hotel or Disney Tickets For the June SSIS and Perf Tuning Class

We're coming up on one year in business and that's cause for a minor celebration! Most of you have probably heard that a high percentage of startups fail within their first year - we're pleased to have made it over that hurdle. We've learned a lot of lessons and probably still have bunch more to go, but it's been fun, challenging, and occasionally intimidating. If you ever get the chance to go solo, try it! You'll be out of your comfort zone and it may not succeed, but it's still a great growth opportunity.

Free Hotel Stay or Disney in June

But back to the celebration. Sign up for the June 11-14 Performance Tuning or June 18-21 Integration Services class between May 21 and June 18, 2007, and you can have a choice of a $250 Disney Gift card (bring the family!) or four free nights in a hotel. Just enter GIFTCARD or HOTEL in the customer comments field during check out. This gives you the chance to visit our Orlando training center for about the same cost as attending a local class since we pay for the hotel for up to four nights! Alumni, don't forget you can receive a 10% discount on any of our classes - just ask for it! June is a great time to visit Florida and the evenings are the best time to enjoy the local attractions. Contact us at with questions about the offers, limitations, restrictions, etc.

Upcoming Classes

In our last update we announced two new offerings; End to End Business Intelligence and One on One Mentoring. We've had a lot of interest in both and a lot of requests for variations of the One on One Mentoring. We're also working on a free SQL event in Orlando similar to a Code Camp that will be targeted at SQL professionals. If it works out, we may try it in other cities. Below is a quick synopsis of our schedule, you can view the full schedule here.

Integration Services Jun 18, Aug 27, Dec 17

Performance Tuning May 21, Jun 11, Jul 16, Aug 20, Sep 10, Oct 15

End to End Business Intelligence Jul 30

One on One Mentoring Jun 4, June 25, Jul 9

Analysis Services Aug 13

See the full schedule and registration on

-- Brian

Professional SQL Server 2005 Administration on the Shelves at Last

After a long period of writing and banging my head into the keyboard, my newest book is on Amazon. Professional SQL Serever 2005 Administration was written by some a fantastic team from Microsoft and the community. It teaches you how to cluster, upgrade, administrate SSIS, mirror and much more. You can get it on Amazon now for the geek Christmas present to yourself :)

Amazon Purchase

Jumpstart TV No Long Requires a Login!

You no longer need to be logged into to watch videos. If you haven't visited the site yet, take a look at it. It has 55 SQL Server how-to videos and growing at 3 a week. The Home Improvement channel will launch in the next few weeks and others to follow after that. The site is looking for speakers so if you'd like to share info, email them at

SQL Server 2005 Service Pack 2 CTP Released

Service pack 2 released today to the public in CTP format and can be downloaded here: Along with bug fixes, there are tons of new features. What impressed me most was I was able to install the SP without a reboot! Vista/Office Fixes:
  • Support for the upcoming Windows Vista.
  • Data Mining Add-Ins for Office 2007, which enables data mining functionality from SSAS to be used directly within Excel 2007 and Visio 2007.
  • SSRS integration with MOSS 2007, which allows integration with the Report Center in SharePoint providing seamless consumption and management of SSRS reports within SharePoint.
  • SSAS improvements for Excel 2007 and Excel Services relating to performance and functionality.
Heterogeneous Environments (Interoperability):
  • Oracle Support in Report Builder. Users will now be able to use Report Builder on top of Oracle data sources.
  • Hyperion support with SSRS. Organizations will now be able to use SSRS to build reports on top of Hyperion Essbase cubes.
  • Data compression (varDecimal), which is important for data warehouse scenarios and is specifically important for SAP BW scenarios. This requires less disk storage of decimal data which increases overall performance.
  • Manageability enhancements. Based on customer feedback, SQL Server provides enhanced management capabilities for DBAs such as improvements in database maintenance plans, enhanced management reports and a new copy database wizard.

Thinking of Going to Vista, Be Careful!

If you currently run SQL Server of MSDE on your desktop, you need to really be careful before migrating to Windows Vista. It has been announced that Windows Vista will only support SQL Server 2005 SP2 (yet to be released) and above. SQL Server 2000 and 7.0 will no longer be support. Is this going to slow support of Windows Vista. I can't help but to think it might for many enterprises that run MSDE on their desktop.

-- Brian

Automating SQL Express Backups

SQL Express allows you to distribute a free edition of SQL Server with your applications. There are an enormous amount of 3rd party applications that use it and MSDE (it's SQL Server 2000 equivalent) for their data repository such as Sharepoint (by default). Generally speaking, the user may not even know they have the software on their workstation since it doesn't install anything on their Programs menu. The main weakness in SQL Express is that it doesn't have a scheduling tool. In this set of scripts, you'll receive a canned solution for automating backups of SQL Express. The same logic can be applied to MSDE by changing SQLCMD to OSQL in the batch file. No other syntax used is SQL Server 2005 specific.

The script contains three files:

  • BackupExpress.sql - Does the bulk of the work, backing up every database on the instance other than tempdb.
  • BackupExpress.cmd - The batch file that executes the script. Must pass in the instance name.
  • ScheduleBackups.cmd - Schedules the job by using AT. Must pass in the instance name to schedule like ScheduleBackups.cmd .\SQLExpress.

To do this, make sure you have the Task Scheduler service started. Uncompress all three of the files into the root of your C drive and run the schedule file from the command prompt.

I tried to keep the solution simple since most people that have SQL Express on their workstation may not be technical. So with that said, there is very little configuration or options. The backup solution keeps 7 days of history and constantly overwrites the previous week's backup. So, there's lots of room for improvement if you're feeling adventurous. Please send me any updates and I'll post them for all, giving you credit. The main thing missing from this solution is the index rebuilds, which I'll probably add in the next release.

Download the package here: Enjoy!

Watch the deployment video on JumpstartTV here:

-- Brian Knight

New SQL Server Books Online

The July update for Books Online is now online and downloadable on here:

Starting in this release, there is no longer a single SQL Express BOL. It is now wrapped into th main BOL and you can use the filter to remove unneeded topics.

-- Brian Knight

Announcing Visual Studio Team Edition for Database Professionals

Really cool tool that I'll have to start blogging about! Here's the announcement I just received from Microsoft. I added some screen shots below.

Visual Studio Team Edition for Database Professionals delivers a market-shifting database development product designed to manage database change, improve software quality through database testing and bring the benefits of Visual Studio Team System and life cycle development to the database professional. It delivers on Microsoft’s commitment to provide tools that reduce communication barriers and complexity across software development teams and fulfils increasing demand in the market for more advanced database change management tools. Database professionals such as database architects, database developers and database administrators, can now employ integrated change management functionality to streamline changes to their databases, ensure quality, and speed deployment.

Visual Studio Team Edition for Database Professionals includes a number of great new features:

·         A new Visual Studio Database Project allows you to import your database schema and place it under source control. When the time comes to deploy schema changes the new project system allows you to quickly build update scripts or packages and then provides a mechanism to deploy them to the database of our choice.

·         Rename Refactoring allows you to easily rename any object in your database and be assured that all references to that object will be renamed to correspond to the change

·         A New T-SQL Editor allows you to be more productive when writing T-SQL code from within Visual Studio including support for parallel execution of queries and viewing of execution plans.

·         SchemaCompare allows you to quickly compare the schema of two databases (or your source controlled project and a database) and script updates to bring the database schemas into sync

·         DataCompare allows you to quickly compare two databases and script updates to bring the data in these databases into sync

·         The Database Unit Testing infrastructure allows you to create database unit tests using T-SQL or managed code.

·         DataGenerator lets you create data generation plans that produce repeatable sets of meaningful data based upon your existing production databases that can be deployed to a database prior to running unit tests thus ensuring consistent test results

You can find out more about this great new release, see screenshots and find out how to get the early community technology preview which will be available on June 11th at

We are making Visual Studio Team Edition for Database Professionals part of the Visual Studio Team Suite, so you’ll get this product for free when we RTM this edition if you are a Visual Studio Team Suite subscriber through MSDN. You can learn more about how to upgrade to Visual Studio Team Suite at

Visual Studio Team Edition for Database Professionals will also be available as a stand alone Edition in the Visual Studio Team System family. You can learn more about how to buy Visual Studio Team System at

The team has already started blogging. You can find out more information directly from the product team by visiting the following blogs:


Take Control of Database Change


Automated Database Testing


Improve Collaboration and Communication

Using the Dedicated Administrator Conection

You should only use a dedicated administrator connection (DAC) in special occasions. For example, in some cases, you may find that a user is running a query that has utilized all of your systems resources. The user may be using so much of the system’s resources that no other users, including yourself can login to the system to kill the users connection.

This would be a great use for the DAC. Under this situation before, you would have to stop and start the SQL Server if some time had passed. With the DAC enabled, you as a DBA can login if you had sysadmin rights by qualifying the server with ADMIN:. For example, if you wanted to login into the localhost machine you would use ADMIN:localhost. You will receive an error if the feature is disabled. To enable the feature, go to the SQL Server Surface Area Configuration. After it is enabed, you must stop and start the SQL Server instance.

For more information about Surface Area Configuration, go to

-- Brian Knight

New SSIS, SSAS, and SSRS Classes Available

Wow, I've broken the number one blog rule. I've been busily writing these three BI classes, that I haven't had time to eat, much less blog. My apologies! Well back from the long lull, the classes are now complete and will be given in Jacksonville (Jax) and other cities around the country soon. Here's the upcoming schedule. Each topic is two days in total and the combo makes 4 days. After giving the SSIS class a number of times, we're expanding it to give you more hands-on topics so when you leave the class, you will truely know how to implement solutions.

Designing and Implementing SSIS Solutions  - July 18th - 21st in Jacksonville
Designing and Implementing SSIS Solutions - July 24th-27th in Orlando
Designing and Implementing SSIS Solutions - Oct 2-5, 2006 in Orlando

--- End Series 1 ----

SSIS / SSAS August 22nd - 25th in Jax
SSIS / SSRS Sept 19th - 22nd in Jax
SSAS / SSRS Oct 24 - 27th in Jax

Email for any of these three above classes.
--- End Series 2 ----


Price: The classes run  $2,200 for 4 days.

-- Brian Knight


Service Pack 1 Released!

Service Pack 1 is here and it fixes loads of items. Some of which were quite embarassing when training or developing a solution with a client standing behind you. The largest enhancement is the addition of database mirroring, which allows you add high availability to your database by sending transactions to another server in real-time. There are loads of SSIS fixes and enhancements. Add a quick rolled up level, here are the SSIS fixes:

Usability of the Import/Export Wizard has been improved in multiple-table scenarios.
The IDtsPipelineEnvironmentService service lets custom data flow components have programmatic access to the parent Data Flow task.
Interoperability with Analysis Services has been improved. The DataReader source in SSIS supports the System.Object data type by converting columns that have this data type to the DT_NTEXT ssISnoversion data type. To change the data type to a type that is more appropriate for your data, you can add a Data Conversion transformation.
Performance has been improved for many transformations, such as the Sort transformation.
Designer usability has been improved. For example, you can now right-click Dataflow and then click Execute Task to execute only the Dataflow task. You do not have to switch to the control flow to execute only the Dataflow task.
The Expression Builder dialog box now has a public and documented API. This increased access to expression-related objects will benefit task developers because expressions are important to the way that tasks are interrelated.
We have added a registry-based policy for handling SSIS package signatures as a package is executed. For example, administrators can disable loading of unsigned packages and untrusted packages. Because these settings are in the registry, administrators can propagate these settings in the domain by using Microsoft Windows Group Policy policy settings.
In the Advanced Editor dialog box, the Flat File source has the new property UseBinaryFormat. This property supports loading packed decimal data into the pipeline for processing by a script or by a custom transformation.
In the Advanced Editor dialog box, the DataReader source has the new property CommandTimeout. You can use this property to modify the time-out period to allow for long-running operations.
To create or to modify the property expressions of variables, you can now open the Expression Builder dialog box from the Properties window.
You can now add annotations to precedence constraints.

It's certainly a must download for anyone using 2005.

-- Brian Knight

New Community Website

I'm proud to be launching with some partners of mine JumpStart TV. The community will have loads of how-to videos on a variety of topics. We'll be placing two 10 minute videos a week on the site that will teach you visually how to perform a function in a Microsoft technology like SQL Server or Team Foundation Server. Later next month, watch for other types of channels on the same site. The site is free but does require a registration. Here are two videos I did to kick off the site. Please keep in mind that the site is in its beta stages and may have bugs.

Date Published: 1/29/2006 4:10:21 PM
Author: Knight, Brian
Description: In this video, Brian shows how to use SSIS to make a simple transformation occur of your data. He aggregates and sorts the data before writing the data to a flat file. After you watch this video, you will know how to use the Data Flow task inside of SQL Server 2005 SSIS.
Rating: 4.25
Date Published: 1/29/2006 4:08:31 PM
Author: Knight, Brian
Description: So, you’ve just installed SQL Server 2005 but don’t know how to configure it. This video will instruct you how to use the SQL Server 2005 Surface Area Configuration tool to turn on SQL Server features like CLR integration and how to enable ports.
Rating: Unrated
-- Brian Knight

Pubs and Northwind databases not installed in SQL Server 2005

I love the new Microsoft SQL Server 2005 sample databases (AdventureWorks and AdventureWorksDW). Compared with the Northwind and even worse the Pubs databases, they're amazingly more real-world. They show best practices and demostrate a good portion of the 2005 features. The problem lies in a lot of the SQL Server 2000 examples on the Internet still work in SQL Server 2005 and content that's specific to 2005 is still being produced. So, what to do while you wait for the examples to be updated? Why not live like it's 2002 and install the SQL Server 2000 sample databases? That way the sample RDL files, T-SQL scripts and most other examples on the net will work.

To do this, you'll need to download them at:

Run the SQL2000SampleDb.msi file to extract the files to the strangely placed directory C:\SQL Server 2000 Sample Databases (no you can't change this I'm afraid). Then you can run the scripts in the directory to install the databases or attach the data files. Instpubs.sql creates the pubs sample database and Instnwnd.sql creates the Northwind sample database. If you don't have the SQL tools installed, you can use SQLCMD from a DOS prompt to install them as shown below:

c:\sqlcmd -S .\InstanceName -i instpubs.sql
c:\sqlcmd -S .\InstanceName -i instnwnd.sql

If you do have the tools, just run the scripts in the Query Window of SQL Server Management Studio. The MDF and LDF files are there for attachment. Another method is to right-click on the database tree and click Attach, pointing to the MDF and LDF files. Before doing this though copy the files to the instance's Data directory. After the attachment or script is run, you can remove the directory and files through Add\Remove Programs.

PS: Don't get in the habit of creating new scripts against these databases, as they no longer represent the current technology strategies you would employ.

-- Brian Knight