July 2008 - Posts

• Learning How to Cluster with Virtual PC and ISCSI

  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. Posted Jul 20 2008, 10:21 PM by Brian Knight with no comments Filed under: SQL Server 2008, clustering

• Webinar Series. So long and thanks to all the fish.

  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! Posted Jul 20 2008, 10:21 PM by Brian Knight with 3 comment(s)

• SSIS for the DBA Sample Files

  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 Posted Jul 15 2008, 11:17 PM by Brian Knight with no comments Filed under: SSIS, Performance Tuning, webcast, SSRS

• Files from Introduction to SSIS Webinar

  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 Posted Jul 15 2008, 12:38 PM by Brian Knight with 3 comment(s) Filed under: SSIS, webcast

• Making the SSIS Data Reader Source Query Dynamic

  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 Posted Jul 02 2008, 11:21 PM by Brian Knight with no comments Filed under: SSIS, data flow

• Free SQL Server 2008 BI / Admin Cert Test (beta)

  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. Posted Jul 01 2008, 11:14 PM by Brian Knight with 6 comment(s) Filed under: certification

• Webinar Summer Series Extended - 5 Days, 11 Hours of Free BI Training

  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 Posted Jul 01 2008, 11:07 PM by Brian Knight with 2 comment(s) Filed under: SSIS, webcast