Is database backup compression enabled?

How do I check if database compression is already enabled?


select case when value = 1 then 'Yes' else 'No' end as [Backup Compression Enabled] from sys.configurations where name = 'backup compression default' ;

Are remote admin connections enabled?

How do I check if remote admin connections are enabled?

select case when value = 1 then 'Yes' else 'No' end as [Remote Admin Connections Enabled] from sys.configurations where name = 'remote admin connections' ;

Check if database mail is enabled

How do I check if database mail is enabled?

select case when value = 1 then 'Yes' else 'No' end as [Database Mail Enabled] from sys.configurations where name = 'Database Mail XPs' ;

On the importance of water

There was a time in the not-to-distant past where I had to pay for clean water to drink, from work, at work, to drink while on the clock.

When did water become an optional business benefit? 

My initial thought was fairly obvious - "Why am I having to pay for clean water to drink at work?"

On the one hand, it's reasonably priced, although unsubsidized, and on the other, what the ???

How is this not a basic business expectation to have clean water to drink, that isn't full of chemicals, etc?

However, after a few weeks, what occurred to me was a little less direct: The other side of this equation is the impact on productivity - if people have to leave the workspace for basic needs such as water (or coffee!) - you might not like what they find while they're out.

Because your competitors have figured out how to buy coffee.

How to stay in sync with others, at your level

== Part Three ! ==

For change data capture, Microsoft has a great overview online

However, for my code, I use something like this:

exec sys.sp_cdc_enable_db;
go

create table table1 (column1 int);
alter table table1 add nowval datetime;
go

exec sys.sp_cdc_enable_table n'dbo', n'table1', null, null,null,null,'nowval',n'primary',1;
go
insert table1 (nowval) values 
(sysdatetime()),(sysdatetime()),(sysdatetime()),(sysdatetime()),
(sysdatetime()),(sysdatetime()),(sysdatetime()),(sysdatetime()),(sysdatetime());
go

First, I have to enable Change Data Capture (CDC) on the database.

Then, for tracking purposes, I'm going to go ahead and make a test target named "table1".

Once I have enabled CDC on the database, and my target, then I enable the target table to track the changes to the column "nowval" using the function sys.sp_cdc_enable_table.

To track and prove the changes, I've included the insert statement I use.

Lastly, I want to see my changes, so I run this query:

SELECT CT.__$start_lsn, CT.__$operation, CT.*, LSN.tran_begin_time, LSN.tran_end_time, LSN.tran_id
FROM CDC.fn_cdc_get_all_changes_DBO_TABLE1(@from_lsn, @to_lsn, N'all') AS CT
INNER JOIN cdc.lsn_time_mapping AS LSN ON CT.__$start_lsn = LSN.start_lsn
GO

How to stay in sync with others, at your level

== Part Two ! ==

I have since lost the URL that first inspired this code, and when I find it, I'll update here.

Here's the basic idea for the DDL triggers:

Create a logging table:

CREATE TABLE dbo.DDLEvents

(

 EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

 EventType NVARCHAR(64),

 EventDDL NVARCHAR(MAX),

 EventXML XML,

 DatabaseName NVARCHAR(255),

 SchemaName NVARCHAR(255),

 ObjectName NVARCHAR(255),

 [ObjectID] INT,

 HostName VARCHAR(64),

 IPAddress VARCHAR(32),

 ProgramName NVARCHAR(255),

 LoginName NVARCHAR(255)

);

...and then, create a trigger that uses it. In this case, I'm making one that's at the server level. This is going to capture the events ALTER_DATABASE, CREATE_DATABASE, or DROP_DATABASE whenever they happen.

CREATE TRIGGER ddl_trigger
ON ALL SERVER
FOR ALTER_DATABASE, CREATE_DATABASE , DROP_DATABASE
AS
begin
 DECLARE @WhatHappened XML ;

 select @WhatHappened = EVENTDATA() ;

 DECLARE @ip VARCHAR(32) = (
  SELECT client_net_address
  FROM sys.dm_exec_connections
  WHERE session_id = @@SPID
  );
 INSERT AuditSampleDB.dbo.DDLEvents
    (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
     SELECT
        @WhatHappened.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
        @WhatHappened.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @WhatHappened,
        DB_NAME(),
        @WhatHappened.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
        @WhatHappened.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END

GO

How to stay in sync with others, at your level

I have found that sometimes, I need to communicate with others around me - colleagues, whether or not they're DBA's.

To do that, I use DDL triggers, Changed Data Capture, and Common Criteria Compliance (c3).

The benefits of that is using DDL triggers, I can monitor things at the system or database level.

Using CDC, I can track the progress of the objects as data is loaded.

There are two main benefits to the combination of these two technologies:
1. It is possible to manage them separately, and
2. When combined, it is possible to learn more about both what object(s) were made in the database, and what data changed in existing objects.

However, this is also the downside - CDC has to be set up in advance, knowing what columns I want to track, and the DDL triggers have to be declared in advance, as well.

In the next post, I'll walk through the steps to set up a sample version of each of these.