3

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.

Improve this question

1 Answer 1

Reset to default
3

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.

enter image description here

I'd do the logins first, in case any of the jobs involve any of the logins.

Improve this answer
6
  • +1 Great info. I personally revert to the SSIS solution. Between it's simplicity (it is extremely simple, even if you never work with SSIS), options, error handling, and reusability. Handles more than just logins and jobs for server-object migration. Thought worth mentioning. Again great answer, Aaron, as I've used to this process many times without a single issue.
    – Thomas Stringer
    Apr 17, 2013 at 15:35
  • I only had one concern about the linked script in that it states this is for mirrored or linked servers? I assume this would be a case of configuring the new instance as a linked server on the old instance before running the scripts?
    – user20442
    Apr 17, 2013 at 15:41
  • It's labeled that way because it's made for guarding your mirror against the orphaned login problem if you fail over. That doesn't mean it's the only use case. In your case you can consider "mirroring" and "migration" to be synonymous.
    – Aaron Bertrand
    Apr 17, 2013 at 15:42
  • Probably a stupid question but just to clarify. the script seems to run with a variable for the new server (@partnerserver =). Even if I have not added this server as a "Linked Server" under "Server Objects" (in SSMS) then it should still carry out the work (assuming I have a login to the new instance before running the query)? Apologies if this is basic
    – user20442
    Apr 17, 2013 at 15:53
  • 1
    No, you need a linked server to run the script exactly as is. You can modify it slightly to print out the commands so that they can be copied and run directly on the other box, but that seems more work than necessary. Just set up a linked server.
    – Aaron Bertrand
    Apr 17, 2013 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.