Database Server Migration – 10 Issues to remember
With out losing any extra of your time, right here is my checklist of suggestions that you must take note whereas coping with databases. They may assist you throughout database server migration, I imply, migrating your manufacturing database from one server to a different.
1. At all times use an alias to hook up with Database
One of many predominant issues we face whereas migrating our database from one server to a different was straight utilizing the hostname and IP deal with to hook up with the database moderately than a DNS alias. Truly, there was no DNS alias setup and that is why hostname was distributed to all of the consumer techniques.
This implies, we first needed to create a DNS alias after which modified our utility to make use of that alias after which requested all consumer functions to make use of that alias. This entire course of took 2 months and derailed the migration plan.
So, the lesson realized is all the time to make use of an alias to hook up with the database. However, if you’re new to SQL world, it is higher to begin with a complete SQL course like The Full SQL Bootcamp course by Jose Portilla on Udemy. That can assist you to be taught SQL higher and faster, and these sorts of articles can even make extra sense upon getting some SQL data below your belt.
2.Take a backup of database by shutting down your system
If you’re utilizing an alias to maneuver to a brand new DB server then you could do not forget that the Database on a brand new server should be in the identical state as an outdated server are, not solely with respect to the schema, entry, consumer, object but additionally knowledge.
Schema and entry will be verified earlier than migration however for knowledge, you could be sure that you copy the newest database to the brand new server earlier than migration and also you achieve this by shutting down your system.
This may permit you to not lose any knowledge which is inserted if you are migrating.
3. Change the alias to migration knowledge
That is most likely the best step on migration date, simply change the DNS alias to level to the brand new database server however you would possibly want a community useful resource to do that job. We ran with entry points as nobody in our crew had entry to alter the manufacturing database alias, fortunately we had booked community assets upfront which helped with pointing alias to the brand new database server.
4. At all times have a rollback plan
I needn’t say why issues can go mistaken and if you do not have a rollback plan then you can be caught and should incur downtime. One other necessary factor to recollect is that simply having a rollback plan shouldn’t be sufficient, you must also perceive and check it totally in a Take a look at atmosphere earlier than doing migration on manufacturing.
5. Should do post-implementation checks
Until you do your post-implementation checks or testing, you’ll by no means know in case your launch is succeeded or not. It is an necessary and obligatory a part of any deployment like binary, configuration, database saved procedures or in any respect.
That is why you must put together an intensive checklist of post-deployment checks on your database migration.
A few of these checks can embody:
1) The DB is accessible utilizing each hostname and alias
2) There isn’t a error in log information
3) Queries are working wonderful
4) There isn’t a long-running queries or database impasse
5) Ask purchasers to check their utility and so on.
6. Watch out for any community gotchas
Sure, they occur so be ready for that. Ensure you have community providers contact particulars and somebody already knowledgeable and lined for help with respect to any community points.
7. Beware if you’re additionally upgrading your database model like SQL Server 2016 from SQL Server 2014
There will be an outdated database which relies upon these variations. Ideally, you must keep away from migration and improve on the similar time, it is going to simply improve the chance, but when you must then be sure you have correctly examined the brand new model in your check and integration atmosphere.
8. Put together a runbook
A pleasant and clear step-by-step information helps loads in the course of the precise migration. Be certain that, the steps are clearly articulated and it is clear who’s doing it and he’s conscious of that. A dry run of precise migration following the runbook is one of the simplest ways to make sure that your runbook is full.
9. E-book your Assets in Advance
If you’re dependent upon different individuals on your launch or database migration, be sure you guide them upfront like DBAs, UNIX system admins, help guys, community engineers, and so on. It is also a good suggestion to contain them early within the planning and dry run section in order that they are going to be conscious of their job. Typically, their enter additionally helps loads to seek out any gaps in planning.
10. Allow log monitoring on database
This may assist you to seek out who’s connecting to the database, you can be shocked to see much-unexpected connections. Now you can chase them to maneuver off to alias if they aren’t utilizing already.
When you do not allow log monitoring you might doubtlessly depart behind these unknown connections and purchasers, which can solely floor on the subsequent enterprise day of your database migration.
That is one thing DBAs typically do however you be sure you have that in your checklist of issues like a step in your migration runbook.
11. Put the outdated database in read-only mode
This will act as a security web, this implies these techniques can nonetheless work albeit with stale knowledge. You may also then discover these connections which aren’t utilizing an alias by working tracing on the outdated database and giving them final probability to maneuver off to the brand new database.
12. Keep in mind to alter firewall guidelines
Once you transfer your database from one server to a different then the IP deal with will change and if there’s a consumer which is connecting to your database throughout firewall utilizing IP deal with then they won’t solely have to alter the IP deal with but additionally ask Community crew to alter the firewall guidelines to incorporate new IP addresses.
When you bear in mind, firewall guidelines are utilized to IP addresses and never on the alias.
Sadly, we caught unguarded towards this as there was a consumer who’s coming from the firewall. They diligently modified the IP deal with to level to a brand new database however forgot in regards to the firewall, which implies they will not ready to hook up with the database till firewall guidelines are amended. You absolutely wish to keep away from such sort of errors, they not solely look unhealthy but additionally add a variety of stress in your precise plan.
That is all about among the necessary issues to recollect whereas migrating your database from one server to a different. The following tips will assist to keep away from among the pricey errors many DBAs and programmers make whereas doing database migration.
Additional Studying
The Full SQL Bootcamp
Introduction to SQL
SQL for Newbs: Information Evaluation for Inexperienced persons
SQL Puzzles and Solutions, the 2nd Version
Different associated SQL queries, Interview questions, and articles:
- Write a SQL question to seek out all desk names on a database in MySQL (answer)
- The way to be part of three tables in a single single SQL question (answer)
- 5 Programs to be taught Database and SQL Higher (programs)
- Write a SQL question to repeat or backup a desk in MySQL (answer)
- How do you discover the duplicate rows in a desk on a database? (answer)
- 5 Superior SQL books to stage up your SQL expertise (books)
- 5 Programs to be taught Oracle and Microsoft SQL Server database (programs)
- The true distinction between WHERE and HAVING clause in SQL? (reply)
- Prime 5 Programs to be taught Microsoft SQL Server in-depth (programs)
- The way to migrate SQL queries from Oracle to SQL Server 2008? (reply)
- Prime 5 Web sites to be taught SQL on-line for FREE? (useful resource)
- What’s the distinction between UNION and UNION ALL in SQL? (reply)
- Distinction between Self and Equi Take part SQL? (reply)
- 4 Free Books to be taught Microsoft SQL Server database (books)
- What’s the distinction between View and Materialized View in Database? (reply)
- Distinction between clustered and non-clustered index in SQL? (reply)
- Distinction between Main and Candidate key in desk? (reply)
- Prime 5 Web sites to be taught SQL on-line for FREE? (web sites)
- 5 Free Programs to be taught Database and SQL (free programs)
- 5 Free Programs to be taught Oracle and SQL Server? (programs)
- Prime 5 Programs to be taught MySQL Database for Inexperienced persons (Programs)
- 5 Free Programs to be taught T-SQL and SQL Server for Inexperienced persons (Programs)
- Distinction between Distinctive and Main key in desk? (reply)
- Prime 5 Programs to be taught PostgreSQL in-depth (programs)
- 5 Free Programs to be taught T-SQL and SQL Server for Inexperienced persons (Programs)
- Distinction between Main and Overseas key in desk? (reply)
Thanks for studying this text to this point. When you discover this text helpful then please share it with your pals and colleagues. In case you have any questions or suggestions about your Database server migration then be at liberty to ask.
P.S. – If you’re all in favour of studying Database and SQL and in search of some free assets to begin your journey, then you may also check out this checklist of Free SQL Programs for Inexperienced persons to kick-start your studying.
