SQL Server Performance

Dynamic DPV

Discussion in 'SQL Server 2005 Clustering' started by haresh, Jan 4, 2007.

  1. haresh New Member

    Hello,

    Can I construct Dynamic Distributed Partitioned Views (DPV)?

    My Scenario is:

    I have distributed my database as per UserID and placed database on different or same server as per below:

    Server1 - DB1 - 1 to 100000
    Server2 - DB2 - 100001 to 200000
    Server3 - DB3 - 200001 to 300000

    If I know I have above servers and database then I can make my distributed view easily, but in future If I add one more server:

    Server4 - DB4 - 300001 to 400000

    Then is there any way to construct a dynamic view that can retrieve rows from new servers also considering I don't need to change views each time when I add new DB.

    Thanks,

    Haresh
  2. MohammedU New Member

    I don't think it is allowed/doable.

    Mohammed U.
  3. haresh New Member

    Hello Mohammed,

    Can you please suggest me how can I handle that situation? Otherwise how can I scale out my application or architecture using database distribution?

    Thanks,

    Haresh
  4. satya Moderator

    You have to perform the changes manually by modifying the code and you might add a checkpoint to ensure the code is modified before the server is added to network.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  5. MohammedU New Member

    Check the BOL topic "Distributed partitioned views"

    Mohammed U.
  6. haresh New Member

    Hello,

    I have SQL 2000 server installed on 3 servers/machines. On one machine default instance is of SQL Server 2005 and one I made say: HARESH (Server Name)HARESH (instance) for SQL Server 2000. Other 2 are running with SQL Server 2000 only.

    I am able to add and test links of other 2 servers on HARESHHARESH. but the only problem is not able to retrieve data from HARESHHARESH when I add link of it on other servers.

    Would you please tell me the step to add HARESHHARESH as a linked server on other 2 servers?

    Thanks,

    Haresh
  7. MohammedU New Member

    Linked server for Named instance also the same way... there is no difference...

    What is the error you are getting?
    When you specify the named instance in your query use square backets [] around the name other wise you will get syntax error...

    You can alia the linked server name also...

    Check the lINKED SERVER topic in BOL...


    Mohammed U.
  8. haresh New Member

    That's saying SQL Server doesn't exists.

    Let me give you my scenario:

    Server Name: HARESH
    on default instance SQL Server 2005 is installed
    SQL Authentication: UID: sa , PWD: 2005

    on newly added "HARESH" instance SQL Server 2000 is installed
    SQL Authentication: UID: sa , PWD: 2000

    Now I added link of this instance "HARESH" in my other server as below:

    In Add Linked Server box

    General Tab:

    Selected Other data source

    Product Name: ''
    Data Source: HARESHHARESH
    Provider String: Server=HARESH;INSTANCENAME=HARESH
    Catalog: ''

    Security tab:

    Local Login: sa
    Impersonate: unchecked
    Remote User: sa
    Remote PWD: 2000

    Be made using the login's current security context

    That's it.

    Now when I query: select * from HARESH.DB.UID.tablename or select * from [HARESHHARESH].DB.UID.tablename
    it says: SQL Server doesn't exists.

    Thanks,

    Haresh

Share This Page