none
两台SQL2005能否共享一个数据文件? RRS feed

  • 问题

  • 由于数据迁移的成本比较大,能否让两台数据库服务器共享一份数据文件,只要读就行?
    2009年2月11日 8:14

答案

  • If read only, you could make multiple SQL Servers using and sharing DB files as scale out solution as following.

    Scaling Out SQL Server 2005: http://msdn.microsoft.com/en-us/library/aa479364.aspx

    Scalable Shared Databases

    The easiest scaleout solution to implement in SQL Server 2005 is Scalable Shared Databases. You create a database on a SAN, and up to eight SQL Server instances running on different servers attach to the database, and start handling queries. This is the classic "shared disk"–style scaleout solution, where processing power is scaled out, but only a single disk image of the data is used. At this point, those of you who understand SQL Server are saying, "But what happens to the locks? I thought each SQL Server instance kept its own locks in its own memory." This is true. Each instance will maintain its own database locks, and none of the instances will know about the other instances' locks. The only way this will work is if there are no locks, and thus Scalable Shared Databases work only if the database is attached as a Read Only database. This means that Scalable Shared Databases are great for data warehouses or reporting databases, but they are not suitable for applications that update data. Going back to our data characteristics, Scalable Shared Databases work only if the Update Frequency is zero. This data is, by definition, historical, and therefore it is all reference data. Figure 1 illustrates the use of Scalable Shared Databases as a scaleout solution.

    For SQL Server Scale Out topics, here is an summary by SQLCAT http://blogs.msdn.com/sqlcat/archive/2008/06/12/sql-server-scale-out.aspx

    Hope this helps.
    Dannol

    2009年2月13日 1:49

全部回复

  • 理论上可以, 如果是生产环境还是不要这样用。
    2009年2月11日 8:34
  •  我也感觉是可以,但是找了很多资料没有看到相关的?请指教如何实现?
    2009年2月11日 8:52
  • 每次使用时,导入这个数据库文件就可以了:
    打开SSMS(SQL Server Management Studio) ,然后连接Database engine. 右键点击 导入[attach],在弹出的对话框中指定数据库文件, re-start你的sql server.

    如果是生产环境不要这样用!!!!
    2009年2月11日 9:00
  • 我想你的范畴应该属于数据库服务器集群的范畴,用于负载均衡,共用存储节点。可以参考这篇文章http://download.microsoft.com/download/6/f/2/6f239ced-6de4-4b20-aa8d-d9aea09bbdd8/20070607_chengdu.pdf
    2009年2月11日 9:02
  • 一个数据库文件被一个实例独占,其他实例如何在使用,共享存储是怎么做的,可以让一个数据文件被两个实例使用吗?

    2009年2月11日 9:12
  •  不好意思,我对于集群也只是看了一些理论文章,至于具体怎么配置,我倒还没有试过,不过有一定可以确定,肯定是行的通的,你可以在MSDN上面找sqlserver2005的集群解决方案,应该会有的吧
    2009年2月11日 9:16
  •  谢谢,希望大家帮顶起来
    2009年2月11日 9:18
  • 你好,刚才看了一篇文章,仅供参考,http://tech.ddvip.com/2008-11/122610724590323.html
    2009年2月11日 9:18
  • No you can't, any database file can only be owned by one instance.
    2009年2月11日 14:39
  • jackfor001 说:

    由于数据迁移的成本比较大,能否让两台数据库服务器共享一份数据文件,只要读就行?



    用數據庫用戶權限控制或用數據庫複製訂閱不行?




    吳熹
    2009年2月12日 9:52
    版主
  • If read only, you could make multiple SQL Servers using and sharing DB files as scale out solution as following.

    Scaling Out SQL Server 2005: http://msdn.microsoft.com/en-us/library/aa479364.aspx

    Scalable Shared Databases

    The easiest scaleout solution to implement in SQL Server 2005 is Scalable Shared Databases. You create a database on a SAN, and up to eight SQL Server instances running on different servers attach to the database, and start handling queries. This is the classic "shared disk"–style scaleout solution, where processing power is scaled out, but only a single disk image of the data is used. At this point, those of you who understand SQL Server are saying, "But what happens to the locks? I thought each SQL Server instance kept its own locks in its own memory." This is true. Each instance will maintain its own database locks, and none of the instances will know about the other instances' locks. The only way this will work is if there are no locks, and thus Scalable Shared Databases work only if the database is attached as a Read Only database. This means that Scalable Shared Databases are great for data warehouses or reporting databases, but they are not suitable for applications that update data. Going back to our data characteristics, Scalable Shared Databases work only if the Update Frequency is zero. This data is, by definition, historical, and therefore it is all reference data. Figure 1 illustrates the use of Scalable Shared Databases as a scaleout solution.

    For SQL Server Scale Out topics, here is an summary by SQLCAT http://blogs.msdn.com/sqlcat/archive/2008/06/12/sql-server-scale-out.aspx

    Hope this helps.
    Dannol

    2009年2月13日 1:49
  • 同一个时间只有一个实例在用这个数据文件就没问题
    集群也是这样的原理
    2009年2月13日 2:11