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.