Migrating Sql server DB to another Server

Question

Hello,
I am planning to migrate the some Sql server databases to another Server on same domains but some of the databases have cross connection dependency.
I mean some of the database either View or Stored procedure I found that it's calling to another database table so it referencing one or multiple databases in some of the stored procedures or views.

What's the best approach to migrate the database(s)?
To make it clean migration, I was thinking to do one by by so we can have thoroughly testing as it has some SSIS packages or jobs but after finding database objects dependency, looking the suggestions.

Thanks for your help!

Answer 1

Hi @pdsqsql ,

You can use the Data Migration Assistant tool to migrate your databases.DMA can migrate multiple databases at once.Please test in the test environment before applying to production.

DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.
https://learn.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Hot issues in October—Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

Old School...

• Open Jobs and hit F7 so you have them all highlighted.
• Script them out somewhere and save.
• Save your backups for restore after the move
• If you have database mail settings, write it down
• if you have maintenance plans, get familiar with them so you know what to recreate
• if you can have both the new and the old, you can cross reference the old to the new
• Audits - script them out
• Facets, right click and get a screen shot of the facets
• Are you running integration services = do your have dtsx packages that need to be moved
• SQL Agent = operators? who are they? Is SQL Agent running notifications
• additional settings like dcom, SDK backward compatible features, other items that experience might help with
• special security settings - what are they? Write them down.
• msdb permissions
• security group permissions
• user permissions

I've not used the tool, but as Criszhan suggests, DMA might make life easier.
Old School,
You have a list, check it twice and get someone to look it over who knows your database or can ask questions to trigger you into thinking out all the scenarios.

Answer 3

Thanks JLongstreet for your detail migration steps.
What's the best way to restore both Sql and Windows logins?
I am having trouble to get the login scripts as what I have that not much helping and I have to fix it manually so if you have any script or link?

I will also try DMA but curious that i have to make any changes for permissions or other things except the new server info?

Answer 4

If you only are moving some of the databases, and the views now relate to databases in different instances, you'll have to recreate these views.
For SP

Answer 5

Almost everything in SQL Server Management Studio can be scripted. If you right click the user, you can script the user (which brings me to ask is the server in mixed mode or windows authentication) but additionally, you should be able to do a CTRL A and script the entire set of logins at the same time. Ditto on the Jobs, any audits.

I find it more important to let it stew while you make updates to the list because it is so easy to forget things. Make sure your list matches with your plan to restore.