locked
What is good design apprach in this case? RRS feed

  • Question

  • Hello,

    Server : SQL Server 2008

    I have two databases, let say DBMain and DBChild. These two database could be on same server machine, or could be on different server machine.

    DBMain has 3 tables. (Main1, Main2 and Main3 table)

    DBChild has 2 tables. (Child1 and Child2 table)

    I would like to create one stored procedure in DBChild database. That sp needs to access Main2 table from DBMain database.

    There are couple of solutions I can think of..

    1. In application layer, not database layer, we may be able to do this. But if possible, I would like to avoid doing this in the application layer.

    2. We may set up in database layer to schedule to transfer table data...but it will create Main2 table in DBChild database..If possible, I don't want to break the current table schema.

    3. We may be able to use full qualified table name(like [DBMain].[dbo].[Main2]) in that sp in order to access DBMain database table from DBChild...

        However, I guess this only works as long as two database are on same server machine and under valid permission.

        Also, if two database are on different server machine, I guess there needs to be some set up required in order to access different server machine.

    I think this is pretty much I can think of possible solutions..

    #3 is pretty much only way what I want to achieve(Keep the database table schema structure and do in database layer)...

    Am I right? Is there any better way/or alternative way?

    Thanks in advance.

    Friday, March 23, 2012 4:17 PM

Answers

    • Using Application Layer: If you are planning to avoid this, then avoid it.
    • Transferring Data from One server to Another server: This will create data duplicacy and delay interval on the data.
    • Using Linked Server: You can using four part naming and ask SQL Server to connect to the other database and fetch the data across Servers. This needs to create a Linked Server.

    Linked Servers are in Server Objects -> Linked Servers

    You can use "master.dbo.sp_addlinkedserver" SP to add Linked Servers.

    /****** Object:  LinkedServer [LOCALSERVER]    Script Date: 03/23/2012 22:08:20 ******/
    EXEC master.dbo.sp_addlinkedserver @server = N'LOCALSERVER', @provider=N'SQLNCLI', @datasrc=N'My-PC'
    GO


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    • Marked as answer by Kalman Toth Thursday, March 29, 2012 11:34 PM
    Friday, March 23, 2012 4:40 PM
  • I personally agree with Kalman that best choice is Single database.

    But it depends on your environment. If it is existing database on different server, your choice would be number three. 

    If NOT, then Kalman is totally right. 

    If you want to use dynamic SQL, your query would be slow and kind e unefficient. 


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Kalman Toth Thursday, March 29, 2012 11:34 PM
    Friday, March 23, 2012 5:36 PM

All replies

  • Your third solution I think is much better and practical. 

    If they are not on the same server, you need to create LINKED Server. How to Create Linked server in BOL.

    Which in that case you have to take to the account your security as well. 

    In mean time, you can use your second solution. You can have your Main2 table inside your child database, which will be synced with CLR triggers from your DBMain. 


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Friday, March 23, 2012 4:26 PM
  • Single database is your best option.

    Avoid placing them on different servers (linked server).

    Two databases on the same server is the second best.


    Kalman Toth SQL SERVER & BI TRAINING

    Friday, March 23, 2012 4:29 PM
  •     Also, if two database are on different server machine, I guess there needs to be some set up required in order to access different server machine.

    You can access the tables in the other server using a linked server with a four part naming convention.

    http://msdn.microsoft.com/en-us/library/aa213778(v=sql.80).aspx

    http://www.sqlservercentral.com/articles/Distributed+Queries/anintroductiontolinkedservers/1366/ (Free Login Required).


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Friday, March 23, 2012 4:32 PM
    • Using Application Layer: If you are planning to avoid this, then avoid it.
    • Transferring Data from One server to Another server: This will create data duplicacy and delay interval on the data.
    • Using Linked Server: You can using four part naming and ask SQL Server to connect to the other database and fetch the data across Servers. This needs to create a Linked Server.

    Linked Servers are in Server Objects -> Linked Servers

    You can use "master.dbo.sp_addlinkedserver" SP to add Linked Servers.

    /****** Object:  LinkedServer [LOCALSERVER]    Script Date: 03/23/2012 22:08:20 ******/
    EXEC master.dbo.sp_addlinkedserver @server = N'LOCALSERVER', @provider=N'SQLNCLI', @datasrc=N'My-PC'
    GO


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    • Marked as answer by Kalman Toth Thursday, March 29, 2012 11:34 PM
    Friday, March 23, 2012 4:40 PM
  • Thanks for all your valuable reply.

    It looks like most answers are under these 3 options.

    One question though, let's say I decide two database on same server machine, and I will take a route with #3.

    Is there any way that I can declare the database name variable globally?

    What I meant to ask is that..let's say I want to join two tables in different database...

    Select blah,blah, from [DBMain].[dbo].[Main2] inner join [Child1] on blahblahblah..

    Just curious I can simply to store variable like this globally..

    Declare @svr nvarchar(500)

    Set @svr = 'DBMain'

    so in any sp, I can use like @svr.[dbo].[Main2]...which I don't believe I can unless I use dynamic query explicitly.  right?

    Just thought if I could have a variable like this, it would be easy to change the database name easily.

    Thanks again.

    Friday, March 23, 2012 5:30 PM
  • I personally agree with Kalman that best choice is Single database.

    But it depends on your environment. If it is existing database on different server, your choice would be number three. 

    If NOT, then Kalman is totally right. 

    If you want to use dynamic SQL, your query would be slow and kind e unefficient. 


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Kalman Toth Thursday, March 29, 2012 11:34 PM
    Friday, March 23, 2012 5:36 PM