I need to implement distributed database for my system. So that my application sees the database as a single one. But the actual database is distributed over two or three servers.
How to configure this?
Add a comment
|
1 Answer
SQL Server is not distributed database system and as such You can't do it. However, there is a way in SQL Server to make your application see databases from different servers as single database on single server. You have to add linked servers and create synonyms for objects on another server in order to access them the same way You would access them if they were residing in the same database.
As stated in MSDN:
Synonyms can be created for the following types of objects: Assembly (CLR) Stored Procedure, Assembly (CLR) Table-valued Function, Assembly (CLR) Scalar Function, Assembly Aggregate (CLR) Aggregate Functions, Replication-filter-procedure, Extended Stored Procedure, SQL Scalar Function, SQL Table-valued Function, SQL Inline-table-valued Function, SQL Stored Procedure, View, Table (User-defined)
This list probably covers all your needs. You can find examples to create synonym for remote objects in the same article.
As always, do test the performance of Your application because queries on distributed objects may be network intensive. For instance, if You join two tables from different servers, there is a network traffic to get the data from one server to another. Maybe You want to consider replication/log shipping/triggers or so to keep exact copies of remote object on your main database.
Please note that synonyms are added in SQL Server 2005 and links I provided here are for SQL Server 2008 R2.
4 Comments
Girish K G
thanks for your reply.. but does adding link server makes my system a distributed database?
Filip Popović
SQL Server does not recognize "distributed database" as concept if You think of it as multiple SQL Server instances on different servers each having a part of one same db. This is not possible in SQL Server. Quote: "I need to implement distributed database for my system. So that my application sees the database as a single one. But the actual database is distributed over two or three servers." First sentence: no. Second sentence: Yes, with linked servers. Third sentence: no You can't-linked servers are "channel" to access different database from different server.
Filip Popović
And what kind of distributed database do You need?
Girish K G
thanks for your help.. i have tried with replication. is this anything related with distributed database? i have seen a sql query that has BEGIN DISTRIBUTED TRANSACTION command. so doesnt this mean sql can handle distributed db? In my system the database is accessed by many apllications. for both read and write. so i fear there is a chance of deadlock or may be the applications wont get updated data.for optimizing i was looking for this distributed concecpt