SQL Server Performance Forum – Threads Archive
Dynamic DPV
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 100000Server2 – 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
I don’t think it is allowed/doable. Mohammed U.
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
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.
Check the BOL topic "Distributed partitioned views" Mohammed U.
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
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.
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
]]>