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.

How to stay in sync with yourself

When I try to stay in sync with myself, I find that it's usually for one of a couple scenarios, which normally I can handle by answering one of two questions:

1. What did I do about X?
2. Where did I put the answer to #1?

To that end, I use two tools, which are Agent Ransack, and WinMerge - usually in that order, too, which is kinda backwards to the way I listed the logical questions, above. You ever notice that when you're looking for something, your search isn't logical? Yeah, mine too.

So, Agent Ransack lets me find just about anything in any folder on my computer, and it's quite fast at doing it. 

The other tool of interest in this search is WinMerge, which is especially useful in the cases where I need to answer Time - Over - Time questions. That is, what did I do about this problem the last time I solved it?

For that, I use WinMerge to comapre either folders:

Or to compare files:

Staying in Sync with the Dev next to you

So there's any number of ways to stay in sync with the developer next to you.

The trick is finding out which one works for you.

In this series of blog posts, I'm going to explore the more technical side of these equations.

Specifically:

1. How to stay in sync with yourself
2. How to stay in sync with others, at your level
3. How to stay in sync with others, outside your level (in any direction, really)
4. How to stay in sync with others, outside your ability to influence directly

Tracking Temp Tables using DDL triggers

At my SQL Saturday session this past weekend, I had a great question about how to track Temp Tables - such as those made by a developer and never cleaned up.

Using the test framework I had presented, I went back and tried it again, and no, they aren't tracked by the DDL trigger. However, it was possible to quickly and easily write a "capture everything" extended events session to record the SQL Statements that created them.

With a little filtering, it would be easy enough to restrict that by database.

Here's my test:

-- Script inspired by
-- www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
USE TEMPDB;
GO
IF EXISTS (
SELECT *
FROM tempdb.SYS.TRIGGERS
WHERE NAME = 'DDLTrigger_Sample'
)
BEGIN
DROP TRIGGER DDLTrigger_Sample
ON DATABASE;
END
GO
USE MASTER;
GO
IF EXISTS (
SELECT 1
FROM sys.server_triggers
WHERE NAME = 'ddl_trig_alter_db'
AND parent_class_desc = 'SERVER'
)
BEGIN
DROP TRIGGER ddl_trig_alter_db
ON ALL SERVER;
END
GO
IF EXISTS (
SELECT 1
FROM sys.databases
WHERE NAME = 'AuditSampleDB'
)
DROP DATABASE AuditSampleDB;
GO
CREATE DATABASE AuditSampleDB;
GO
EXEC AuditSampleDB.dbo.sp_changedbowner 'SA';
GO
USE AuditSampleDB;
GO
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)
);
USE TEMPDB;
GO
CREATE TRIGGER DDLTrigger_Sample ON DATABASE
FOR CREATE_PROCEDURE,
ALTER_PROCEDURE,
DROP_PROCEDURE,
CREATE_TABLE,
ALTER_TABLE,
DROP_TABLE AS
BEGIN
SET NOCOUNT ON;
DECLARE @EventData XML = 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 @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END
GO
USE TEMPDB;
GO
CREATE TABLE #TESTTEMP (C1 INT);
GO
DROP TABLE #TESTTEMP;
GO
CREATE TABLE ##TESTTEMP2 (C1 INT);
GO
DROP TABLE ##TESTTEMP2;
GO
USE MASTER;
GO
CREATE TABLE TEMPDB.DBO.ISTHISTRACKED (C1 INT);
GO
DROP TABLE TEMPDB.DBO.ISTHISTRACKED;
GO
SELECT LoginName,
EventType,
EventDDL,
EventXML
FROM AuditSampleDB.dbo.DDLEvents
ORDER BY EventDate
GO

Looking forward to SQL Saturday #395

I'm really excited to be presenting this coming weekend in Baltimore (Columbia, actually) at SQL Saturday #395!

See you there !

netdom join isn't working - eh? wha?

I couldn't join my VM to the Test domain, using just

netdom join machinename /domain:mydomain.com 


Instead, I was getting this error message:

Logon failure: unknown user name or bad password.

The command failed to complete successfully.


netdom join machinename domain:mydomain.com /userd:mydomain.com\Administrator /passwordd:*

Running SSMS as a non-domain user

Every so often, I have to run SSMS against a host (or a VM ) that's in a different domain than the current PC/client that I'm on.

runas.exe /netonly /user:domain\username "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Since, in whatever environment that is, I have to do this repeatedly, I create a shortcut on the desktop for it, and use the shortcut as the text above.

Editing a resume

A couple quick thoughts on resume editing:

Don't include your salary history.
It's nobody else's business what you made. Even if you were government-employed, and your pay is a matter of public record, make people ask you for that information. Think of it this way - if you don't include it, and they ask, and they already know the answer (maybe they had that same job at that same pay grade, for example), and you tell them what they already know, then you've proven once again how honest and upstanding you are!

Don't include your references.
This is putting the cart before the horse, and exposes your friends and contact list to potentially needless phone calls, *especially* if you put your resume on a publically searchable website.

Employers care about the five W's , yes, but only after they've either skimmed your information or had it electronically scored, or both. I do suggest you address these things in your resume, sure, but do it as story telling, not as facts and figures.

• who
• when
• why
• where
• how much

So, what's the point? Why do any of this at all? Why not just keyword up a version? Well, yes, and I recommend a keyword heavy version from a text-only resume point of view where you know your information is going to get scanned by searches (e.g. Monster, etc.).

But in general, think about it from a hiring manager's point of view. He wants to know within seconds whether or not you can

• stop his pain
• stop his groups pain
• solve a problem
• save the business money above what it costs him to hire you

If you're going to craft a traditional resume, in any of its forms, then consider how you can tell a 2-3 sentence story for each position you've held, that answers one of these things.

That said, the most important resume I have is my satisfied customers and my testimonials on my LinkedIn profile.

The social network portion of your job hunt can't be overlooked - you know you're getting Googled before the interview, so make that work for you !

Not in college anymore

So the other day, a friend says to me "... and it's just time to stop living like I'm in college, you know?"

In their particular case, I did know. I knew exactly, from having lived through a very similar circumstance once, and I could readily empathize with them. That said, it still made me think, quite a bit, on what that means.

In my case, it meant things joked about years ago as kids were now quite real:

• If you don't pay your bills, mommy and daddy won't save you.
• You really do have to get up every day for work.
• Life is short. Eat dessert first.
• You really do have to go to bed on time every night to get up the next morning.
• Be nice to people, and in general, they'll be nice back.
• Source control (and the processes like it in non-software careers) look like a lot of work, and can be, but actually are there to protect you. It's like insurance for your continued employment.
• Investing is non optional. There will come a day where you don't want to or can't work anymore. Then what are you going to do?
• Always, always, always buy the best shoes you can afford.
• Invest in yourself - train yourself. Don't expect other people to get you places.
• Do what you love, sure, but realize that the definition of "professional" says more about what you do when the fun is no longer fun.

Task Scheduler Error 2147942667

Cause

Error code 2147942667 indicates that the directory name is invalid. In most cases, this is caused by placing quotes around the "Start In" directory.

Resolution

Remove the surrounding quotes from the "Start In" path.

The path of the program to launch must be surrounded by quotes if it contains spaces; the "Start In" path must not be surrounded by quotes.

https://support.microsoft.com/kb/2452723?wa=wsignin1.0

http://www.arcomit.co.uk/support/kb.aspx?kbid=000058

VMWare, Virtual Machines, and Viruses, Oh My!

So I'm building a new set of lab machines again. They're going to be using Windows Server 2008 R2 and SQL Server 2012 as guests inside of VMWare's VM Workstation 9.

I don't need virus protection for my VMWare based virtual / lab machines, because:

1. The lab machines are virtual, and
2. The lab machines, after activation, have no internet access, and
3. If my host PC has a virus that transmits to my lab machines, I have bigger problems that an infected lab machine

So, I went in to my anti-virus program, and added read/write/execute exclusions for the following files types:

• *.iso 
• (Granted, ISOs are not really a part of the lab, per se, but I get better throughput when loading into the VMs if the source images aren't being scanned. For pete's sake, they're read only anyway.)
• *.vmdk
• *.nvram
• *.vmsd
• *.vmx
• *.vmxf
• *.vmem
• *.vmss

What I did NOT exclude were the files that I know are also file type extensions used elsewhere, such as:

• *.lck
• *.log

Sometimes, it's the little things

Visual Studio 2013 editor highlights the current line by changing the background color of the current line.

By default, it's this hideously impossible to see also-black that looks like crud on my LED screen. Also, the fact that I'm using the dark theme means that also-black doesn't help when the highlight value is a grey value of 80% black.

So I changed it from the default to Navy for Active, and Olive to Inactive.

It's found in the Visual Studio 2013 menu at

• Tools\Options\Environment\Fonts and Colors\

The ones to edit are

• Highlight Current Line (Active)
• Highlight Current Line (Inactive)

Change all the SSIS package's protection levels, all at once

So, I have a scenario where I'm working in SSIS 2012 on SQL Server 2012 with SQL Server Data Tools 2012 (SSDT), and in order to ensure all the packages are both coming to me and going to others with the same level of protection and level of editability, I need to change all of the protection levels of all the packages in my solution, all at once. Somehow, somewhere, one or more of them got reset to a non-default value - or maybe, we need to pull one back from some other source - either way, at anything above about two packages, this gets to be a real pain.

I found two options:

Before you do anything else:

1. First things first, commit everything to source control, close SSDT, and make a zip backup of the whole project folder. 
2. Verify the backup.

Then:

1. Set or Change the Protection Level of Packages
1. http://technet.microsoft.com/en-us/library/cc879310.aspx
2. Now, open a command prompt at the folder with the packages you want to modify.
3. Next, create either a command window, or a command file (AKA batch file) with this:
1. for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;2;strongpassword
4. What I wanted instead was to set them to DontSaveSensitive, plus, I have spaces in the file names (radical, I know), so I used this:
5. for %f in (*.dtsx) do dtutil.exe /file "%f" /encrypt file;"%f";0 /Q
6. The /Q doesn't prompt to overwrite the existing package
7. You can find more about the DTUtil syntax here
1. http://msdn.microsoft.com/en-us/library/ms162820.aspx
2. Open the .dtproj file using your favorite text editor, and do a find and replace on some strings like
1. me="ProtectionLevel">3

So, for me, it was 

me="ProtectionLevel">3

and replace with 

me="ProtectionLevel">0

last, reopen and build