SQL Server Replication Monitor - Agents

Moving Filegroups on a SQL Server Database under Replication

Sooner or later, with any SQL Server database of a suitably large scale, you’re going to run into a situation where you start hitting hardware limitations.  It may be disc space, it could be IO issues caused by concurrent access of multiple filegroups and databases on the same controller.  Either way, you’re likely to hit the point where you make the decision that you need to move some filegroups around to distribute the load across several drives.  Alternatively, you may just want to reorganise your DB files into a more logical structure.

This is very straightforward to do normally via backup and restore, or simple detach and attach.  However, when your database is under replication, either as a publisher or subscriber, this simple process becomes a little more difficult.  Thankfully, there’s a very simple solution to this, which I came across the other day while spreading our DB across multiple drives. Continue reading…

Script Task Project Properties in SSIS 2012

Calling external DLLs from a Script Task in SSIS 2012

The 2012 version of SQL Server has brought with it a number of new features, not least the new, Visual Studio 2010 shell-based Management Studio. However, the changes are more than just aesthetic, as I found out when I came to try and upgrade some SQL Server Integration Studio (SSIS) packages from SQL server 2008 to 2012. Most packages upgraded fine, but there were some issues when I tried to upgrade any packages that used Script Tasks containing references to external DLLs. Continue reading…

How To Define an XSD file for an SSIS XML Source using an expression

Invalid XML source in SSISI’ve been looking at migrating some SQL Server Integreation Services (SSIS) packages lately, and as part of the move, am parameterising a lot of settings to improve the ease of future configuration changes.  One of these changes involved a package that reads from an XML source and uses an XML Schema Definition (XSD) file to validate the source file.  However, with the XML source being in the data flow, you’re not allowed to set an expression on this property to assign a value at runtime.  Or at least not directly.  So, if you want to assign the XSD location from a variable or project parameter, just follow these simple steps. Continue reading…

How To Enable Remote Errors in SQL Server Reporting Services (SSRS)

Just a quick one for a Wednesday afternoon.  I was recently asked if it was possible to enable remote errors in SQL Server Reporting Services (SSRS), so that people (and applications) could return full error details from RDL execution, without having to consult the local log files.  It’s an incredibly easy task to enable this.  Here’s how: Continue reading…