SQL Server – When is a new database appropriate?

I frequently get requests from our developers to create new databases.  This is no biggie, and using the Standardising new database creation using Powershell and SMO scripts takes no time at all to implement.  However, something that usually isn’t considered at the point of a request is: why do we need a new database?

Continue reading

SSIS – Script Tasks to replace file system tasks

I’ve recently been working on a large ETL project for a data migration, so I’ve been spending far more time working in SSIS than I would normally do so. As with any ETL project, there’s a substantial portion of outputting data to files, both Excel and text, and moving those around a filesystem to various locations. Worst still, to write to an Excel file, it must already exist, with all the correct column headers. Eurgh. This means I need to create a bunch of template files, and copy them in to place for me to then use as the target in my Excel Destination. I really hate Excel….

SSIS comes with file system tasks built in which would enable me to do this. However, I find them notoriously flaky and unflexible; for instance, if a file might exist, but it also might not exist, they don’t really seem to like it. Plus, sometimes, I just get random errors; I’ll run it again and it will work, without changing a thing. This is neither good for my sanity, or my sleep when I’m on call.

Fortunately, SSIS also has the solution: the Script Task!

Continue reading

Replacing SQL Text within a script file using regular expressions and Powershell

I recently needed to update a load of scripts in our Source Control to remove the filegroup declaration from ON [PRIMARY] to just not be declared, so that new objects would be deployed to the default filegroup on some new databases we had created. Personally, I like to keep user tables away from the system tables to assist with being able to do piecemeal restores, and allow easier management of database files.

From looking at the scripts, written by lots of different devs, there were various different standards and styles that had been applied, from not declaring a filegroup (yey no work for me!), to differing numbers of spaces between the ON and [PRIMARY] keywords:-

  • ON   [PRIMARY]

All in all, there were a few hundred scripts that I needed to make this change to and I had absolutely no intention of doing that manually. So: to Powershell!

Continue reading