August 19, 2009 at 9:32 am
I am a web developer. My company's website is basically an online version of our product catalogs. We display detailed product specifications on our products pages that are pulled from SQL Server 2005 databases.
Currently we do not have Integration Services installed on SQL Server and we do not have a SQL Server DBA. The data we eventually store in SQL Server 2005 comes from Adobe InDesign files. We copy the data from InDesign into Excel 2003, then we remote desktop into SQL Server and upload the Excel file to the Server. After that we use the SQL Server Import/Export Wizard to copy the data from the Excel worksheets into the database tables.
Somtimes we have problems with the formatting of our data changing when we use the wizard. We also have problems keeping our development SQL Server instance sychronized with our production SQL Server. I am looking for advice on steps that I could follow to make the transfer of data easier and the synchronization between the development and SQL Server instances easier.
Thanks.
August 19, 2009 at 2:03 pm
Creating a SSIS project in BIDS instead of using the Wizard could help with building out a more verbose ETL process. You can use SSIS to assist with data validations, formatting, etc.
As far as keeping your 2 environments in sync, what do you want to do? Is your development environment a copy of production, real-time?
August 19, 2009 at 2:11 pm
I would like to be able to update the development SQL Server periodically from the Production box so that in development we would have the most current identity values for tables (such as a ProductID as a primary key in a Products table for example).
In our environment, the production box is isolated from our internal network, and right now Integration Services on not installed on either the Production or Development Server (actually there are future plans to configure a Test SQL Box on the same domain as Production, but development will always be disconnected).
But yes, the Production and Development SQL Servers are mirror images of one another (or at least should be, right now there are discrepancies because we had to do quick fixes and updates on Production that aren't reflected in development).
August 19, 2009 at 2:19 pm
SSIS could definitely be used to smooth out the import process and make it more self-correcting.
On integrating the dev and production servers, the easiest way to do that is to restore a backup from production into dev. Just make sure any work that's ongoing on the dev server has scripts saved in source control so that you can move those back into the dev database after the restore.
On the SSIS bit, you could almost certainly hire a contractor to set that up for you. If you aren't familiar with SSIS, that'll almost certainly be the most efficient and effective way to get what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 20, 2009 at 9:22 am
As Gus stated, backup and restore would be the easiest. The catch would be if you only want the reference data moved into your development environment and you have transactional data or test cases that only live in development.
Getting SSIS installed on your development box shoudn't be a big deal but your development box will need to be able to find the production box on the network to pull data.
Another option would be to restore your production backup to a new database on your development instance and then use t-sql to move over the data that you want to keep in sync. You could then delete the copy of the production DB if needed.
August 20, 2009 at 7:07 pm
If the production server has a large amount of data on it, backup/restore probably isn't the most effective method. If a SAN is involved, it sometimes comes with or you can purchase an addition chunk of software that will "snapshot" a near tera-byte of data in just seconds, on demand, with no production downtime or slowdowns.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply