Splitting Database into Mulitple Others
-
We are wanting to split our Business Intelligence database being used to build Cubes.
We have lots of external systems attaching to the database eg. 10 other systems sending information to the BI database.
What has been suggested is........
We split the database up into 3 or 4 other databases eg. Staging, Reference and FactTblCubes.... We will then need to change all Stored Procedures and SQLs to reference the appropiate database for each table. In each sql or sp it could reference the 3 or 4 databases.
Or.........
We create databases specifc to each of the 10 systems and have the sp's or sql's reference 1 database. On the odd occasion we may need to share information across databases.
Would appreciate any feedback on performance issues etc on either scenaro.
Cheers
Angie
-
Sounds like a lot of job to do if to do it proper way.
You will do it anyway but there is a way to postpone this job.
Create in DB say Staging view "SELECT * FROM Reference.dbo.SomeTable" and name it "SomeTable" - the same name as original table. This will help you retrieve data from another DB without changing code.
Than create trigger on this view FOR INSERT, UPDATE, DELETE
DELETE FROM Reference.dbo.SomeTable
where SomeTableKey in (select Key from deleted)
INSERT INTO Reference.dbo.SomeTable
SELECT * FROM inserted
This will allow you to do modifications of data without changing the code.
This is not best solution in terms of performance. But it is quick and reliable fix. Later you can modify your SPs for using external DB tables in better way.
P.S. You know columns names, so don't forget to replace "*" with right names.
_____________
Code for TallyGenerator -
Thanks for the reply.
We will give that a go in the meantime. As you say we may just need to do it the proper way anyway.
Thanks
Angie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply