DBArtisan is a product developed by IDERA. It is a multi-platform database administration tool that simplifies your day-to-day tasks. One of those tasks is ensuring that database schemas and data and properly copied from instance to instance.
Maybe you have a non-production sandbox for your developers where they can delete data and make schema changes. Maybe you have to ensure that the copy of your database on the load test server is up to date and matches the data in production. Maybe you need to copy your schema and data to a remote server. Whatever the need, DBArtisan has built-in functions that allow you to easily replicate database objects and data.
After selecting a connected datasource, Schema Migration is available under the UTILITIES menu.
The first step of the migration wizard allows you to create a new migration or to ‘replay’ a previous migration. Replaying is very useful when you are performing a repetitive task. You set up the migration once and you can run it again with only three mouse clicks. For this example, I will walk through creating a new migration. You will see in step 6 how I can save the migration and then call it back up here.
The next few wizard screens are simply selecting a source instance and database, and a target instance and database. The databases can have the same name, or different names.
Here I am selecting two different databases on the same server. I am doing this to make a backup copy of the GIM database before I run a destructive test on it.
After I have selected the source and target databases, I select the types of objects I want to migrate on the left. In this example, I have selected Tables. You can see that it will also migrate check constraints, foreign keys, indexes, primary keys, and unique keys. The number and types of objects listed in the left side of the screen depend on the database platform, but virtually all objects, including users, roles, logins, user datatypes, and so on are supported.
I have used the “Schemas” button above the object types to bring up a pop-up window where I selected the “dbo” schema. On the right, I have selected “Select All” to identify which specific objects I want to migrate. Again, the left side determines what types of objects I will migrate, and the right side is the list of specific objects I will migrate.
In step 5, I choose the options that will affect how the migration occurs. I can keep or change object owners, I can choose to drop objects in the target database before recreating them, I can select how data is migrated (INSERT statements, bulk copies, or other bulk copy utiliites, depending on database vendor). The “Example Preview” on the right shows the format of the script that will be generated by the migration wizard. It will change as you check and uncheck the options. One of the options available on the left is “Generate Script.” If I select this option, DBArtisan will generate a script that can be run against the target database, but it will not run the script. The script can be copied for later use or sent through your normal change control process.
Note that I have the ability here to save the options I have selected. If I run another new migration, I can simply bring up the options I have already used.
The next-to-last step in the wizard is a summary of what will be done. Notice that I have the abililty to save this entire migration. Should I decide to rerun it, or if it is a task I must run on a regular basis, I can simply select this .sav file when I start the migration wizard.
Lastly, the migration occurs. The time it takes to migrate depends on the number of objects moved, as well as the amount of data, the way in which the data is being copied, and network speed. The “Report” button will present me with an HTML report showing the steps taken and data copied.
And there it is! In a few simple wizard-driven steps, I’ve copied tables, indexes, constraints and other objects along with the data from one MS-SQL database to another.
When first using the data migration, start simple. Move a few tables using the default values. Learn the options available in step 5. After a few trial migrations, you should be ready to set up a migration that copies gigabytes of data and hundreds of objects ensuring that your development, qa, and production environments are all in sync.
You can use the migration feature within DBArtisan to move schemas and data between vendors. I’ve unloaded tables and data the Adventure Works database in MS-SQL and reloaded them in Oracle. Be aware that some objects, such as procedures, functions, and user datatypes, are specific to one vendor and may need some tweaking before they will import into another vendor’s platform.
If you are new to DBArtisan, you can find more information about it here: https://www.idera.com/dbartisan-database-administration-solution
On that page you can review summaries of the DBArtisan features and functions as well as download a free fully-function 14-day trial. You can also request a product demo if you need additional information about how DBArtisan can make your job simpler and faster. Happy migrating!