SQL Server Performance

Trying To Understand Distributed & Centralized Database Structures With SQL Server (2008 R2)

Discussion in 'ALL SQL SERVER QUESTIONS' started by Vicerox, Dec 27, 2012.

  1. Vicerox New Member

    Hi, I have recently been trying to understand the concept of distributed and centralized database architectures, with regards to a SQL Server 2008 R2 Database Engine.

    For example, a single database engine with a single instance but multiple databases or a single database engine with multiple instances and each instance having multiple databases.

    I'm confused as to which setup falls under which structure: centralized or distributed?

    Secondly, how does the user base get distributed under both these configurations?

    Lastly, how does it affect database performance (Figures of maximum database users and queries SQL Server 2008 R2 onwards can handle, would be nice)?
  2. Shehap MVP, MCTS, MCITP SQL Server

    Generally speaking about Distributed systems between different sites , it is indeed a big challenging because you have to consider lots of factors while your design such as Data Conflicts , PK Violations , Unique constraints violations, triggers usage and other business constraints exists at App layer that perhaps you might not know about them as DBA or DB Architect ….etc

    Even though, they are still achievable to build distributed systems using 2 different kinds of techniques :

    · Microsoft Merge Replication based on triggers
    · 3rd part party tools for replication such Sybase Replication server , IBM infosphere CDC (Change Data Capture) or Oracle GG (Golden gate)…etc which are commonly based on transaction replications not triggers at all

    For most cases , performance impacts are non-appreciable coz all of such replication solutions should work transparently..

    Despite this, you have to take with consider largely that it is most expected that your DB design should be altered to be adapted with Microsoft Merge Replication particularly more If you haven’t Identity values for PK to ensure no PK violations wile replication between different sites otherwise you have to manage ranges of PK values of each site through APP layer

    Hence you will find several quirks but by the end you have to be confident that it is still doable to build distributed system

    Please let me know if any further help is needed about this regard
  3. Qasim Rasul New Member

    Is transactional replication of sql server r2 is possible between 2 different networks, like local area to wide area? If then pleas guide me coz i have subscription problem.
  4. Shehap MVP, MCTS, MCITP SQL Server

    Yes, it is applicable and even between 2 different domains exists at 2 different DCs , let me know what kind of subscription problem you have ..?
  5. Qasim Rasul New Member

    Currently I have 2 different DR sites at hosting side SQL 2008 R2 Enterprise is installed and at Subscriber side SQL 2008 R2 Developer edition is installed , both are connected through VPN's IPs and remotely connected , while to add subscriber through name pipes and with SQL authentications its give error 53. TCP/IP and Name Pipe are enable. kindly suggest me what can i do.
  6. Shehap MVP, MCTS, MCITP SQL Server

    It seems you have a connectivity issue between the publisher and subscriber thereby you should open a troubleshooting case by the same order of steps below:

    · Make sure that both publisher and subscriber have static TCP port configurations for SQL Server instances (Ex: TCP 1433 )
    · Telnet these TCP ports at each server to make sure no block from any Firewall port devices , Antivirus tools or any other IDS/IPS devices …etc
    · If it is fine for this aspect but you are still not able to connect, you can create a SQL alias for each server at the other server as follows:

    Run>>Cliconfg.exe>>Alias>>>Add> select TCP/IP protocol from left panel >>put the connection string within Server name command box>> and assign the appropriate alias (Might be the same as server name)>>>>mark out dynamic port to be static like 1433 Accordingly to your configuration for SQL Service made within step #1

    · Make sure that you are using SQL Authenticated users for connections between publishers and subscribers

    Please try that and If it still persist , please let me know t
  7. Qasim Rasul New Member

    All ports were already (TCP, UDP)set on 1433, when i try to add Alias name as per you guide me and also entry in the HOST File (Procedure is written below) its work. coz i am not pinging through Server name.

    The HOSTS file is a text file that you can edit with any text editor (such as Notepad). If your network uses HOSTS files for host name resolution and you cannot connect to the other computer using its host name, there may be an invalid entry in your HOSTS file. Search your HOSTS file for the host name of the other computer, verify that there is only one entry per host name, and then verify that the entry for the host name of the other computer is valid.

    For more information on the HOSTS file, see the sample HOSTS file in the %SystemRoot%\System32\Drivers\Etc folder.

    Thanks for Guide me .....
  8. Qasim Rasul New Member


    I have a problem in transcational replication between two different database, DB A is working fine and when i create transactional replication of DB B its not working and when i create snapshoot replication its working. is it possible for two different DBs on transactional replication? and guide me how can i replicate 2 different DBs automatically?


Share This Page