I asked this question to lot of DBA and most of their answer is that performance will be hindered as VMs are sharing resources. They like virtualizing lower environment (dev, test) SQL Server but definitely not on production environment. Then my question is why there is no performance issue when you have multiple SQL instances on one physical server or have multiple databases on one SQL instance? Aren't this case also sharing resources? To me, this is an issue on design, management and configuration on server but not on the technology itself. As long as you do it right, there should not be any major issue regardless of whether you virtualize the SQL Server or create multiple instances or multiple databases on single instance.
But why virtualization? Below are some reasons why I virtualized SQL Server in my environment.
Provisioning
A new server can be built in couple of minutes with the correct template. How fast can you build a physical server?
Flexibility
Server can be migrated from one host to other host transparent to user if there is resource constraint. Extra host can be added to provide more resources if needed. What do you need to do to scale up or scale out a physical environment?
Disaster Recovery
It gives you extra simpler option on DR. VMs can be continuously replicated to DR site. Failover and failback is easier and simpler compared to traditional log shipping method.
High Availability
You can use live migration to move VMs if there is hardware failure to minimize downtime.
Rollback
What happened if you patch your physical server on one nice day, reboot the server, it crashed and never able to boot again? What is the different if it is a VM and you have the snapshot created before you patch it? I think it will safe your life, time and your day will be still a nice day.
Consolidation
It is just a waste of resource if you are using 5% of CPU resource on your physical server. Why do you want to drive 10 mph with your Ferarri? Other than effective use of resources, you can also reduce hardware maintenance cost, power and cooling, create a greener environment by consolidating physical servers.
I think there is challenge in virtualizing SQL Server, the main one is to do it right to meet performance requirement of the applications and databases. However by looking at the benefit of virtualization, I think it is worth to do it. There is always challenge no matter what you do, so why not just face the challenge and do it? I will talk about how I successfully virtualized my most critical production SQL Server with minimum hiccup in my next post .
Tuesday, March 26, 2013
Thursday, March 14, 2013
SQL Server 2012 "Evaluation period has expired" Message After Upgrading to Licensed Edition
I have a old dev box that I run evaluation edition of SQL Server 2012 that I have decided to upgrade to licensed edition. The upgrade went successfully without any issue. However I got below error message after the upgrade when I tried to launch SQL Server Management Studio:
After some Googling or Binging, I found below KB on similar error.
http://support.microsoft.com/kb/971268
However it is for SQL 2008 evaluation edition. Since it is just a old dev box, why not just give it a shot to see what happen. So I just follow "Option 2" but with different path on the registry as this is SQL 2012. Below is the screen shot on what I changed. After that just rerun the upgrade.
Guess what, it works!
If you have similar problem, you might want to try the same thing but try at your own risk especially if it is on production server as this involved registry chance. But why do you run evaluation edition on production server? This supposed to be on SQL 2008 according to the KB, I am no sure why it is happening on my SQL 2012 instance, let me know if you know why.
After some Googling or Binging, I found below KB on similar error.
http://support.microsoft.com/kb/971268
However it is for SQL 2008 evaluation edition. Since it is just a old dev box, why not just give it a shot to see what happen. So I just follow "Option 2" but with different path on the registry as this is SQL 2012. Below is the screen shot on what I changed. After that just rerun the upgrade.
Guess what, it works!
If you have similar problem, you might want to try the same thing but try at your own risk especially if it is on production server as this involved registry chance. But why do you run evaluation edition on production server? This supposed to be on SQL 2008 according to the KB, I am no sure why it is happening on my SQL 2012 instance, let me know if you know why.
Wednesday, March 6, 2013
Database Migration
I have been thinking of what should be my first post and this immediately pop up as this is what I have been doing for the past 6 months.
We have projects on replacing all old servers in all our data centers with new servers and as such we need to move all databases hosted on the old server to new server with minimum impact and downtown to the users. As most mission critical databases contain financial information, we also need to ensure there is no data loss and also modification to the data in the databases adhere to SOX compliance requirements.
There are multiple ways to deal with database move, backup/restore, detach/attach and SSIS are among the most frequently used method by most DBA. However the challenge is that there are about 90 databases, size range from 5-20GB each, hosted on one SQL instances for example. Think about questions below:
1) How long do you think it needs to complete the move using above method?
2) What is the rollback plan if the databases don't work on new server, are you going to use the same method to move the databases back to old server?
3) What will be the total downtime especially with rollback?
The method that I used is actually to setup database mirroring on all databases. Old server is the principal and new server is the mirror. I normally set this up a week prior to the official move date. Once I setup mirroring, I will also start collecting performance baseline information on new server. This is to ensure the performance of the databases will not be impacted. All of these steps can be done anytime without any outage.
During the official switch date time, what need to be done is just failover the databases to mirror server. The outage is essentially the failover time. If thing went wrong, the rollback is just to failback to old server.
With this method, the overall outage that I have for the SQL instance above is less than 5 minutes. As it is high safety mirroring, we are confident on the integrity of the data moved to satisfy audit requirement.
I hope this post is useful and give you an extra option if you are working on database migration project. This might not suitable for all migration project, careful planning and testing should be perform prior to implement this. Please let me know if you use any other alternative method before that actually works quite well, I am interested to learn that. Thanks.
We have projects on replacing all old servers in all our data centers with new servers and as such we need to move all databases hosted on the old server to new server with minimum impact and downtown to the users. As most mission critical databases contain financial information, we also need to ensure there is no data loss and also modification to the data in the databases adhere to SOX compliance requirements.
There are multiple ways to deal with database move, backup/restore, detach/attach and SSIS are among the most frequently used method by most DBA. However the challenge is that there are about 90 databases, size range from 5-20GB each, hosted on one SQL instances for example. Think about questions below:
1) How long do you think it needs to complete the move using above method?
2) What is the rollback plan if the databases don't work on new server, are you going to use the same method to move the databases back to old server?
3) What will be the total downtime especially with rollback?
The method that I used is actually to setup database mirroring on all databases. Old server is the principal and new server is the mirror. I normally set this up a week prior to the official move date. Once I setup mirroring, I will also start collecting performance baseline information on new server. This is to ensure the performance of the databases will not be impacted. All of these steps can be done anytime without any outage.
During the official switch date time, what need to be done is just failover the databases to mirror server. The outage is essentially the failover time. If thing went wrong, the rollback is just to failback to old server.
With this method, the overall outage that I have for the SQL instance above is less than 5 minutes. As it is high safety mirroring, we are confident on the integrity of the data moved to satisfy audit requirement.
I hope this post is useful and give you an extra option if you are working on database migration project. This might not suitable for all migration project, careful planning and testing should be perform prior to implement this. Please let me know if you use any other alternative method before that actually works quite well, I am interested to learn that. Thanks.
Introduction
G'day mate, my name is BP or some people called me Boon, anyway it's just a natural key to me and there can be multiple natural keys depend on situation.
As this is the introduction part so I will mainly talk about myself. I am a SQL Server DBA at least for now. I used to be an application developer dealing with backend SQL Server, then for some reasons turn to do SQL Server development and afterward administration plus BI development.
I worked in different cities and countries, met lot of great people and organization. I won't be who I am today without them. So I think it might be time for me to give what I have learnt and knew back to the community and this is mainly this blog about. I hope you can learn something useful from here. Cheers.
As this is the introduction part so I will mainly talk about myself. I am a SQL Server DBA at least for now. I used to be an application developer dealing with backend SQL Server, then for some reasons turn to do SQL Server development and afterward administration plus BI development.
I worked in different cities and countries, met lot of great people and organization. I won't be who I am today without them. So I think it might be time for me to give what I have learnt and knew back to the community and this is mainly this blog about. I hope you can learn something useful from here. Cheers.
Subscribe to:
Posts (Atom)