Globally used Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Globally used Database

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-15 : 10:27:03

sqlBeginner writes "Currently, we have two applications using SQL server for data storage. I know we will be having more in the future. In these two applications, there are tables that are common and tables that are not. We are thinking of having a global db for those common tables and have separate dbs for the ones that are specific to each application. Is it a good idea to do it this way or should we just keep all the tables from both application in one database?

I know this might not be a hard question for you but my manager is asking me for reasons why i think we should separate the 2 apps and have a global db for all the others apps to access the tables remotely.

any suggestion is greatly appreciated!"

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 10:53:36

You can put a VIEW in each database that points to a table in other database:


CREATE VIEW MyViewName
AS
SELECT *
FROM   MyDatabaseName.dbo.MyTableName

Note that MyTableName and MyViewName can actually be the same (but you cannot have a VIEw and a TABLE in the SAME database with the SAME name).

Note: When you backup the databases it is NOT possible to backup DatabaseA and DatabaseB as one unit - so there is an issue with this approach if you need to have the data that is shared, and in the "Other Database", synchronised - e.g. so that you can restore to an exact point in time.

The VIEW solution would work well, for example, where User Logon Info was in a central database - you might have to recreate some user IDs in a disaster scenario, but that would probably be considered "OK".

Account balances and stock levels, on the other hand, would not be so good!

(Please do NOT use "SELECT *" - you are much better off to actually list out the columns when you do it for real)

Kristen

vsrajan76
Starting Member

10 Posts

Posted - 2004-07-15 : 10:58:19

You can always go for maintaining data in two different databases....
As long as both the databases are in the same server...it can be referenced very
easily in the SQl statements as below..
select col1... from DatabaseName.UserName.TableName.ColName

In case, if you want to have those databases in two different servers and want to access it...
You need can either link the server in EM or link it dynamically and use it.
After linking the server just prefix the Server name before databasename in the
SQl statements you use. Thats it.

If you plan to have both the databases in the same server you can always tell the belw mentioned reasons...
The reasons you can give are
1. No duplicate data copy needs to be maintained.
2. Single loaction and centrally loacted to all the user for accessing.
3. Easy to maintain.
4. Less storage space consumption
5. Central adminstration on one database will take care of everything
6. Flexiablity

and many more...

In case, if you want to have it in two different servers...then it is a different case all together.
It has it own adv.. and disadv...

Hope this helps!!!
-Sundar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources