Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

In my project I have one DB used for everything. I want it to break into two databases. Static tables having look up values are to be stored in one DB and another DB would be having tables with dynamic data. My problem is that how would I use foreign key constraint in between those two DBs. Can someone help me out and suggest a way to proceed, better if I'm provided an example for the same.

I thought of using synonyms for tables and then constraints on synonyms. but later I came to know that synonyms couldn't be used for constraints.

I need to maintain relationships among the tables from both DB as the issue is with update, with a new release I just want to update look up tables and for the same I want to split my DB.

share|improve this question
2  
Are you starting to experience problems that can be related to database size? Is that why you want to split it? –  Sandokan Nov 30 '12 at 11:30

2 Answers 2

Robin:

If you want to physically separate volatile and static data, I suggest that you look at the filegroups feature. (Scroll down to the middle of that web page to get to the relevant information.)

Filegroups allow you to group tables. This allows you to control their physical location more easily (often-used data could be placed on fast SSD and other data could be placed on slower hard disks, for example), control read/write access (a filegroup can be flagged read-only) and improve backup/restore schemes (a filegroup can be be backed up or restored). Since all of the the tables are in the same database, regardless of filegroup, declarative referential integrity (DRI, also known as "foreign keys") still works.

If you insist on using different databases, you won't be able to use foreign keys and will have to use a different method. This means writing your own triggers (or maybe procedures). Writing your own code means extra work, there is always the possibility of bugs in that code and triggers usually perform worse than DRI. There are other negative aspects of using different databases, most obviously security (you'll have twice as much to manage) and point-in-time recovery (it's harder to get consistency between two databases than one database). Those things might not seem important today, but:

  1. It is easier to do things correctly now than it is to fix things later.
  2. It's best to acquire good habits.
share|improve this answer

Let me suggest you are clueless and have no idea what you want.

See, sql server size is a problem - either in express when you hit the hard limit of 10gb, or on a "real" server when you hit - no idea, maybe 10000gb or so.

By most means your assumption are wrong - you can nothing and only ad complexity (foreign keys dont work, msut replace them with triggers) in this. If this is two db, then you can nothing, if that is two servers, you add latency and a ton of other issues.

I would start with the assumption taht the requirement you present here is invalid. Why you think there is a need for that?

I have a nearly 1000gb database at home and no problems with size.

share|improve this answer
    
Size is nothing to do in my case, I've just updated my query plz go through it. –  Robin Jain Nov 30 '12 at 11:51
1  
"at home"? I thought you ran that database on your phone? –  Ward Dec 3 '12 at 3:38
    
Well, my home is my casle. The lower 2 floors of the house I live in are my companies offices ;) We have 15kw USV for our little HPC cluster in the basement. –  TomTom Dec 3 '12 at 5:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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