About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Friday, June 12, 2015

vwDBScripts - Change database properties with ease

Some of these ALTER DATABASE commands are used when you are assessing a new server, or even for doing ongoing maintenance on an old server.

ALTER DATABASE [Admin] SET RECOVERY FULL 
ALTER DATABASE [Admin] SET RECOVERY SIMPLE     
ALTER DATABASE [Admin] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE     
ALTER DATABASE [Admin] SET SINGLE_USER WITH ROLLBACK IMMEDIATE   
ALTER DATABASE [Admin] SET MULTI_USER WITH ROLLBACK IMMEDIATE    
ALTER DATABASE [Admin] SET READ_ONLY     
ALTER DATABASE [Admin] SET READ_WRITE    
ALTER DATABASE [Admin] SET AUTO_SHRINK OFF WITH NO_WAIT    
ALTER DATABASE [Admin] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [Admin] SET OFFLINE WITH ROLLBACK IMMEDIATE 
ALTER DATABASE [Admin] SET ONLINE  
ALTER DATABASE [Admin] SET COMPATIBILITY_LEVEL =

It is much more efficient to do this via T-SQL rather than by right-clicking the database and modifying the properties in the GUI.  Using a script will give you repeatability.  

With this view, you can quickly identify the database properties that need to be changed, such as turning on CHECKSUM or putting the recovery model to SIMPLE or FULL as required.  Then simply look for the appropriate column containing the script and copy/paste into a new window.  All the other columns available from sys.databases are included for reference, especially if you want to add other columns depending on your need.

SELECT Instance, Name, DBID, CreateDate, SqlChecksum
FROM vwDBScripts
WHERE PageVerify <> 'CHECKSUM'

I put this view into my Admin database which I have on all servers which I maintain.  Makes administration much more manageable. 


USE Admin

IF Object_ID('dbo.vwDBScripts') Is Not Null
      DROP VIEW dbo.vwDBScripts
go

CREATE VIEW dbo.vwDBScripts
AS
/*    DATE        AUTHOR            REMARKS
      6/4/15      PPaiva            Initial creation.
      6/14/15     PPaiva            Added column MirroringState.
     

      SELECT *
      FROM vwDBScripts
      ORDER By Name

      SELECT Instance, Name, PageVerify, SqlChecksum
      FROM vwDBScripts
      WHERE PageVerify <> 'CHECKSUM'

      SELECT Instance, Name, RecoveryModel, SqlRecoveryFull, SqlRecoverySimple
      FROM vwDBScripts
--    WHERE RecoveryModel = 'SIMPLE'
      WHERE RecoveryModel = 'FULL'

      SELECT Instance, Name, AutoShrink, SqlAutoShrinkOff
      FROM vwDBScripts
      WHERE AutoShrink = 1

      SELECT db_name(database_id) DB, *
      FROM sys.database_mirroring m     

*/
SELECT  ServerProperty('ServerName') Instance,
            name,
            d.database_id DBID,
            CASE WHEN name In ('master', 'model', 'msdb', 'tempdb', 'distribution')
                        THEN 1
                        ELSE 0
                              END IsSys,
            create_date CreateDate,
            compatibility_level CompLevel,
            user_access_desc UserAccess,
            is_read_only ReadOnly,
            is_auto_close_on AutoClose,
            is_auto_shrink_on AutoShrink,
            state_desc StateDesc,
            recovery_model_desc RecoveryModel,

            CASE WHEN m.mirroring_state = 0 THEN 'Suspended'
                   WHEN m.mirroring_state = 1 THEN 'Disconnected from partner'
                   WHEN m.mirroring_state = 2 THEN 'Synchronizing'
                   WHEN m.mirroring_state = 3 THEN 'Pending Failover'
                   WHEN m.mirroring_state = 4 THEN 'Synchronized'
                   WHEN m.mirroring_state = 5 THEN 'Partners not synchronized. Failover not possible.'
                   WHEN m.mirroring_state = 6 THEN 'Partners synchronized.  Failover possible.'
                   WHEN m.mirroring_state Is Null THEN 'NONE'
                   ELSE 'undefined'
                   END MirroringState,

            page_verify_option_desc PageVerify,
            collation_name,
            is_in_standby,
            'ALTER DATABASE [' + name + '] SET RECOVERY FULL' SqlRecoveryFull,
            'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE' SqlRecoverySimple,
            'ALTER DATABASE [' + name + '] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE' SqlRestrictedUser,
            'ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' SqlSingleUser,
            'ALTER DATABASE [' + name + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE' SqlMultiUser,
            'ALTER DATABASE [' + name + '] SET READ_ONLY' SqlReadOnly,
            'ALTER DATABASE [' + name + '] SET READ_WRITE' SqlReadWrite,
            'ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF WITH NO_WAIT' SqlAutoShrinkOff,
            'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM' SqlChecksum,
            'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE' SqlOffline,
            'ALTER DATABASE [' + name + '] SET ONLINE' SqlOnline,
            'ALTER DATABASE [' + name + '] SET COMPATIBILITY_LEVEL = 8  -- 2008 = 100, 2012 = 110, 2014 = 120' SqlCompatibility,

            is_cleanly_shutdown,
            is_supplemental_logging_enabled,
            snapshot_isolation_state,
            snapshot_isolation_state_desc,
            is_read_committed_snapshot_on,
            recovery_model,
            page_verify_option,
            is_auto_create_stats_on,
            is_auto_update_stats_on,
            is_auto_update_stats_async_on,
            is_ansi_null_default_on,
            is_ansi_nulls_on,
            is_ansi_padding_on,
            is_ansi_warnings_on,
            is_arithabort_on,
            is_concat_null_yields_null_on,
            is_numeric_roundabort_on,
            is_quoted_identifier_on,
            is_recursive_triggers_on,
            is_cursor_close_on_commit_on,
            is_local_cursor_default,
            is_fulltext_enabled,
            is_trustworthy_on,
            is_db_chaining_on,
            is_parameterization_forced,
            is_master_key_encrypted_by_server,
            is_published,
            is_subscribed,
            is_merge_published,
            is_distributor,
            is_sync_with_backup,
            source_database_id,
            owner_sid,
            user_access,
            state,
            service_broker_guid,
            is_broker_enabled,
            log_reuse_wait,
            log_reuse_wait_desc,
            is_date_correlation_on
FROM sys.databases d
JOIN sys.database_mirroring m
      ON d.database_id = m.database_id


2 comments:

  1. One lesser known technology in SQL Server, specifically SQL Agent, is MSX/TSX for multi server management. One SQL Server instance is used as the Master, aka MSX, and the servers to be managed are called Target Servers, the TSXs. Multi server jobs must be able to be executed on and TSX instance, therefore the use of @@ server variables and system views must be used. The code shown in your article here is a good representation of such TSQL code. An example of an MSX/TSX job would be checking for database and job ownership, and ensuring that ownership is set to sa or the service account, I prefer sa as the owner, as the login id is the same across ask SQL Server instances, allowing for portability, and avoiding the times when service accounts are disabled by mistake. The default owner of a job is under creator of the job, hence should the creator of the job leave the company, it poses as a threat to the job running successfully. Database ownership poses the same threat, including SQL Server refusing to bring the database online.

    No one, Not even a person with system administrator role can change a multi server job.

    By using dynamic SQL , and MSX/TSX, I was able to successfully manage over 300 instances as the sole MSSQL dba at Colorado's largest energy provider. DBAs must have coding skills and be familiar with system views in order to provide maximum up time.

    Before the birth of the title SQL Developer, we DBAs did it all. Over time, because of third party tools and the invention of the SQL Developer position, many of us need to get back to scripting, even if it means fake it till you make it and using the Script button in SSMS, hence Operations dbas should have a similar skill set as a SQL Developer. How else can we provide support unless we ourselves be masters of Tsql?

    Thank you for the article. This was my first visit and it won't be the last. I suggest multi server management be covered as a future topic. I'm a true lazy dba and have no less than 15 MSX/TSX jobs in my arsenal that can identify and fix, provide security audit reports without SSRS, look for orphan users, predict replication issues. No running around changing scripts, changes to tne MSX job are pushed out to ail TSX instances.

    Familiarize yourself with multi server management, and you won't have developers writing operations code.

    Erik Grob, MCITP
    MSSQL DBA since 4.21a

    ReplyDelete
  2. Thanks Erik for your comments. I will keep you topic in mind; it is of interest to me and of course to all DBAs.

    ReplyDelete