Hello guys, recently, I have to work on a high profile project which involves migrating a live database from one server to another server as part of their data center exit program. This was one of the critical projects to pull off because we can't afford any mishap or data loss or production outage. There are a lot of things that I learned and would like to share with you guys. All these lessons not just apply to migrate databases like Microsoft SQL Server, Oracle, or MySQL database from one server to another but to any production process, you are migrating from another server. These are the things we learn from experience but as I have said in the past, you can only learn a few things
This means, we first had to create a DNS alias and then changed our application to use that alias and then asked all client applications to use that alias. This whole process took 2 months and derailed the migration plan.
So, the lesson learned is always to use an alias to connect to the database. But, if you are new to SQL world, it's better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.
Schema and access can be verified before migration but for data, you must ensure that you copy the latest database to the new server before migration and you do so by shutting down your system.
This will allow you not to lose any data which is inserted while you are migrating.
That's why you should prepare an extensive list of post-deployment checks for your database migration.
Some of these checks can include:
1) The DB is accessible using both hostname and alias
2) There is no error in log files
3) Queries are running fine
4) There is no long-running queries or database deadlock
5) Ask clients to test their application etc.
If you don't enable log tracking you may potentially leave behind those unknown connections and clients, which will only surface on the next business day of your database migration.
This is something DBAs generally do but you make sure you have that in your list of things like a step in your migration runbook.
If you remember, firewall rules are applied to IP addresses and not on the alias.
Unfortunately, we caught unguarded against this as there was a client who is coming from the firewall. They diligently changed the IP address to point to a new database but forgot about the firewall, which means they won't able to connect to the database until firewall rules are amended. You surely want to avoid such kind of mistakes, they not only look bad but also add a lot of pressure on your actual plan.
That's all about some of the important things to remember while migrating your database from one server to another. These tips will help to avoid some of the costly mistakes many DBAs and programmers make while doing database migration.
Further Learning
The Complete SQL Bootcamp
Introduction to SQL
SQL for Newbs: Data Analysis for Beginners
SQL Puzzles and Answers, the 2nd Edition
Other related SQL queries, Interview questions, and articles:
P.S. - If you are interested in learning Database and SQL and looking for some free resources to start your journey, then you can also take a look at this list of Free SQL Courses for Beginners to kick-start your learning.
Database Server Migration - 10 Things to keep in mind
Without wasting any more of your time, here is my list of tips that you should keep in mind while dealing with databases. They will help you during database server migration, I mean, migrating your production database from one server to another.1. Always use an alias to connect to Database
One of the main problems we face while migrating our database from one server to another was directly using the hostname and IP address to connect to the database rather than a DNS alias. Actually, there was no DNS alias setup and that's why hostname was distributed to all the client systems.This means, we first had to create a DNS alias and then changed our application to use that alias and then asked all client applications to use that alias. This whole process took 2 months and derailed the migration plan.
So, the lesson learned is always to use an alias to connect to the database. But, if you are new to SQL world, it's better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.
2.Take a backup of database by shutting down your system
If you are using an alias to move to a new DB server then you must remember that the Database on a new server must be in the same state as an old server are, not only with respect to the schema, access, user, object but also data.Schema and access can be verified before migration but for data, you must ensure that you copy the latest database to the new server before migration and you do so by shutting down your system.
This will allow you not to lose any data which is inserted while you are migrating.
3. Switch the alias to migration data
This is probably the easiest step on migration date, just change the DNS alias to point to the new database server but you might need a network resource to do this job. We ran with access issues as no one in our team had access to change the production database alias, thankfully we had booked network resources in advance which helped with pointing alias to the new database server.4. Always have a rollback plan
I don't need to say why things can go wrong and if you don't have a rollback plan then you will be stuck and may incur downtime. Another important thing to remember is that just having a rollback plan is not enough, you should also understand and test it thoroughly in a Test environment before doing migration on production.5. Must do post-implementation checks
Unless you do your post-implementation checks or testing, you will never know if your release is succeeded or not. It's an important and mandatory part of any deployment like binary, configuration, database stored procedures or whatsoever.That's why you should prepare an extensive list of post-deployment checks for your database migration.
Some of these checks can include:
1) The DB is accessible using both hostname and alias
2) There is no error in log files
3) Queries are running fine
4) There is no long-running queries or database deadlock
5) Ask clients to test their application etc.
6. Beware of any network gotchas
Yes, they happen so be prepared for that. Make sure you have network services contact details and someone already informed and lined for support with respect to any network issues.
7. Beware if you are also upgrading your database version like SQL Server 2016 from SQL Server 2014
There can be an old database which is dependent upon those versions. Ideally, you should avoid migration and upgrade at the same time, it will just increase the risk, but if you have to then make sure you have properly tested the new version in your test and integration environment.8. Prepare a runbook
A nice and clean step by step guide helps a lot during the actual migration. Make sure, the steps are clearly articulated and it's clear who is doing it and he is aware of that. A dry run of actual migration following the runbook is the best way to ensure that your runbook is complete.9. Book your Resources in Advance
If you are dependent upon other people for your release or database migration, make sure you book them in advance like DBAs, UNIX system admins, support guys, network engineers, etc. It's also a good idea to involve them early in the planning and dry run phase so that they will be aware of their job. Sometimes, their input also helps a lot to find any gaps in planning.10. Enable log tracking on database
This will help you to find who is connecting to the database, you will be surprised to see much-unexpected connections. You can now chase them to move off to alias if they are not using already.If you don't enable log tracking you may potentially leave behind those unknown connections and clients, which will only surface on the next business day of your database migration.
This is something DBAs generally do but you make sure you have that in your list of things like a step in your migration runbook.
11. Put the old database in read-only mode
This can act as a safety net, this means those systems can still work albeit with stale data. You can also then find those connections which are not using an alias by running tracing on the old database and giving them last chance to move off to the new database.12. Remember to change firewall rules
When you move your database from one server to another then the IP address will change and if there is a client which is connecting to your database across firewall using IP address then they will not only have to change the IP address but also ask Network team to change the firewall rules to include new IP addresses.If you remember, firewall rules are applied to IP addresses and not on the alias.
Unfortunately, we caught unguarded against this as there was a client who is coming from the firewall. They diligently changed the IP address to point to a new database but forgot about the firewall, which means they won't able to connect to the database until firewall rules are amended. You surely want to avoid such kind of mistakes, they not only look bad but also add a lot of pressure on your actual plan.
That's all about some of the important things to remember while migrating your database from one server to another. These tips will help to avoid some of the costly mistakes many DBAs and programmers make while doing database migration.
Further Learning
The Complete SQL Bootcamp
Introduction to SQL
SQL for Newbs: Data Analysis for Beginners
SQL Puzzles and Answers, the 2nd Edition
Other related SQL queries, Interview questions, and articles:
- Write a SQL query to find all table names on a database in MySQL (solution)
- How to join three tables in one single SQL query (solution)
- 5 Courses to learn Database and SQL Better (courses)
- Write a SQL query to copy or backup a table in MySQL (solution)
- How do you find the duplicate rows in a table on a database? (solution)
- 5 Advanced SQL books to level up your SQL skills (books)
- 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
- The real difference between WHERE and HAVING clause in SQL? (answer)
- Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
- How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
- Top 5 Websites to learn SQL online for FREE? (resource)
- What is the difference between UNION and UNION ALL in SQL? (answer)
- Difference between Self and Equi Join in SQL? (answer)
- 4 Free Books to learn Microsoft SQL Server database (books)
- What is the difference between View and Materialized View in Database? (answer)
- Difference between clustered and non-clustered index in SQL? (answer)
- Difference between Primary and Candidate key in table? (answer)
- Top 5 Websites to learn SQL online for FREE? (websites)
- 5 Free Courses to learn Database and SQL (free courses)
- 5 Free Courses to learn Oracle and SQL Server? (courses)
- Top 5 Courses to learn MySQL Database for Beginners (Courses)
- 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
- Difference between Unique and Primary key in table? (answer)
- Top 5 Courses to learn PostgreSQL in-depth (courses)
- 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
- Difference between Primary and Foreign key in table? (answer)
P.S. - If you are interested in learning Database and SQL and looking for some free resources to start your journey, then you can also take a look at this list of Free SQL Courses for Beginners to kick-start your learning.
No comments:
Post a Comment