I'm working towards migrating an IBM DB2 database to SQL Server, however the DB2 database sits in a Data Warehouse environment.

As I am very new to Data Warehousing, DB2 and DB Migration between different types of databases, I would like to ask about how to best approach this and understand what components are involved (listing or linking elsewhere is fine - happy to read a bunch of things or learn something new).

The context:

  • The ETL tool in use, and to be kept in use, is Informatica
  • The only application that queries the DB is Cognos
  • There are 4 DB2 DBs in use on 2 (non-windows) hosts, one of these is 'H1' which has a 'DB2-DW-PROD' DB used as part of the Data Warehouse
  • I am interested in migrating the 'DB2-DW-PROD' DB to some space I have on an existing (Windows) SQL Server host, 'H2' under the name 'SQL-DW-PROD'. This host currently has several DBs on it for other purposes (in one instance) and can have SSRS/SSIS or other services/resources installed or configured if needed.
  • I would like to clean up the poorly maintained DB a little bit during the migration, if possible, based off what has not been used/updated in the past couple of years.

What I would like to know, directly or indirectly:

  • Should I create a new instance on H2 or is a single new database enough?
  • Does the new instance/DB need particular resources? If so, where from?
  • Should I use a particular Microsoft tool or use some type of import/export to get the data I need? (Is Informatica involved in this process?)
  • Someone mentioned 'metadata tables' for Informatica and/or Cognos, should I be wary of something?
  • Another mentioned maintaining sequence values -How is this best achieved; is it needed?
  • Any changes on the Informatica side are not my personal concern but the work of another, as such, Is there anything I should provide this person (apart from the address of the new db)?

I'm very new in this field, so anything dumbed down is greatly appreciated. That being said, anything complicated but necessary or good to know is as well. It's a steep learning curve for me but I'm kind of stumped right now and need a hand.

I have no control over the process - Informatica and Cognos must be kept as they are. Also, at this stage, I am not able to view the DB2 DB but I know it is not too large. At the very, very most, assume it's 1 TB for the purpose of this question.

At the moment I am simply trying to understand the process I will follow and what I will need to look out for when the time comes.

EDIT: Have received plenty of feedback on the instance vs database part of the question which I greatly appreciate. However, I am still confused as to how I should approach migrating the data accross. I.e. 'how, or what should I use, to migrate such that I have everything I need to support Informatica and cognos?'

The most pressing issue is that I don't understand any of informatica's/cognos's dependencies or know how db2 works. I am not confident that simply copying user table data accross is enough and would really like some confirmation or pointers to know exactly what to do.

closed as too broad by Marco, Colin 't Hart, mustaccio, McNets, James Anderson May 12 '17 at 8:19

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Why don't you save yourself a bunch of licencing and use SSIS instead of informatica. You're going to have to mess with your informatica jobs anyway to migrate to SQL Server. A 'data warehouse environment' is just another database that has a lot of integrations going in (and sometimes out), and reports coming out, and is optimised for reporting. If you move your DW database you'll need to first test that your Infromatica jobs can write into it and your Cognos reports can read out of it. You might have to redevelop both of those, particularly if they make extensive use of database functions – Nick.McDermaid May 11 '17 at 8:46
  • At this stage you need to 'try it and see' and discover which parts of your system need to be redeveloped. How big is the DB2 database? Data warehouses are often loaded in batch, and can usually be loaded from empty, so it might be an idea to run your DB2 and your SQL datawarehouse in parallel. But the first step is to find all the 'gotchas' – Nick.McDermaid May 11 '17 at 8:48

One thing is hardware resources. The current server will use a certain amount of memory, CPU and IO to do its job. For a 1TB database this is likely to be non-trivial. If you move that work to a new server it is still going to need (more-or-less) the same amount of memory, CPU and IOPS. If the new server is already hosting applications and serving their needs it may not have capacity to also accommodate the new needs of the migrated application. Result: everything runs slowly; sadness abounds.

DW work tends to use big sequential reads. OLTP work usually has small random IO. If you mix these two on one hardware box it could prove tricky to configure the storage (SAN array?) for best performance for either, likely resulting in mediocre performance for both.

Each instance of SQL Server has a fixed overhead, such as tempdb and minimum memory. The more instances there are on one box the more overhead has to be paid for. Also you'll likely have to over allocate resources to each instance to accommodate peak load, again costing more. With everything in a single instance this cost can be spread. The flip side is that it can be more work to isolate normal work from a greedy task on the same instance, but not impossible. Making this decision would be a consultancy engagement, not a Q&A post.

Not the answer you're looking for? Browse other questions tagged or ask your own question.