Sql-server – Is Policy-Based Management dead? Is it scalable

configurationsql serversql-server-2012

Policy-Based Management was introduced in SQL Server 2008.

I'm looking for software/tool I can use to enforce configuration standards across a SQL Server 2012 /2014 database environment. Instances are in the hundreds & globally distributed.

Is PBM the right tool to use? It has great out of box functionality, but i fear it will not scale & will not be future-proof.

Other end of the spectrum is Powershell DSC. Which is the more 'roll your own' approach. Admittedly there are 'experimental' scripting resource kits.

Are there other tools which provide a good middle ground?

Best Answer

I liked it and have not seen it being removed anywhere so far. I used PowerShell DSC since the beta and it's great but it is no replacement for PBM. DSC is great for enforcing system states and some SQL states. I can use it to roll out automated testing environments and ensure all the domain membership, firewalls, users, restored databases, masked data, shares, etc. are all done.

Then PBM would be used to help out in areas that I want a report to meet compliance requirements, actually enforcing state on the fly. DCS has a check in time where it scans, and it requires you to write custom scripts to test and apply changes if the test fails, it also requires a central server.

I like both but for very different purposes and PBM can be very helpful if deployed as part of a greater effort.

Related Solutions

Postgresql Cost-Based Vacuum Delay parameters

The postgres docs are the best place to start

SQL Server 2012 Express – Limit Database Size and Retention Policy

You can set the database's max file size to 9GB, but you will need to do the purging yourself. This is not something that SQL Server supports. You could get notified of the limit being reached using extended events, but there is no way for SQL Server to know what records to delete without you telling it. SQL Server does not log or otherwise keep track of how old a row in a table is. You have to build it into the table schema and maintain your own aging logic.

To set the file size in SQL Server Management Studio:

Right-click the database and get properties
Click the Files link in the menu on the left
Click the ... button under Autogrowth / maxsize and set the properties accordingly

T-SQL to set the file size:

ALTER DATABASE MyDb
MODIFY FILE
(
    NAME=N'MyDataFile',
    MAXSIZE=9000MB
)

Note: MyDataFile is the logical name of your data file, not the physical file name on the filesystem. You can find the logical file names of your database files with the sys.database_files system view:

USE MyDb
SELECT name
FROM sys.database_files
WHERE type_desc = 'ROWS'

The value in the name column is the logical name of the file.

Best Answer

Related Solutions

Postgresql Cost-Based Vacuum Delay parameters

SQL Server 2012 Express – Limit Database Size and Retention Policy

Related Question