主页 / dba / 问题 / 193718
Accepted
Martin Guth
Asked: 2017-12-22 03:49:57 +0800 CST

创建表/索引时如何强制用户指定文件组

  • 772

我很快就会迁移到 SQL Server 2017，因此我正在努力美化我们数据库中一些被忽视的方面。一方面是文件组：我的 DBA 前任创建了多个文件组，但是因为没有人真正被告知大多数对象只是转到默认文件组 (PRIMARY)。

现在我想强制创建表和索引的人实际命名他们的对象应该驻留的文件组。含义：没有“ON [Filegroup]”的 CREATE TABLE 或 CREATE INDEX 应该回滚并返回文件组丢失的错误。有什么办法可以做到这一点，您会建议继续采用这种方法吗？我刚刚发现基于策略的管理无法阻止在默认文件组上创建对象。

这样做的要点是，someboy 应该首先为对象选择文件组，而不是在默认文件组中创建它们，然后必须根据 DBA 的咆哮移动对象:-)。

我想到了两种技术......但是由于缺少知识而无法开始使用它们中的任何一种：

  • 使用数据库触发器检查是否有 on 子句，如果没有则回滚 --> 这似乎很脆弱，因为它取决于命令文本的文本分析
  • 使主文件组的大小非常小（100KB？）以便系统表仍然能够容纳但其他中等大小的表会导致错误，因为没有足够的剩余空间 --> 这可能是个坏主意可能对数据库可用性产生严重的副作用，并且在防止在默认文件组中创建每个用户定义的表或索引方面不够精确

非常感谢您的帮助

马丁

sql-server filegroups

1 个回答

  1. Best Answer

    您可以使用 Read_Only 属性集创建一个新的默认文件组。

    添加数据库文件：

    USE [StackExchange]
GO
ALTER DATABASE [StackExchange] ADD FILE ( NAME = N'StackExchange_DefRO', FILENAME = N'C:\SQL\SQL_DATA\StackExchangeRO.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [DEFAULTRO]
GO

    将新文件组修改为READONLY：

    USE [master]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'DEFAULTRO'
if(@readonly=0)
    ALTER DATABASE [StackExchange] MODIFY FILEGROUP [DEFAULTRO] READONLY
GO

    将新文件组修改为DEFAULT：

    GO
USE [StackExchange]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'DEFAULTRO') ALTER DATABASE [StackExchange] MODIFY FILEGROUP [DEFAULTRO] DEFAULT
GO

    如果用户现在创建对象而不指定文件组：

    USE [StackExchange]
GO

/****** Object:  Table [dbo].[NewTable]    Script Date: 21.12.2017 14:12:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NewTable](
    [ID] [int] NULL,
    [Test] [nchar](10) NULL
)

GO

    他们将收到错误消息：

    Msg 1924, Level 16, State 2, Line 11
Filegroup 'DEFAULTRO' is read-only.
    • 8

相关问题