SQL Server – Issues I have with SSDT based deployments: Part 4.1

Continuing my series on SSDT based deployments, in this part, I’m going to look into something that’s not entirely SSDTs fault, but I’m sure could be less painful: importing pre-existing databases into an SSDT project.  Particularly ones that have cross database references, or three part naming of objects (database.schema.object) which effectively make them a self referencing object.

Firstly, I want to state that I don’t really like cross database references.  In a world of filegroups, schemas and so on, I think a large number of multiple database setups should probably just be merged into a single db.  I address quite a few of these thoughts in my article SQL Server – When is a new database appropriate?

However, I’m also aware that this refactoring takes time, and that the damage is already there.  Add this into the fact that these databases will most likely not have been developed with any source control, and so somehow we have to drag them, kicking and screaming, into TFS and an SSDT project…..

1581831440-jesus_kicking_and_screaming_cartoon

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]
  • ON  [PRIMARY]
  • 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

Standardising new database creation using Powershell and SMO

Personally, I think that standardisation of a SQL Server is an important part of the DBA role; it should start by ensuring that instances are standardised, with the same naming convention and set of configurations added, and go all the way through to coding practices and object naming convention. It’s important not to miss off database creation from this list.
Continue reading

Scripting Indexes using Powershell and SMO

As part of a review of the indexes across my new workplace’s estate, I wanted to get all the indexes scripted up into re-runnable scripts which could then be loaded into source control.  However, considering the number of databases, tables and indexes, doing this manually would be both time consuming and mind numbingly boring; as such, I decided to create a powershell function to do this.
Continue reading