On Friday, Sept. 12, you can hear about all the new features of SQL Server 2008 in this full day of free training brought to you by JSSUG, Idea Integration and Pragmatic Works. We've already had about 100 register for the event and we only have room for about 200 more so grab your seat fast. Each session will dive deeply into new features from a BI, DBA and developer perspective around SQL Server 2008. To claim your free seat (including lunch), go to http://www.sqlsaturday.com and select the SQL Launch (the event is on Friday, Sept 12). Then, click Register.

The schedule is still being perfected but you can see a tentative schedule on the site or below. We also have room for two more sessions so if you'd like to speak, please click Call For Speakers.

Hope to see you there!

In this series, I plan on sharing a lot of my recent experience with the SQL Server 2005 to SQL Server 2008 upgrade. Part of our ongoing project was to also use some of the new fantastic full text features in SQL Server 2008, which are now fully integrated. We had some difficulties in turning this on after the upgrade.

Our upgrade strategy was to backup a database on SQL Server 2005 and restore it on SQL Server 2008. The most important to note about that strategy is you will need to set the databases to 10 compatibility mode after you do the restore and you'll need to rebuild your statistics.

After that though, we went to create our first full text and we noticed that our Define Full Text button and all other full text options were greyed out. After some time of troubleshooting, we looked at the sys.databases databases table and noticed that the is_fulltext_enabled was turned off for the database, which was supposedly deprecated (http://technet.microsoft.com/en-us/library/ms403375.aspx). In reading, the Technet articles stated that the enabling full text is no longer supported as well, since it's always on.

To get this working, we had to use an older stored procedure which gladly wasn't deprecated yet:

sp_fulltext_database 'enable'

We ran that on each of the databases, and then we were off and running to the races.

I hope this series saves you some time and aggravation. My next one will be on some issues I ran into with the SSIS data providers during the upgrade.

- Brian Knight

I was trying to deploy a custom assembly today using Windows 2008 and SQL Server 2008 and found a bit of differences in Windows 2008. The GACUtil.exe utility has now moved first and foremost and is not installed by default until you install the .NET and Windows 2008 SDK. After that, you'll be able to find the GACUtil.exe utility in the SDK directory and can call it as shown below:

C:\Program Files\Microsoft SDKs\Windows\v6.1\bin\gacutil.exe /i "dllfilename.dll"

After then, you'll need to copy your DLL file into the C:\windows\Microsoft.NET\Framework\v2.0.50727 (or whatever .NET version you wish to be on) directory and you're golden to use a custom assembly in the Script Component or Task in SQL Server 2008/Windows 2008.

Brian

A icon of the business intelligence industry for Microsoft has now left the company to take a CTO role at Visible Technology. Bill has been with the SQL Server team or with in a BI role since 1996. I will truly miss Bill in the community, as he was a BI pillar and his departure to work for his new great opportunity will leave a large gap inside of Microsoft. To show the gap that he will leave, when he left the Microsoft SQL Server team after SQL Server 2005 to go to the office group, a large segment of the SQL Server team left to join him and it caused a domino effect.

We'll miss you Bill!

We’d like to thank all of you for attending our first of many free SQL Server Business Intelligence training sessions. To view the webinar recordings and register for our next free session please click below:

It's official, SQL Server 2008 has now launched and can be downloaded on MSDN, Technet. For more information, you can see the press release here: http://www.microsoft.com/presspass/press/2008/aug08/08-06SQLServer2008PR.mspx.

In Microsoft tradition with each new release, they have added a new edition: Web Edition. Web edition offers a lower price-point edition of SQL Server for web hosting providers or companies that just want a small SQL Server footprint with moderate scaling.

So why upgrade? Well, it depends on your situation. Here are some really compelling cases that are on the top of my list:

• Are you synchronizing systems two SQL Servers?
• The Change Data Capture feature will allow you to read the source table and only determine the rows that have changed. (Enterprise Edition)
• Do you have a data warehouse or a database that's a few hundred gigs in size?
• The compression feature will compress with about a 3:1 ratio, speeding up read IO (Enterprise Edition)
• The new star join improvements will speed up query time
• Do you have more than 10 instances?
• Your environment may becoming too uncontrollable and the Policy Framework will allow you to determine which servers are not complying with your policies
• Monitor server usage and across your enterprise to determine capacity
• Do you reporting against your SQL Server?
• You can stop a runaway query by using the Resource Governor to put caps on the query. (Enterprise Edition)
• In Reporting Services, IIS is now gone!
• A much, much better report builder can be downloaded and used in 2008

For a full feature list by edition, go to: http://msdn.microsoft.com/en-us/library/cc645993(SQL.100).aspx.

Yesterday I finished up my Professional SQL Server 2008 Administration book at long last, which makes my 9th book. I've never tried to write two books at the same time but I can tell you I'll never do it again! Writing is always like a bad hangover where you wake up the next day saying, "I will never, ever do that again" and then two weeks passes and you're back with a beer in front of you.

Regardless of the pain, I learned some interesting new things in this book. My most challenging adventure was to simulate a cluster on my laptop using Virtual PC. Previously I had always done Virtual Server but because of me being a stubborn guy, I really wanted to make Virtual PC work. To do this, I first had to find an ISCSI Target. The target's job is to host a shared drive that can be seen on multiple nodes and drive the failover. The software I found to be the easiest was Rocket Division's StarWind target software.

Once the ISCSI software was configured, Windows 2008 or Vista has a ISCSI initiator built into it. If you're running Windows 2003, you can download an ISCSI initiator for free. ISCSI initiators connect to the target and write data to the remote drive.

I will be creating a series of videos on the full clustering process on JumpstartTV.com later next week but let me tease this by saying that Windows 2008 clustering really is amazing. The process asked me two questions during the entire process and all nodes are done in one click without having to go to each node.

Watch this space for videos on the entire process.

Thanks to the nearly 15,000 people who attended a webinar with me last week! All the webinars were recorded and we'll be posting them within a few weeks. We're just awaiting them for them to be edited since we have full motion videos in them. We were excited to bring this service to the community in partnership with SQLServerCentral.com. Thanks again for coming and we hope to launch a Reporting Services series in the next few months!

Thanks to all who came to today's session on Introduction to SSIS. Tomorrow's session is on SSIS for the DBA. If you have not registered, I'm afraid we've maxed out the conference system at this point with 3200 people spread across Wednesday's sessions and we're expecting the typical webinar drop off, which should still be a large number. Hopefully on Wednesday we don't "stress test" the Gotowebinar system like today, bringing it to its knees :).

The Wednesday session has a number of examples for the DBA and how you can use SSIS. For example, one example is a package (see below download) that will loop through a list of database instances and capture the worst performing queries. I've also including some quite ugly but functional reports to show you the worse performing queries. To get this working in your environment, simply run the CreateScript.sql file in a database you want to log all this information into. Then populate the MonitoredServers table with a list of instances that you have access to that you wish to monitor.

This sample is provided "as is" with no warranty. Please modify it however you wish and run at your own risk. One thing to keep in mind that it does flush the procedure cache after it runs for statistical purposes so don't run this that often. I'm more showing this to you as an learning example.

Download the example:

SSISfortheDBA.zip

The setup for this will all be covered in Wednesday's session (http://www.dtsxchange.com/freebisessions.asp) and we will be recording the sessions for those who can't make it. Expect the recordings by next week on this site.

Oops! In the zip file, I left out the ChangeTracking table, which can be found here in this script:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChangeLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ChangeLog](
    [ChangeID] [int] IDENTITY(1,1) NOT NULL,
    [ChangeTitle] [varchar](150) NULL,
    [ServerName] [varchar](25) NULL,
    [SchemaName] [varchar](25) NULL,
    [ChangeDescription] [varchar](2000) NULL,
    [ChangeDate] [datetime] NULL,
    [ChangeUser] [varchar](50) NULL,
CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED
(
    [ChangeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF

Thanks!

Brian Knight

Thanks to the 1500 + people who attended today's webinars. The recording will be posted on PragmaticWorks.com or SQLServerCentral.com next week. In case you're following along with the example I had on the webinar, you can download the code below.

Download : IntroSSISWebinarSample.zip

I was recently working with FoxPro for Unix as a data source using ODBC in SSIS (story for another day) and found some interesting scenarios inside of it. We wanted to pull the table name dynamically, thus changing the SELECT statement inside the SSIS Data Reader Source. Whilst this is quite easy in OLE DB Sources, there's no obvious way to do this in the Data Reader Source for ODBC or legacy connections as you can see in the below screenshot.

There's still a workaround, which feels like a hack but it works great. Before you start, change the name of the Data Reader Source to something that you're fine leaving it as because you're about to create a dependency on this name. To make the query dynamic from an SSIS Data Reader Source or ADO.net Source, select the Data Flow Task and in the Properties window, you'll see the name of the Data Reader Source with your query inside of it as shown in the below screenshot. This shows you which property you want to make dynamic, which in my case is the [DataReader Source].[SQLCommand] property. You then want to set the expression on that property by clicking the ellipsis button next to the Expression property. Lastly, create an expression like this, which makes the query dynamic using a variable:

"SELECT * FROM "  @[User::TableName]

You may find yourself needing to turn off the ValidateExternalMetadata property if don't want your query to parse until execution instead of during the validation stage. You can set many properties this way in the data flow to be dynamic but it does require the names of the components you're setting to be static.

Brian Knight

If you're interested in becoming certified in SQL Server and are really, really driven, there's a free test for BI and SQL Server for this month only. You have to register soon to get one of the available open beta slots though. If you do pass, you'll be credit with the test once it's out of beta. If you fail, you're not out any money at all if you choose the promo code. I'm taking the BI one Thursday the 3rd and will post how it goes. The admin one I'm taking the following week.

See more information about how you can claim one of the slots here: http://blogs.msdn.com/gerryo/archive/2008/07/01/sql-server-beta-exams-extended-open-invitation.aspx

For the test use the promotion code of: 943F6 and there are lots of restrictions.

Due to popular demand, many of the webinars have reached the capacity of more than 1500 registered guests. To make sure everyone could participate, we have added an additional hour each day for those who missed the first session. You must register in order to attend and the second session is expected to reach capacity as well!

More information can be seen here: http://www.dtsxchange.com/freebisessions.asp 

I've never done a webinar before with anywhere near this number so I'm curious how the technology scales :). It should be a great case study to put 1000 geeks on a concall and screen capturing system at once. If you can make it, we've added the extra slot and session for you but please register soon to get in.

Monday, July 14 @ 11:30 AM EDT 1hour
Upgrading DTS Packages to SSIS
The session will include an actual DTS package conversion using DTS xChange and other strategies.  DTS xChange offers the latest automated DTS to SSIS migration technology used to complete SQL Server 2005 upgrades.
Sorry, session is full. Please attend afternoon session

Monday, July 14 @ 1:30 PM EDT 1hour
Upgrading DTS Packages to SSIS
The session will include an actual DTS package conversion using DTS xChange and other strategies.  DTS xChange offers the latest automated DTS to SSIS migration technology used to complete SQL Server 2005 upgrades.
https://www2.gotomeeting.com/register/976091722

Tuesday, July 15 @ 11:30 AM EDT 1hour
Introduction to SQL Server Integration Services (SSIS)
This session will get you past the learning curve of SSIS. Brian covers creating an end to end package that covers many of the concepts in SSIS. He covers, looping over a set of files, processing each file and then archiving. You won't want to miss this if you're new to SSIS.
Sorry, session is full. Please attend afternoon session

Tuesday, July 15 @ 1:30 PM EDT 1hour
Introduction to SQL Server Integration Services (SSIS)
This session will get you past the learning curve of SSIS. Brian covers creating an end to end package that covers many of the concepts in SSIS. He covers, looping over a set of files, processing each file and then archiving. You won't want to miss this if you're new to SSIS.
https://www2.gotomeeting.com/register/471645031

Wednesday, July 16 @11:30 AM EDT 1hour
SQL Server Integration Services (SSIS) for the DBA
Brian will start by showing you the basics of SSIS then quickly jump into common DBA tasks like trying to integrate SSIS with dynamic management views (DMVs) to determine the worst performing queries across multiple databases and servers. If you're a DBA and have SSIS in your environment, you won't want to miss this presentation.
Sorry, session is full. Please attend afternoon session

Wednesday, July 16 @ 1:30 PM EDT 1hour
SQL Server Integration Services (SSIS) for the DBA
Brian will start by showing you the basics of SSIS then quickly jump into common DBA tasks like trying to integrate SSIS with dynamic management views (DMVs) to determine the worst performing queries across multiple databases and servers. If you're a DBA and have SSIS in your environment, you won't want to miss this presentation.
https://www2.gotomeeting.com/register/301757498

Thursday, July 17 @ 11:30 AM EDT 1hour
Performance Tuning SQL Server Integration Services (SSIS)
In this demo-rich presentation, Brian shows you some of the common and not so common ways to tune SQL Server Integration Services (SSIS). Learn how to tune the data flow using some of the advanced SSIS options and how to avoid common SSIS mistakes. See how to measure performance and how to keep SSIS from monopolizing your server's resourcing. Lastly, discover SQL Server 2008 features that will make SSIS more efficient.
Sorry, session is full. Please attend afternoon session

Thursday, July 17 @ 1:30 PM EDT 1hour
Performance Tuning SQL Server Integration Services (SSIS)
In this demo-rich presentation, Brian shows you some of the common and not so common ways to tune SQL Server Integration Services (SSIS). Learn how to tune the data flow using some of the advanced SSIS options and how to avoid common SSIS mistakes. See how to measure performance and how to keep SSIS from monopolizing your server's resourcing. Lastly, discover SQL Server 2008 features that will make SSIS more efficient.
https://www2.gotomeeting.com/register/220829813

Friday, July 17 @ 11:30 AM EDT 1hour
Administrating SQL Server Integration Services (SSIS)
In this session for DBAs, Brian Knight shows you how to configure your SSIS packages for a zero-touch deployment. Then Brian covers how to deploy, schedule and administer SSIS in production.
Sorry, session is full. Please attend afternoon session

Thursday, July 17 @ 4:00 PM EDT 1hour
Administrating SQL Server Integration Services (SSIS)
In this session for DBAs, Brian Knight shows you how to configure your SSIS packages for a zero-touch deployment. Then Brian covers how to deploy, schedule and administer SSIS in production.
https://www2.gotomeeting.com/register/296733568

Friday, July 18 @ 3:00 PM EDT 1hour
What's New In SQL Server 2008 Business Intelligence
This session will get you past the learning curve of SQL Server 2008. Brian will cover each of the BI platforms' best new features: SSIS, SSRS, and SSAS. Additionally, he'll cover many of the T-SQL new features that will effect business intelligence.
https://www2.gotomeeting.com/register/742337468

I just signed up for a service called Twitter, where you can follow your co-workers through mini-micro posts throughout the day. If you interested in following the truly boring life of Brian, you can go to http://www.twitter.com/brianknight.

In this session by SQL Server MVP, Brian Knight, you’ll learn how to make a package that needs zero configuration as you migrate from development to production. He will then show you how to deploy your packages and administer the SSIS service. You’ll also learn how to:

-Create configuration entries for dynamic configuration of packages
-Deploying SSIS packages to various environments
-Administering the SSIS service
-Scheduling packages

Register for this free session at: https://www2.gotomeeting.com/register/430701585