09Jul 2012 by dave No Comments

Copying indexes from Oracle to SQL Server

Oracle, Resources, SQL
I'm working with a client that has access to an Oracle 10g database containing transactional data that they copy to their new SQL Server 2012 as a data warehouse. (We recently migrated the straight from SQL 2000 into SQL 2012, but that's another story.) They already had scripts to copy the tables and data to a staging database, and then copy new records to their data warehouse. Queries were slow against this data, as no indexes were created on the target tables. I wanted to copy the Oracle indexes to SQL Server, but found no simple way to do this, so I created a script to do so. I used Toad for Data Analysts to connect to the Oracle database, which is a really fantastic program for working with Oracle.…
Read More
03Jul 2012 by dave No Comments

The great adventure: Migrating from SQL 2000 to 2012

Oracle, SQL
I worked with a client that migrated from SQL 2000 32 bit database straight up to SQL 2012 64 bit! Quite a challenge, and quite the journey over 12 years and 5 releases (2000,2005,2008,2008R2,2012) of SQL. Some of the complicating factors include: Backup and Restore It is not possible to restore a SQL 2000 backup into SQL 2012. The solution is install an intermediate SQL instance (2005,2008,2008 R2), restore to that instance, then detach and attach into the SQL 2012 instance. For smaller databases, I was able connect to the SQL 2000 database from my SQL 2012 instance and use 'Generate Scripts...' in the context menu to script the objects and data. DTS SQL 2000 DTS packages no longer supported. Found the solution on MSDN from Jyoti Grove's comment. This…
Read More