Exposing SQL Server Risks in 1 minute

Often we find ourselves meeting new SQL Server environments for the first time and for SQL Server Reporting and/or Business Intelligence servers this is no exception.   Whilst your role may be purely business intelligence developer, it is handy to know where the current exposure to risks are (such as recoverability) that could potentially derail the Business Intelligence project later.

Developing a quick DIY dashboard in Report Builder or Power Pivot is now an easy reality however you could find yourself investing some time in developing the various datasets that interrogate SQL Server’s back end tables.

This nice article by Brent Ozar shows there is plenty of available out of the box community resource available, to help you build a set of datasets that expose some of these common risks.

SQLServerPedia Auditing Configuration

Searching for Extra Terrestrial SSIS or DTS Package Content

So you have your SQL 2008 R2 or SQL 2012 server up and running and  ready to pull the pin on that tired old Analysis Services.  You are concerned about something breaking when you turn out those lights for the last time.   A last minute search of any ETL packages you suspect may still look for the old server after it is gone…

For SSIS packages stored in the MSDB database, a fantastic article by James Greeves on how to perform an object search on all SSIS packages deployed on a server.

James Greeves

If we are talking legacy SQL 2000 content, then for DTS packages, the search becomes a little more troublesome.   A good discussion here about how to write an ActiveX script to perform said task.

SQL Server Central Active X Solution.

However this tool (DTS Power Search) is absolutely brilliant for a one off adhoc search of all DTS packages on a specified server or list of servers .  Even if the DTS packages have been imported as legacy to a SQL Server 2008 R2 instance, the tool will still identify and search the old package content nicely.

DtsPowerSearch

With SQL 2012 finally shutting the door on SQL 2000 you may find this article useful.

Happy object searching !