Andy Leonard : SQL Server 2008

SSIS Design Patterns, the Book

andyleonard — Mon, 06 Aug 2012 16:37:43 GMT

For the past two years, I have had the honor and privilege or authoring SSIS Design Patterns alongside Jessica Moss, Michelle Ufford, Tim Mitchell, and Matt Masson. Publication of the book – like many projects of this scope – has been delayed. The current...(read more)

SQLUniversity Professional Development Week: Learning To Fly

andyleonard — Fri, 25 Feb 2011 12:00:00 GMT

Introduction Clem and Jim Bob were out hunting the other day in the woods south of Farmville. As they crossed a ridge, they came upon a big ol' Momma Bear and her cub. The larger bear immediately started towards them. Jim Bob took off running as fast...(read more)

Presenting at the PASS Application Development Virtual Chapter 8 Sep!

andyleonard — Thu, 03 Sep 2009 11:00:00 GMT

Business Intelligence at REST September 8th at 1:00 PM Eastern Presenter: Andy Leonard I am honored to present Business Intelligence at REST to the PASS Application Development Virtual Chapter on 8 Sep 2009! This is going to be a fun presentation for...(read more)

Presenting Incremental Loads, CDC, and SSIS 2008 to Columbus SQL Server UG Tomorrow!

andyleonard — Wed, 08 Jul 2009 15:30:00 GMT

I'm honored to present Incremental Loads, Change Data Capture, and SSIS 2008 to the Columbus SQL Server Users Group tomorrow evening! This is the first remote presentation I've attempted in a while. I won't be in physical attendance, only virtually. I'll...(read more)

xp_delete_file

andyleonard — Wed, 11 Mar 2009 22:00:00 GMT

Introduction

I built a SQL Server 2008 Maintenance Plan and included a Maintenance Cleanup Task. For those who don't work with this stuff every day, Maintenance Plans are actually SSIS packages. The short version of a long story is: I couldn't execute the Maintenance Plan on the server. I won't go into that (not now, at least); but I noticed a View T-SQL button on the task:

When I clicked the button, I was presented with a textbox containing the following T-SQL:

EXECUTE master.dbo.xp_delete_file 0,N'E:\Database_Backups',N'bak','2009-03-07T16:45:17',1

I Don't Know Either

I said to myself: "Self, this could be a very useful extended stored procedure." So I popped open Books Online to look it up and... no topic found. Not a problem.

I popped over to MSDN and started scrolling through the online documentation and... no topic found. Not a problem.

I searched MSDN and... topics found. Yay! I clicked on a link to Connect about the topic missing from the documentation and... the link was broken. </HowIronic>

What I Do Know

I actually needed to clean up older backups, but I thought I could use this to also clean out old Team Foundation Server WSS (Windows SharePoint Server) logs that were filling up one of my drives. It turns out I could.

I edited and tested the following code:

declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -24, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
EXECUTE master.dbo.xp_delete_file 0,N'E:\WSSLogs',N'log',@DeleteDate,1

It works. What's more, it runs at the same time as my nightly backup jobs (as a step in the same SQL Agent Job) and leaves my server fresh and clean for another day of pleasant computing.

Conclusion

I still do not know what the numeric arguments accomplish in the xp_delete_file extended stored procedure. But I was able to get some use from it nonetheless.

:{> Andy

Presenting at New England SQL Server User Group!

andyleonard — Thu, 29 Jan 2009 03:35:00 GMT

I am getting psyched about presenting to the New England SQL Server User Group in a couple weeks! On 12 Feb I'll be talking about Incremental Loads, Change Data Capture, and SSIS 2008.

The bonus is I get to hang out with my friend Adam Machanic!

If you read this blog and will be in the area, stop by and say Hi.

:{> Andy

SQLPass 2008: Thursday Keynote

andyleonard — Thu, 20 Nov 2008 16:39:00 GMT

Introduction

So, it's a few minutes before the PASS Summit 2008 Thursday keynote, and I'm settled into the Press Table behind the sound board. Tony Davis is here. Steve Jones is here. And I'm seated beside Brent Ozar.

Rushabh Mehta

To complement Wayne Snyder's entrance on a bike yesterday, Rushabh Mehta showed up this morning on a cool-looking scooter - a scooter that Idera is giving away later today!

Rushabh engaged in a conversation with the disembodied (though intriguing) voice he called "Voice Over".

Rushabh: "I used to have such long, beautiful, and shampooed hair - like Donald Farmer!"
Voice Over: "Thanks Rushabh. Have a great keynote."
Rushabh: "Thanks Voice Over, I will!"

Rushabh is a great speaker. If you've never heard him speak, you've missed out.

So Far

It's been a great conference so far.

Summit updates for today: Women In Technology Luncheon. The Exhibition Hall closes today at 5:00 PST. The PASS Summit 2008 has a fantastic exhibition.

Q: Where else are vendors going to find 3,000 SQL Server geeks concentrated in one place?
A: Nowhere.

Tonight!

SQL Server Heroes Unite Party is tonight, starting at 6:00 PM. Rushabh warned us: "If you drink a bit too much, you might go home with a tattoo." A Flock Of SQLs is playing. I can't wait. I wonder if they'll allow an picker to sit in on a tune? Maybe a little Southern Rock?

The New SQLPass

SQLPass finances are opening up. It's really cool to see PASS opening up about finances - this has been a consistent criticism and it's great to see the PASS Board responding. I won't bore you with the numbers. They're on the website.

Membership is now free!

More Announcements from Rushabh

Sqlpass.org is no longer in beta! [Applause]

Key improvements: Community-focused content.
Technical Articles, PASS Tips, PASS Blog, 300 hours of recorded content!
Did I mention this is free?

PASSPort
- Connect
- Share
- Learn
- Be Recognized

You can find this new content at sqlpass.org - under Community.

Among the cool announcements today, I paid particular attention to the Hosted SQL Server 2008 trial - very cool! This allows to try out some cool features of SQL Server 2008 without having to install and configure it.

PASSion Award - recognition. The 2008 PASSion Award goes to: Kathi Kellenberger, SQL Server MVP. Congratulations Kathi!

Tom Casey and Pervasive Business Intelligence

Tom's theme was moving Business Intelligence as a specialization to Business Intelligence for everyone by turning insight into action.

Bruno Aziza demonstrated a dashboard built with PerformancePoint Server, Microsoft Office SharePoint Server, and SQL Server. It included Outlook integration, Excel docs, and lots of other cool stuff integrated into a personal portal. Collaborative integration was also featured. Pretty cool stuff

The BI stack integrates spatial data and leverages collaboration in this context. "Continuous" is a key concept here: Continuous communication, Continuous metrics, all in near real-time. Tom's point to the PASS Summit 2008 attendees: This is built on SQL Server.

Why upgrade to 2008?

- Merge upsert
- Data profiling
- Resource governance
- Data Compression
- SSAS Design Guides
- Data visualization
- Scalability for DW
   - 100s of Terabytes (Project Madison)
   - 12T is the average for DWs now, about 5% are around / over 25T
   - DatAllegro is an opportunity to scale over time.
   - ETL Performance and improved SSIS Connectivity
    - 1.1T loaded in 30 minutes using SSIS. Compared to 45 minutes with Oracle and Informatica. Record set in February.
   - Reporting improvements. Report Builder 2.0 enables users to self-serve with ad hoc reporting.

Report Builder Demo - Carolyn Chau

Carolyn's demo or Report Builder 2.0 was really cool, but I believe a lot of folks missed an important point: She was dragging and dropping metrics onto the design surface from a 1.8TB Analysis Server cube. And the data preview was refreshing in milliseconds! Very impressive!

The editors are improved and interactive - which will facilitate a nice productivity boost for developers. Gauges in the table - very cool, and an impromptu Undo demo - also very cool! The mini-trends are a really slick "quick" visualization element.

<Editorial>

There was some push-back from the relational folks to some comments (or interpretations of comments) from Tom about the importance of BI. Some of it is understandable. Some of it is not. The title of the keynote clearly identified the topic as Business Intelligence. That's what it was.

No one (no one I know of anyway), thinks BI is the end-all be-all. It's a tool in the toolbox. I mean, relational folks, you're not still in command-line, are you? Your tools have changed. BI actually builds on your systems. It's an application, pure and simple. To think of it in any other way is a mistake, in my opinion. It's an integrated app, mind you, and ever-more-coupled to your relational engine.

You can choose to ignore it. Personally, I don't think you'll need to know Business Intelligence to continue working in the database field. And you don't have to like it. Bashing it, though, is a mistake. In my opinion. Underestimating the value is a similar mistake. Just because you or I don't see all the value does not mean it's not there.

Me, I like to learn new stuff. You may not. That's cool. It's a big database world out there. Cool?
</Editorial>

Insight into Action == More Productivity

Microsoft is leveraging Business Intelligence internally - from the Performance Warehouse to Enterprise Data Warehousing. The cool part? It all fits. This forms the basis of the new stuff. It's a constantly-evolving vision, somewhat realized as of 2008. But that's the way it will (and should) always be: the vision should evolve and ever be in a state of "partial realization".

Gemini and Donald Farmer

Under-the-desk apps can shut down companies. There is a widening gap between end users and IT needs. How to bridge the gap? Enter Gemini. Enlighten the application infrastructure; enable the end user to access data easily. The demo leverages SSAS as a service-provider for MOSS.

Donald demo'd a cool-looking Operations dashboard on the Gemini screen and drilled into some Sandbox portal visualizations. Very cool "chartlets" if you will. Nice drill-through into sub-dashboards.

There was an odd little bubble chart in the center of the demo screen. The odd part was the Play button beneath the visualization. And then Donald clicked it and wow - the bubbles (representing KPIs) went into motion! The animated graphs are awesome! Being able to visualize the data in this way added a depth to the data that's difficult to describe if you haven't seen it. The relationships between KPIs are clearly visible - and in a way I haven't seen before.

In addition, the security status report is a fantastic idea!

Once again, Donald Farmer has demonstrated landscape-changing technology for BI. ROI will come from gaining actionable insight in the context of the common user experience. MarketingSpeak? You bet. Accurate? You bet.

You can learn about all this by searching Kilimanjaro. I can't wait.

:{> Andy

Speaking at Raleigh Code Camp tomorrow!

andyleonard — Fri, 14 Nov 2008 11:00:00 GMT

Code Camp RDU Fall 2008 is tomorrow (15 Nov 2008)! If you're going to be in the Raleigh area tomorrow and are interested in anything related to Microsoft technology, register and check out the sessions. I always have a blast in Raleigh!

I will be presenting my popular session: Incremental Loads, Change Data Capture, and SSIS 2008. Hope to see you there!

:{> Andy

Speaking at Midlands PASS Chapter in Columbia SC!

andyleonard — Fri, 26 Sep 2008 20:48:00 GMT

I finally get to meet Brian Kelley in person!

I'll be presenting Incremental Loads, Change Data Capture, and SSIS 2008 to the Midlands PASS Chapter in Columbia, SC on Thursday 2 Oct 2008! If you're in the area and read this blog, stop by and say "Hi".

:{> Andy

SQL Server 2008 Management Studio - Function Keys

andyleonard — Fri, 08 Aug 2008 16:17:00 GMT

F7!

The F7 key displays details in SSMS 2008. I'm almost positive SSMS 2005 RTM'd with the same funtionality and it was later removed.

F8?

I love the F7 key. And I love that it displays details when I highlight something in Object Explorer - which, by the way, no longer displays when I press the F8 key... (grrr) - and then press the F7 key.

Functionitis

As a keyboard guy, I think it would be nice to have the Object Explorer display when I press F8 and Details display when I press F7. I'm sure there's a reason we don't have both in SSMS 2008, even if I don't know what that reason is.

I'm not starting an online petition to have it changed. I'll just work around it.

Multiple Mappings

I sometimes get myself in trouble with function keys.

A few weeks ago I was delivering SSIS training and attempted to demonstrate how you can remove all breakpoints in the Visual Studio 2005 IDE with Ctrl+Shift+F9. I pressed the key combination and it didn't work. Durnit!

I have Camtasia loaded on my presentation laptop. It just so happens Ctrl+Shift+F9 starts/stops recording via the Camtasia plug-in in PowerPoint. I recorded the remainder of the day.

:{> Andy

SQL Server 2008 ReleaseWatch!

andyleonard — Tue, 22 Jul 2008 17:54:00 GMT

With the imminent release of SQL Server 2008, I think we should set up a SQL Server 2008 ReleaseWatch.

Who's in?

:{> Andy

Installing SQL Server 2008 RC0

andyleonard — Tue, 15 Jul 2008 03:01:00 GMT

Introduction

SQL Server 2008 RC0 has been out for a while. You can download it here.

This post is not a detailed examination of the setup options. There are much better articles and posts out there if you want more information. This post represents one way to get SQL Server 2008 RC0 up and running so you can start learning the new features!

Getting Started

I downloaded the software and installed it on a brand new virtual machine running Windows Server 2008 Enterprise (without Hyper-V). I used VMWare Workstation for this virtual machine because I need a 64-bit guest OS. I was also able to configure the machine to use both CPUs on my host system. This may be possible with Virtual Server or Virtual PC, but I don't know how to do it (if it is).

I loaded the ISO image for SQL Server 2008 RC0 64-bit into the virtual DVD drive and started the setup application. The following screen displays:

Now this looks a lot different from previous SQL Server installation applications. In general, you navigate between pages using the Page List on the left. To continue installing click the Installation link in the Page List:

Installing a Stand-Alone Default Instance

To install a new instance of SQL Server 2008, click the "New SQL Server stand-alone installation or add features to an existing installation" link. The Setup Support Rules wizard executes and checks your system configuration:

Green checkmarks == good. When you click OK the Product Key screen displays. On this screen you can select any of the available evaluation editions or enter a product key:

Click Next to continue. The License Terms page is next. Accept the license terms and click Next to proceed:

The Setup Support Files page displays next. Click Install to begin application setup:

The first step in setting up the application is checking the Setup Support Rules:

On this execution, the Setup Support Rules detected Windows Firewall and issued the following warning:

I will address this warning after setup is complete. The next page is Feature Selection. Select the SQL Server 2008 features you wish to install and their respective directories, then click the Next button:

The Instance Configuration page allows you to specify the SQL Server instance name and root directory:

Next, disk space is checked. Click Next to proceed:

Before configuring the Server, I created a SQL Server service account. Right-click My Computer and select Manage:

Navigate to Server Manager \ Configuration \ Local Users and Groups \ Users. Right-click in the Users pane and select New User:

I named my service account SQLDude:

Returning to the Server Configuration page of the SQL Server 2008 RC0 installation application, I click the "Use the same account for all SQL Server services" button and supply the credentials for SQLDude:

Click OK to assign the credentials to all services:

Click Next to proceed to the Database Engine Configuration page. On this page, configure administrator accounts, data directories, and the filestream options:

On the Data Directories tab, set a data root directory and edit any associated default paths:

I enabled filestream on the FILESTREAM tab. I haven't tested this yet. Configuring Filestream did not work in the CTPs (this was one the bugs I reported):

The next page is Analysis Services Configuration. Select administrators and data directories for SSAS 2008:

Reporting Services Configuration is the next page. I installed the native mode default configuration:

Click Next to set Error and Usage Reporting options on the next page:

Click Next to execute the Installation Rules. If there are no errors or warnings, click Next to proceed. Otherwise, stop here and address them:

Click Next to navigate to the Ready to Install page. Review the summary and click Back to make any changes. Click Install to install SQL Server 2008 RC0:

Once the installation completes the Next button will enable. Review the Status to make sure all items are marked Success and click Next to complete the installation:

Click Close on the Complete page to unload the SQL Server 2008 RC0 installation application:

That's it! You've successfully installed SQL Server 2008 RC0 - congratulations!

:{> Andy

SQL Server 2008 RC0 Download

andyleonard — Tue, 10 Jun 2008 18:48:00 GMT

The SQL Server 2008 RC0 download is available to the general public here.

For goodness' sake, don't load this on a real machine. Go get Virtual PC or Virtual Server - they're both free. You can get the Trial Editions of Windows 2008 Server here:

Windows Server 2008 Standard (x86 and x64)

Windows Server 2008 Enterprise(x86 and x64)

Windows Server 2008 Datacenter(x86 and x64)

Windows Web Server 2008(x86 and x64)

You can also get a configured virtual hard drive here - already installed and ready to run.

Regarding virtual machines: invest a day now if you are not familiar with this technology. It will save you weeks and months in the future, I promise.

After you get your virtual envirnoment set up and running, you can install anything you like. This protects your laptop or desktop from "bad things" happening with a CTP or RC installation. You will thank me later.

Using Virtual PC and Virtual Server, you have to install the x86 versions of the OS and SQL Server 2008 RC0. For me that's no big deal - I'm not loading it to stress it (not yet anyway), I'm loading it to learn more about it. You can too.

:{> Andy

Speaking at Philly Code Camp 2008.2 Saturday

andyleonard — Thu, 15 May 2008 03:31:00 GMT

I will be presenting Change Data Capture, Incremental ETL, and SSIS 2008 to Philadelphia Code Camp 2008.2 Saturday, 17 May 2008!

The good folks in Philadelphia always do a great job with Code Camps and the attendance numbers are growing to reflect that! They're sold out again. Great job!

:{> Andy

Thanks PassMN!

andyleonard — Wed, 30 Apr 2008 01:43:00 GMT

I had a fantastic time presenting Change Data Capture, Incremental Loads, and SSIS 2008 to the Minnesota SQL Server User Group tonight! They're a fun bunch.

It was cool to meet Lara and Mark in person - and thanks Lara for bailing me out on a couple relational engine questions! (I am such a developer sometimes...)

:{> Andy