Skip to content

jstexasdba Posts

Featured Post

Database Migrations – Time to move them with dbatools

In my last post, I wrote about Database Migration – Capacity Planning with dbatools. There are quite a few different functions to do these types of things using dbatools. They even have a function named Start-DbaMigration. I used that command about 2 years ago and it worked wonders for me. In this post, I am going to show you the steps that I decided to take to do this particular migration.  This migration was going to be a bit different. I am merging two systems together, so I had to really think the process through and ensure I get everything I needed. I also did not want to rollback due to the systems being legacy and unsupported. Some of the hosts were going to be pushing storage limits so I had to ensure everything went flawlessly so I could decommission the legacy systems quickly.
I am working with several locations and with each having different amounts of bandwidth; I wanted to capture how long the process took at each location. For that, I referenced this recent blog post “Using a Stopwatch In PowerShell” by Adam Bertram(b|t|l|g).

Next, I knew that I did not want any jobs executing during this period. I decided that the following command would work just perfect.

Now that has completed, I wanted to ensure I had all of the logins and users just in case something went wrong. I did this because I was being paranoid with other commands I was going to use.

With this piece being done, I can go ahead and execute the copy command. Here, I was reminded that I am restoring databases from one system to another, all I need are the appropriate logins and then we can fix users later. I added this command next.

So far so good, we are chugging along. This is where we really need to understand our systems and how these dbatools functions work. I do not know all of the internals of each function. I mean, who would. People have contributed over 300 functions. All I know is that, we have some legacy databases in ReadOnly mode and those had to be included in the migration as well. After a few moments, I realized that there is a function to find those and all you have to do is pass a simple parameter.

Okay, great, we now know what databases are read-only and now we need to ensure that they are read-write so we can move them. To do that, I found that this command would work best.

Now that we have all the databases set to do a successful migration, I am able to generate my list and start the copy process. In this process, I want to update the database owner.

Okay, now that the copy process has completed, I still need to copy linked servers and a sql agent job.

Alright, everything I had for the migration has copied. I still have to stop the old engine from running. With that, I used the Stop-DbaSqlService function.

Remember how we copied the logins over in the beginning; we still have to repair the orphaned users. For that, we do the following.

Just about all done. We still have to set our databases to read only for the ones we made read-write. I used the same command but just use a different variable.

There you have it folks! Making use of multiple dbatools functions, we were able to successfully merge databases from one server to another, not have any hiccups, and successfully decommission 18 legacy systems. Here is the script in full.

Thank you to all of the contributors of dbatools! This migration was the easiest one yet.