Migrating Legacy DTS packages

The environment: SQL Server 2000, SQL Server 2005 and SQL Server 2008

Consider an environment in which you're asked to migrate a large number of DTS packages from one of your servers onto another server.  You could manually move each package, but if the following conditions are met, a simple insert statement into msdb..sysdtspackages can make your life a lot easier:

• you don't need to convert your DTS packages to SSIS packages
• all of your packages are stored on the server (rather than as a file)
  

use [msdb]

go

insert into sysdtspackages

    select [name],
        id,
        versionid,
        description,
        categoryid,
        createdate,
        owner,
        packagedata,
        owner_sid,
        packagetype
    from [linkedserver].msdb.dbo.sysdtspackages

 

Choosing a monitoring solution for SQL Server

Depending on the size of your environment, you might find yourself in need of third-party software to monitor you various SQL Servers.  I'm going to examine various tools and outline my findings below.

It should be noted that there is not one solution for everyone - your solution can and should vary based on the size of your environment, budget, monitoring needs, and your personal preference.  I've discarded several of the solutions below because I simply did not like either the implementation or the interface.  Also, this is not at all a comprehensive list of monitoring software - this represents the result of a quick search of several SQL forums' recommendations, as well as a quick Google search.
I have need to monitor about 8-10 servers - 5 production clusters, 2 reporting repositories, and 1 dedicated replication distributor.  I need job monitoring, query collection, and counter monitoring.  Reports and trending would be nice (this is something I've found a bit lacking with our current solution).

** This post does not constitute a recommendation or endorsement of any of the products below.  I am not an employee or representative of any of the companies listed.  I am not being compensated in any fashion in conjunction with these recommendations.

Idera (http://www.idera.com)

SQL Diagnostic Manager

Pros: relatively intuitive dashboard views, multi-server views, very usable interface

Cons: occasionally buggy; I find some of the alerting annoying and obtrusive

Features: query collection at configurable intervals, SQL and Windows counter alerting and collection, job monitoring and alerting

Price Point: $2049 per instance, plus maintenance of $409.80

Notes: We are currently using this in our production environment (I'm examining other solutions as the license is up for renewal)  There are a few bugs in our version, but I've spoken with their support team, who is responsive and helpful, and it seems that these are resolved in the latest version they offer.

Confio (http://www.confio.com)
Ignite
Pros:
Cons: Java-based web client.  I prefer a standalone executable.
Features: Did not get as far as configuring this to run.
Price Point: N/A
Notes: As stated above, I installed this, but did not get past the configuration part for reasons stated (this is a web client, rather than a standalone executable) 


Quest (http://www.quest.com)
Spotlight on SQL Server
Pros:
Cons: The only con is that our shop is currently using Idera SQL Diagnostic Manager, which we can renew (and download the latest version) for far cheaper than we can purchase a new install of Spotlight for.  The outright purchase price is comparable with SQL Diagnostic Manger, though this seems to offer far more powerful tools with respect to reporting and trending.
Features:  Configurable alarms for Windows and SQL Server counters, fully configurable data collection scheduling, configurable custom counters, file monitoring, job monitoring, error log monitoring, and quite a bit more that I don't really have time to examine.  The default view is a little confusing and uninformative (consists of a lot of large, brightly colored circles and arrows and animations simulating network traffic) but the other views are very slick - there is reporting and trending for any of the counters (ex: Database File IO Statistics, Plan Cache - 2000 and 2005 only, for now).  It looks like the reporting and trending relies on a statistics repository that I've not yet configured.
Price Point: per server license of $1,995.00
  

Red Gate (http://www.red-gate.com)
SQL Response
Pros: Small, light-weight and easy-to-use.  Initial setup and configuration is minimal and intuituve.
Cons: lacks configurable trace - in the past, I have found it extremely useful to collect snapshots of running queries at set intervals, for the purpose of troubleshooting.  The "enable trace" option only collects for a given interval before/after an alert is raised, and retention is highly minimal.
Features:
Price Point: $619, with support/upgrades
Notes: I really like Red Gate's other products (SQL Compare, SQL Backup, etc) - they know how to create a good-looking, functional interface.  My experience with their sales and support staff has been overwhelmingly positive.  This would be my tool of choice at a smaller shop with less rigorous performance monitoring requirements.


Embarcadero
Performance Center 2.7
Pros:
Cons:
Features:
Price Point:
Notes: cancelled install when it prompted to install Apache

Find the last full backup date for all databases on a server

Consider a situation in which you're taking over a number of SQL Servers, and you find yourself wondering about the backup situation - maybe you need to pull a full backup history for each database on each server, or maybe you just need to verify that your databases are being backed up.  This is a quick script that pulls the last full backup date for each database on the server - I'm including a SQL Server 2000-compatible version of the script:

SQL Server 2000

select d.[name],
    x.backup_date
from sysdatabases d
    left outer join
    (
        select database_name,
            convert(varchar(50),max(backup_finish_date),110) as backup_date
        from msdb..backupset
        where [type] = 'D'
        group by database_name
    ) x on x.database_name = d.[name]
order by d.[name]

SQL Server 2008 (should also work for SQL Server 2005)

;with x as
(
    select database_name,
        backup_size                                 as backup_size_bytes,
        backup_size / 1024                          as backup_size_kbytes,
        backup_size / (1024*1024)                   as backup_size_mbytes,
        compressed_backup_size,
        convert(varchar(50),backup_finish_date,110) as backup_date,
        [type],
        row_number() over ( partition by database_name order by backup_finish_date desc ) as rnk
    from msdb..backupset
    where [type] = 'D'       
)

select d.[name],
    d.recovery_model_desc,
    x.*
from sys.databases d
    left outer join x on x.database_name = d.[name]
                        and x.rnk = 1
order by d.[name]

Note that this is only the first step in your due diligence - you still have to determine where your databases are being backed up to, your retention window (locally and on any off-site tapes or servers), and (of course) run periodic fire drills to make sure your backup solution is adequate to the task - more on that in a later post.