July 29, 2015 at 10:00 am
Hello All,
We're in the process of moving our SQL server 2000 to SQL server 2014. I've created a new instance on the SQL server 2014 along with the linked server to SQL server 2000. It's going to be a slow transition from SQL server 2000 to SQL server 2014. I've few questions regarding the same:
1) I need to regularly insert/update the data in the new database on SQL server 2014 from the old (SQL server 2000) until we fully transition to the new server (SQL server 2014). Can somebody please share their experience/inputs? Will be really helpful to start with.
2) The next step is to encrypt the data (few columns) on the new sql server 2014 as and when data gets inserted. However, my priority remains the first step to get the insert working on the new SQL server.
Any help is greatly appreciated.
Thanks
July 30, 2015 at 3:56 am
You might be facing a bit of an issue here. I'm assuming you're planning to do log shipping after a restore to get the 2000 database to 2014? The problem here is, you can't restore a 2000 backup onto a 2014 instance. You'll have to go through a 2005, 2008 or 2012 instance first. This is not something I've done personally, but I suspect it's going to be quite difficult. Instead of a zero-downtime switch-over you might want to plan for one with down time so that you can do the necessary hop. How big is your database?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2015 at 9:38 pm
1) I need to regularly insert/update the data in the new database on SQL server 2014 from the old (SQL server 2000) until we fully transition to the new server (SQL server 2014). Can somebody please share their experience/inputs? Will be really helpful to start with.
There is nothing good about this. You're putting load on your old server, to duplicate data to the new server, and it will take ages for you to build all of the ETL processes in (not to mention the risks you have in doing so), but what would be the benefit? It doesn't confirm your applications are still able to operate on SQL 2014. You really need to either have a dedicated and separate test environment, possibly with occasional database refreshes, and/or replay queries.
I'd go back to the drawing board. The normal process is:
1) Business wants to upgrade.
2) You copy your production databases into another environment (e.g. Test), cleanse data off where possible (e.g. PII data, email addresses). Automate this as much as you can because you will likely need to refresh often.
3) Testing would occur there to determine most of the functionality you require is available and still working. Most of this will be user testing, with developers checking application logs hoping that they aren't swallowed. On the SQL side you can capture traces and look for errors.
4) Then you would repeat the process with your real production environment.
You can move databases-at-a-time over, but replicating row-level information is not something that is normally done in this way for these purposes.
2) The next step is to encrypt the data (few columns) on the new sql server 2014 as and when data gets inserted. However, my priority remains the first step to get the insert working on the new SQL server.
The way you've described it this would have to be an application side change (technically you may be able to alter the stored procedures to encrypt and decrypt the underlying data, but what's seamless to the application is also seamless to anyone else querying the data). The best you can do without application support in SQL 2014 is encrypt data at rest (e.g. so if someone steals your disks or backup drives then you're secure). But then key management must be taken and tested extremely seriously.
Otherwise, you can do something similar in SQL 2016 or Azure Database with AlwaysEncrypted. That would require more minor application-side changes (at least I think the .NET library in use, they haven't released full details).
--
I'm tired. I take no responsibility for mistakes in an informal conversation.
August 5, 2015 at 1:23 pm
There is not a direct upgrade path from 2000 to 2014, and yes it is a painful experience made even more complicated and painful if you have replication running.
I had to go from 2000 > 2005, and then was able to upgrade to others.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 12, 2015 at 11:48 pm
Cross-post from MSDN Forums:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 24, 2015 at 7:44 am
Grant Fritchey (7/30/2015)
You might be facing a bit of an issue here. I'm assuming you're planning to do log shipping after a restore to get the 2000 database to 2014? The problem here is, you can't restore a 2000 backup onto a 2014 instance. You'll have to go through a 2005, 2008 or 2012 instance first. This is not something I've done personally, but I suspect it's going to be quite difficult. Instead of a zero-downtime switch-over you might want to plan for one with down time so that you can do the necessary hop. How big is your database?
Going down this route, would it be necessary to purchase a license for SQL Server 2012 Standard / Enterprise for the sole purpose of migrating betwee 2000 and 2014 ?
For example, is it possible to restore from 2000 Standard / Enterprise Edition to 2012 Express Edition (assuming the database can fit within the 10 GB size limitation), and then backup / restore from 2012 Express to 2014 Standard / Enterprise?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply