Posts

Showing posts from 2011

SSRS key backups and PowerShell

Whenever I do anything with an SSRS server, I always backup the key and park it somewhere. Automating that would be handy, and  this seems to fit the bill nearly 100%. The only additional think that I would like to see something that uses PasswordSafe or KeePass to keep a password, rather than a table.

Running SSIS Packages in Denali

I was skimming SSIS Logging in Denali and I noticed that the author used a feature that I had heard was going to be in Denali, but had forgotten about. You can run a package using straight-up Transact-SQL. Developers have been asking me for this ability for four or five years. Since it wasn't available, we have tried and/or implemented all sorts of stuff. Things like: Running packages via SQL-CLR. (Microsoft recommends that you don't do that.) Running packages via a call to a web service running on IIS that is co-located on the same server as SQL Server. (That is a lot of extra work, installation and configuration. It is more stuff that can break and it is more code to maintain.) Setting up a job and letting developers have all sorts of permissions just to test a package on a server. (That is more configuration to do and you are going to wind up with jobs whose purpose has been forgotten as developers transition  into and out of projects.) Denali looks better and better...

Quickly adding new columns to tables in Denali

More and more, I am starting to really like what I see in Denali. The other day, this headline caught my eye: "ONLINE NON-NULL WITH VALUES COLUMN ADD IN SQL SERVER 11". In short, it will be possible to quickly add a new non-nullable column to a table. This operation is quick because it is a metadata change. Prior to this new feature, it would be necessary to wait until the server physically manipulates every row in the table. This manipulation would involve exclusive table locking. It would possibly include splitting pages and lots of disk I/O. This feature would be an absolute boon in a 24x7 system and it should be handy for avoiding lengthy schema upgrades on any system with large tables. You can read about this feature in detail  here .

A quick review of SolidQ's latest e-book, on Data Quality and Master Data Management

The folks at SolidQ are providing a free e-book: Data Quality and Master Data Management with Microsoft SQL Server 2008 R2 Dejan Sarka, Davide Mauri I had a look through the book and I'm providing this tiny synopsis/review. The book can be found here . Other than marketing blurbs, I haven't really been exposed to MDM until I read the book. So, I am starting more-or-less from scratch. Chapter 1, "Master Data Management" seems to be a decent primer. It presents a background on relational and dimensional models, and where they and MDM fits in the universe. Chapter 2, "Master Data Services Concepts and Architecture", discusses MDS in detail. I think that this gave me a good feel for how MDS is supposed to work. Nonetheless, I think that most of that could be gleaned from other sources. The hidden gems here are in the second half of the book, which is comprised of chapters 3 and 4, "Data Quality and SQL Server 2008 R2 Tools" and "Ident...

HTML5 Talk at FIS in Malvern

I attended the inaugural meeting of the Philadelphia Google Technology User's Group (PGTUG) last night. The content of the meeting was a talk given by @kurrik, a Google Programs Engineer, on new HTML5 features. I was the only attendee brave (or uncool) enough to sit in the front row. While I am an admin by definition, I am a coder at heart. For evidence,  I can submit thousands of Transactions-SQL and Powershell scripts. Even so, web development isn't my 'thing'. It was interesting to see what the development community is up to. This was particularly true since the talk was Chrome centric. I drink plenty of Microsoft Kool-Aid as it is. The talk was fast-paced, with 68 slides in less than 90 minutes, including demos. The talk was nominally about items in the HTML5 specification. While competing products were mentioned, it might be more accurate to say that the talk was on Chrome's implementation of certain HTML5 features. The talk did not get bogged down in det...

Designing Highly Scalable OLTP Systems

I have been spending time watching SQL Bits videos lately. So far, the video that I find most interesting is " Designing Highly Scalable OLTP Systems ". The material was presented by Thomas Kejser and Ewan Fairweather, who are both members of the SQLCAT team at Microsoft. Several years ago, I saw a system with the the compilation lock behavior that they describe in their talk. We happened to use the same method that they suggested to remediate the problem with our system. Since the code was in stored procedures, making these changes was not difficult. We measured a transactional throughput increase, but It is always nice to get additional, real-world validation. I am also intrigued by the "hash partitioning" strategy used to avoid hot pages in tables.