The SQL UPDATE Statement

This is just a quick note to let everyone know I’ve uploaded the project files from my Pragmatic Works webinar on Automating Web Application Deployments with SSIS. You can find the link on my Presentations page. If you missed the presentation you can view the recording of it here.

The zip file includes a readme document that highlights some of the important things to setup for project as well as an explanation of the different solution files inside the zip file. If you have any questions or problems feel free to leave them in the comments below!

Whether you know it or not, Policy-Based Management is an integral part of the new release of SQL Server “Denali”. Did you know that the HADR feature actually leverages PBM for checks? Yup, that’s right folks. If you’re an administrator and you’re NOT using Policy-Based Management yet, I highly recommend you pick up a book and check it out!

So what’s new in SQL Server “Denali” CTP3 for Policy-Based Management? I just got the shrink wrap off of it (I’m not quite the go-getter that Aaron Nelson is) but at a quick glance here’s the changes I see. These comparisons were made with listing of facets between SQL Server 2008 R2 and the new “Denali” CTP3 release.

We now have 84 facets (as opposed to 76 in 2008 R2). These are our new facets and their applicable target types!

Again, this still has that new install smell so I haven’t had time to mess around with it but I’ll soon be posting some new policies using these facets so you can get an idea of what you can do administratively using Policy-Based Management and these new facets.

Updating Resume is NOT Lulz

Wow, it’s been a crazy last couple of months with LulzSec running around doing what they do. Oh, what’s that? You’ve never heard of them? Well for many outside of IT this is probably the case. For those of us who ARE in IT have more than likely heard of them as well as their high-profile hacking exploits over the last couple of months.

This weekend TechCrunch posted a pretty good discussion piece on how the media has handled LulzSec’s exploits. To summarize in my own words, the author states that the general media cowered in the coverage of what this group was doing by hacking and leaking info of high profile targets such as the CIA (website), AT&T (internal data leaked) and Arizona Department of Public Safety (internal documents and sensitive information leaked). Carr goes on to say that rather than report on the seriousness of the group’s crimes and activities media would rather cheerlead them due to fear of retaliation from the group itself.

So what does this have to do with SQL Server? Well, if anything, I hope this rash of high-visibility targets has raised your awareness about something that far too many people slack in: Security. When was the last time you did a true security audit of your database servers? Are your web applications authenticating with the sa account (read also: “God” rights)? Are they authenticating with Windows accounts that are backed by stringent and contained groups via Active Directory? If you’re not certain of any of those questions I highly suggest you take a look at Brian Kelley’s (Blog | Twitter) SQL University Security Week posts from this past semester and start to at least formulate some kind of plan.

Security shouldn’t be an afterthought, it should be a base. As data professionals we hold are tasked with protecting the most vital piece of any organization: its data. Do you want to answer to your supervisor, manager and Executives when someone walks away with sensitive information from YOUR databases? Do yourself a favor and if you’re not already discussing security in your offices, start it. How do you handle security in your organization? Afterthought? Hardcore? What’s security? Let me hear your thoughts in the comments.

Ah DTS packages…they suck. Seriously, it’s 2011. It’s been over a decade and we still have SQL 2000 and DTS packages lingering around our shops…sitting there…taunting us…asking to be upgraded but HOW? There’s SO much work having to learn SSIS, figure out what the current DTS package even does, figure out what shiny new components in SSIS are equivalent to that legacy crap we created a decade ago and recreate it on the new platform! Well fret not, we at Pragmatic Works got you covered with a nifty tool called DTSxChange that will actually convert your DTS packages to SSIS for you!

DTSxChange is a powerful tool that converts your legacy DTS packages to SSIS (can convert to SQL Server 2005 or 2008 SSIS) for you with minimal effort. In addition to saving you a ton of work, the tool actually tells you how much time (and money) you’re saving using this tool over a manual effort via the DTSxChange Profiler feature. This tool allows you to scan all of your current packages and it generates a really nice report (read also: bosses/managers/CFOs love this)

So now that you know the scope of the work you’re looking at, let’s get to the nitty gritty and start converting! The conversion portion of the software uses an easy step-by-step navigation that lets you customize all sorts of options for your conversion efforts such as destinations, checkpointing, logging options, auditing framework options, connection manager consolidation, converting child packages automatically associated with a package, etc. The nice thing about this is you can convert single packages or hundreds of packages at the same time! If you want to see some of this stuff in action you can watch the video here.

We converted the stuff and now we’re done right? NO WAY! With DTSxChange you also have the option of adding on our custom Auditing Framework which gives you tons of useful reports (Note: Use Report Viewer Application to view auditing data) so that you can easily and quickly view the state of your SSIS packages once their deployed. Here’s a listing of all of the different reports you get:

• Which packages are currently running and which task is running inside the package
• Historical package execution detail for selected date range (i.e. Run time, Errors, Warnings etc.)
• Error and Warning by Task and Package
• Run time by Task and Package
• Extracted and Loaded Records along with their source and target information (e.g. Table/View, Sql Query, File Name, Component Name, Data Flow Name, Connection String etc.).
• Run time Trend for several days/weeks/years by Package and Task
• Error/Warning/ Trend by Package and Task
• Extract/Load Trend by Package, DataFlow
• Extract/Load Trend by Data Object (e.g. File, Table/View or Sql Query)

DTSxChange helps you migrate away from those legacy DTS packages and gets you on to the latest SSIS platform in minutes as opposed to days or weeks! Don’t believe me, try it out yourself. When you download a trial version of our product it comes with 3 conversions for you to play with. Would you rather have one of the experts at Pragmatic Works do a live demo for you and your company? We can set that up as well for you by sending us a quick email. So what are you waiting for? Kiss DTS goodbye today!

Prayer Cat Prays

Well not THE Bible, but rather the SQL Server Bible! I’m incredibly excited and proud to say I’ll be one of the primary authors on the 2011 edition (aka “Denali”). Who are the rest of the folks involved on this venture? Check out this great list of authors:

Primary Authors

• Adam Jorgensen (Blog | Twitter)
• Patrick LeBlanc (Blog | Twitter)
• Aaron Nelson (Blog | Twitter)
• Denny Cherry (Blog | Twitter)

Contributing Authors

• Audrey Hammonds (Blog | Twitter)
• Julie Smith (Blog | Twitter)
• Scott Klein (Blog | Twitter)
• Tim Chapman (Blog | Twitter)

Lots of exciting new stuff coming down the pipeline with this release of SQL Server so I think I can safely speak for all of us when we say we’re extremely excited to be putting this title together!