July 29, 2015 at 12:12 pm
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 29, 2015 at 12:23 pm
Well a few questions,
1)Are you going to be transitioning entire applications at one time or will applications be running in parallel on both platforms?
2)How much data are you talking about transferring, either at one time or on a daily basis?
3)If you are planning to transfer data daily what options do you have for trapping for new/updated records on the legacy platform and are there any restrictions based on the data like identity columns or enterprise id's generated by the application that can't be migrated?
4)Do your applications have any built in data migration tools?
Linked servers aren't a terrible option if you're talking small data sets(10000's of records) and they're likely much easier to set up and use than an ETL tool of some kind.
July 29, 2015 at 12:48 pm
1)Are you going to be transitioning entire applications at one time or will applications be running in parallel on both platforms?
For now the applications will be running from the old server but eventually when the new server will have all the data (using Linked Servers) from the old server. I need to create a script to insert/update the new instance on the new server from the old SQL 2000 server using Linked Servers.
2)How much data are you talking about transferring, either at one time or on a daily basis?
I'm not sure about the size of data. Current database size is 2GB
3)If you are planning to transfer data daily what options do you have for trapping for new/updated records on the legacy platform and are there any restrictions based on the data like identity columns or enterprise id's generated by the application that can't be migrated?
I'm planning the trap new/inserted records by recid.
4)Do your applications have any built in data migration tools?
Not that I can think of.
Hope this helps. I appreciate your help.
July 29, 2015 at 1:02 pm
2GB is pretty tiny over all, you can probably just copy the whole thing over the linked server if it's split into small tables. It should be pretty straight forward to write a query to generate a bunch of INSERT INTO <2014 table> SELECT * FROM <linked server> statements and pull the data over.
Is there a business reason you can't do the entire migration at once and just cut over to the 2014 instance?
July 29, 2015 at 1:43 pm
ZZartin- I appreciate your response. I've 25 tables in the database that has to be moved over to the new server. I already have the database restored on the new server. The suggestion you made using the insert statement makes sense. I'm going to follow that. Just a quick question.
1) Do I have to run the insert statement for each table? Pardon my question. I haven't done this before. Can you help me with the script to do this?
2) Also, how do I insert/update if the new record in the new server if new record is added/updated in the Old server?
Thanks again for your help.
July 29, 2015 at 2:25 pm
1) if you just want something simple to get the data into you 2014 instance something like should generate the commands you need(this is assuming the database structures are exactly the same),
SELECT 'TRUNCATE TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + '; INSERT INTO ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' SELECT * FROM <INSERT YOUR LINKED SERVER NAME HERE>.<Database>.' + TABLE_SCHEMA + '.' + TABLE_NAME + ';' FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Replace the linked server and database with what you need.
2) That was my other question, unless there's a reason you need to do updates your database is small enough you can just reload everything and not worry about it.
July 29, 2015 at 2:39 pm
Thank you for your response. Below is the answer to your second question:
2) That was my other question, unless there's a reason you need to do updates your database is small enough you can just reload everything and not worry about it.
I need to automate the insert/update to the new server for now. Once we have everything in place we will be switching to the new server. This is just a precautionary step before switching entirely to the new server. Hope this helps.
Also, in regards to the script, do I need to run the script for each table schema? I mean I've 25 tables in the database. Should I run this script 25 times for each table?
Thanks, again.
July 29, 2015 at 2:56 pm
SSRS Newbie (7/29/2015)
Thank you for your response. Below is the answer to your second question:2) That was my other question, unless there's a reason you need to do updates your database is small enough you can just reload everything and not worry about it.
I need to automate the insert/update to the new server for now. Once we have everything in place we will be switching to the new server. This is just a precautionary step before switching entirely to the new server. Hope this helps.
Also, in regards to the script, do I need to run the script for each table schema? I mean I've 25 tables in the database. Should I run this script 25 times for each table?
Thanks, again.
If you have 25 tables in your db you can run that script once and your result set should be 25 lines, you could just copy and paste those into a query window or put them in a stored procedure or job step and run them.
Keep in mind that is clearly the tables in the target database.
If you just want to keep your 2014 up to date with your 2000 environment code you can run the code from the script I gave you however often you want. However if you're making changes in both environments and need to keep them synched(2000 up to 2014 and 2014 down to 2000) that would be a little more involved.
July 30, 2015 at 8:46 am
ZZartin- I ran below script on my new SQL server using LinkedServers. The tables did not update. I mean I checked few tables. The number of rows in the original database are more than the number of rows in the new database. Any suggestions/help is greatly appreciated.
SELECT 'TRUNCATE TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + '; INSERT INTO ' + TABLE_SCHEMA + '.' + TABLE_NAME +
' SELECT * FROM <TestServer>.<TestDB>.' + TABLE_SCHEMA + '.' + TABLE_NAME + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
July 30, 2015 at 9:28 am
Need help guys. Thanks
July 30, 2015 at 9:31 am
SSRS Newbie (7/29/2015)
ZZartin- I appreciate your response. I've 25 tables in the database that has to be moved over to the new server. I already have the database restored on the new server. The suggestion you made using the insert statement makes sense. I'm going to follow that. Just a quick question.1) Do I have to run the insert statement for each table? Pardon my question. I haven't done this before. Can you help me with the script to do this?
2) Also, how do I insert/update if the new record in the new server if new record is added/updated in the Old server?
Thanks again for your help.
You need to allow for identity columns with this script. Obviously he might not have any identity columns, but if he does, your script will want to do the explicit column listings, as well as setting identity_insert on and off, etc.
Just a suggestion, I'm a big fan of generating sql like this so not trying to be picky LOL
July 30, 2015 at 9:41 am
patrickmcginnis59 10839- Can you help me with the script? I appreciate your response. Thanks.
July 30, 2015 at 9:46 am
SSRS Newbie (7/30/2015)
ZZartin- I ran below script on my new SQL server using LinkedServers. The tables did not update. I mean I checked few tables. The number of rows in the original database are more than the number of rows in the new database. Any suggestions/help is greatly appreciated.
SELECT 'TRUNCATE TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + '; INSERT INTO ' + TABLE_SCHEMA + '.' + TABLE_NAME +' SELECT * FROM <TestServer>.<TestDB>.' + TABLE_SCHEMA + '.' + TABLE_NAME + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
You look like you are inserting from a server called "testserver". I sure hope the other server that you are truncating on and inserting to isn't called "productionserver"!
If you are SURE that you are selecting from the correct server, and inserting into the correct server, you could post any resulting message from trying the above on a table that it doesn't seem to work on.
As I mentioned, "identity" columns will prevent this code from running, "computed columns" will too, and also foreign key constraints will prevent the "truncate" from working. But you should be seeing some error messages that highlight these failures in any case.
Be careful that you have your truncates and inserts working on the server you actually want them to work on!
July 30, 2015 at 9:48 am
Hmm... well if you run just the SELECT FROM linked server part of the query from the script, what are the results? Or are you getting an error message when you run the script, you should be if there are computed or identity columns on the tables as you wouldn't be able to insert into them?
July 30, 2015 at 11:18 am
ZZartin & patrickmcginnis59 10839,
Here is the result after running the script. I'm running the script on the new sql server 2014 under the destination database. Also the difference between the number of rows in actual sql server (old) and new is close to 20000. It only shows 24 rows affected.
Below are the results:
(No column name)
TRUNCATE TABLE dbo.Order__V2; INSERT INTO dbo.Order__V2 SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Order__V2;
TRUNCATE TABLE dbo.Exception_Order; INSERT INTO dbo.Exception_Order SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Exception_Order;
TRUNCATE TABLE dbo.order_tab; INSERT INTO dbo.order_tab SELECT * FROM [TestServer].[TestDB].[dbo]dbo.order_tab;
TRUNCATE TABLE dbo.OLD_Order_Process; INSERT INTO dbo.OLD_Order_Process SELECT * FROM [TestServer].[TestDB].[dbo]dbo.OLD_Order_Process;
TRUNCATE TABLE dbo.Order_Count; INSERT INTO dbo.Order_Count SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Order_Count;
TRUNCATE TABLE dbo.OLD_Stage; INSERT INTO dbo.OLD_Stage SELECT * FROM [TestServer].[TestDB].[dbo]dbo.OLD_Stage;
TRUNCATE TABLE dbo.Process; INSERT INTO dbo.Process SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Process;
TRUNCATE TABLE dbo.Staging; INSERT INTO dbo.Staging SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Staging;
TRUNCATE TABLE dbo.Hold; INSERT INTO dbo.Hold SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Hold;
TRUNCATE TABLE dbo.Process2; INSERT INTO dbo.Process2 SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Process2;
TRUNCATE TABLE dbo.Page_count; INSERT INTO dbo.Page_count SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Page_count;
TRUNCATE TABLE dbo.Order_status; INSERT INTO dbo.Order_status SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Order_status;
TRUNCATE TABLE dbo.Temp_status; INSERT INTO dbo.Temp_status SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Temp_status;
TRUNCATE TABLE dbo.LG_Status; INSERT INTO dbo.LG_Status SELECT * FROM [TestServer].[TestDB].[dbo]dbo.LG_Status;
TRUNCATE TABLE dbo.Delete_order; INSERT INTO dbo.Delete_order SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Delete_order;
TRUNCATE TABLE dbo.Order_Version2; INSERT INTO dbo.Order_Version2 SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Order_Version2;
TRUNCATE TABLE dbo.Multi_Order; INSERT INTO dbo.Multi_Order SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Multi_Order;
TRUNCATE TABLE dbo.Order_Local; INSERT INTO dbo.Order_Local SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Order_Local;
TRUNCATE TABLE dbo.Order_PP; INSERT INTO dbo.Order_PP SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Order_PP;
TRUNCATE TABLE dbo.Status2; INSERT INTO dbo.Status2 SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Status2;
TRUNCATE TABLE dboOrder_Count2; INSERT INTO dboOrder_Count2 SELECT * FROM [TestServer].[TestDB].[dbo]dboOrder_Count2;
TRUNCATE TABLE dbo.File; INSERT INTO dbo.File SELECT * FROM [TestServer].[TestDB].[dbo]dbo.File;
TRUNCATE TABLE dbo.Create; INSERT INTO dbo.Create SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Create;
TRUNCATE TABLE dbo.Order_P2; INSERT INTO dbo.Order_P2 SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Order_P2;
TRUNCATE TABLE dbo.dtproperties; INSERT INTO dbo.dtproperties SELECT * FROM [TestServer].[TestDB].[dbo]dbo.dtproperties;
TRUNCATE TABLE dbo.Hold; INSERT INTO dbo.Hold SELECT * FROM [TestServer].[TestDB].[dbo]dbo.Hold;
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply