We currently have a SQL Server 2005 instance hosting a lot of application specific databases (ArchiveManager, SolarWinds, Kaspersky etc). We are planning to deploy a new SQL Server 2008 R2 instance on a new server and slowly migrate across to this instance. All of the applications have fairly straightforward migration plans for changing the database location normally revolving around stopping services, detaching, attaching and rerunning installers.
All looks good so far. My only concern is with SQL Server Agent jobs and logins. Initially I was under the impression that these were all stored in the system databases and that I could move these. I understand now that this is not the case. Microsft KB 918992
suggest running a script to generate a stored procedure and then calling this and running the generated script on the new instance. There seem to be a lot of caveats to it though. Another option would be to use SSIS to generate a "Transfer Logins" and "Transfer Jobs" package and run that (both editions are Standard) but I am unversed in SSIS so am a touch scared to use that.
I suppose my question boils down to this:
- Is the Microsft KB the best way to migrate logins to the new SQL instance?
- Is the only way to migrate tasks to the new instance via SSIS?
I am trying to get it as clear in my head as possible for my planning documentation so am very appreciative of any replies/guidance.
Best Answer
For logins, this is better IMHO:
http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror
And for jobs, you don't need SSIS. Just highlight the Jobs node in Management Studio, click on Object Explorer Details (F7), in the right pane select all, right-click and script.
I'd do the logins first, in case any of the jobs involve any of the logins.