May 13, 2015 at 1:11 am
We just migrated from sql 2000 to 2008. Okay so there are 2 computer servers. The one is running SQL Server 2008 and the other is SQL Server 2000. Let's call them DB1 and DB2. So Db1 (SQL Server 2008) is a production server. And everyday we need to backup DB1 server to DB2 server (SQL Server 2000). We have tried SSIS in VS 2008. It works great. However, any changes that will occur in the future pertaining to the database structure of Db1 (SQL Server 2008) will not be reflected in the mirrored database which is DB2 (SQL Server 2000) unless reconfigured manually. For example if in Db1 (SQL Server 2008), an admin added a table or add/delete columns in a certain table, the SSIS package will get an error thus we need to rebuild or reconfigure manually again in VS 2008. We don't want to do that from time to time, right?
May 13, 2015 at 2:45 am
If you're going to maintain the 2000 system for the long term, then I'd treat it like it's a full part of your development and deployment system. Changes to your 2008 systems should go through a source control management system and those changes that can be should then be deployed directly to the 2000 system. It'll have to be either an automated process like this, or a manual process like you have currently.
Otherwise, you can look to set up mirroring to the 2000 system. That won't be perfect, but can work.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 13, 2015 at 2:55 am
Grant Fritchey (5/13/2015)
Otherwise, you can look to set up mirroring to the 2000 system. That won't be perfect, but can work.
Database mirroring?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2015 at 2:56 am
Grant Fritchey (5/13/2015)
If you're going to maintain the 2000 system for the long term, then I'd treat it like it's a full part of your development and deployment system. Changes to your 2008 systems should go through a source control management system and those changes that can be should then be deployed directly to the 2000 system. It'll have to be either an automated process like this, or a manual process like you have currently.Otherwise, you can look to set up mirroring to the 2000 system. That won't be perfect, but can work.
Hi Sir. I'm sorry but I didn't quite understand what you just said. Yes the 2000 system will also be a long term. The 2008 database will be updated everyday so what will happen is that there's a scheduled task that will run a certain SSIS/package to copy/backup it to sql 2000.
May 13, 2015 at 2:58 am
I think what he meant is the DTS mirroring for SQL 2000 only. Well today, wel'll be using sql 2008. So we need a certain script/ssis/package that can mirror sql 2008 to 2000.
May 13, 2015 at 2:58 am
kanuto.tapon (5/13/2015)
The 2008 database will be updated everyday so what will happen is that there's a scheduled task that will run a certain SSIS/package to copy/backup it to sql 2000.
You can't restore the backup of a SQL 2008 database to SQL 2000. Replication might work, though it'll likely be a challenge. Otherwise as Grant suggested, make sure schema changes get deployed to both DBs and sync the data with SSIS on whatever schedule you need.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2015 at 3:04 am
GilaMonster (5/13/2015)
kanuto.tapon (5/13/2015)
The 2008 database will be updated everyday so what will happen is that there's a scheduled task that will run a certain SSIS/package to copy/backup it to sql 2000.You can't restore the backup of a SQL 2008 database to SQL 2000. Replication might work, though it'll likely be a challenge. Otherwise as Grant suggested, make sure schema changes get deployed to both DBs and sync the data with SSIS on whatever schedule you need.
Summary is: We need to reconfigure/rebuild the package manually everytime there's a schema changes in the DB1 main source (sql 2008)?
May 13, 2015 at 3:23 am
kanuto.tapon (5/13/2015)
GilaMonster (5/13/2015)
kanuto.tapon (5/13/2015)
The 2008 database will be updated everyday so what will happen is that there's a scheduled task that will run a certain SSIS/package to copy/backup it to sql 2000.You can't restore the backup of a SQL 2008 database to SQL 2000. Replication might work, though it'll likely be a challenge. Otherwise as Grant suggested, make sure schema changes get deployed to both DBs and sync the data with SSIS on whatever schedule you need.
Summary is: We need to reconfigure/rebuild the package manually everytime there's a schema changes in the DB1 main source (sql 2008)?
Yes.
If nothing else, it can act as motivation to get the 2000 system retired.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply