Jon Baker's Outer Join

Among the people I work with, I'm known as a command line guy in a Windows world.  Yes, I use Start | Run to open cmd about 100 times a day, and I usually have a few open on my desktop.  But I never really took to ISQL or OSQL when working with SQL Server.  Oh, don't get me wrong, I use Query Analyzer 10:1 over SEM (some things are just faster in the GUI, as much as I hate to admit it). 

One tool in SQL Server 2005 may change me from the GUI completely (notice I said may), and that's SQLCMD.  I became familiar with the tool while working on the presentation material for the Detroit Launch Event.  Until then, it was but a bullet point on some marketing slides. 

In true Letterman fashion , here are my Top 10 Reasons why SQLCMD could become my new favorite tool. 

1. Extensive variablization.  Environment variables and declared variables can be used in various places in the script, including to change database (E.G. USE ($DB) ) and changing the server you are connected to (more on this next).

2. :CONNECT.  From within a single script, you can connect to multiple SQL Server instances and execute commands.  This allows for a great deal of automation possibilities, such as backup from one server, restore to another server.  Also, you could execute a configuration change script on all servers in production.  That's extremely powerful.

3. :!!.  This command allows you to execute an OS command on the client without having to connect and use xp_cmdshell.

4. SQL.INI.  This ini file allows you to set default parameters and even create a default query to execute on the server as soon as you connect.  This helps you to automate your connection properties and to some degree your environment in command line mode

5. SQLCMD Mode in SSMS.  Yeah, that's write, I'm mentioning the GUI in this post.  Essentially, if you build your SQLCMD script is SSMS, you have an IDE for development with all the benefits of command line.  It's the best of both worlds! You can set this in Tools | Options or click the button with the Red ! in SSMS (Not the Execute button, the other one).

6. Error Handling. :Error :On Error :EXIT with the -b and -v switches.  There's pretty much all you need in some combination of these to gracefully deal with run-time problems.  That is so much better than piping out and digging through log files! 

7. :PerfTrace.  This feature lets you put your statistics (IO, TIME, ShowPlan) out to a separate file for later viewing as so not to clutter the results file output.

8. :XML ON - Put all out put to XML format

9. Performance. SQLCMD connects with the .NET SQLCLIENT OLEDB Provider instead of ODBC, which is a much better performer

10. Remote Dedicated Admin Connection (DAC).  If you use the -A switch, you can get into a server that might otherwise be hung.  You can kill an offending process and restore order to the universe - without a reboot!

You'll notice that there are a lot of new commands, almost a sub-language for SQLCMD.  It might take a little while to learn these new features, but I think it will be well worth it.

So now Oracle DBAs can't kick SQL*Plus in our faces anymore.  We have a tool that easily competes and may be even better.  Have you looked at SQLCMD yet?  Are there other features of this tool you think are important? Tell me what you think.

Jon Baker

A colleague of mine, Heidi Carmen, was complaining about not having a hot-key for changing connections in Management Studio.  She proceeded to send a message to SQLWISH@microsoft.com.  Within the hour she receieved these instructions to remedy the problem.

Control+N using the Standard keyboard layout will bring up a new window using the current connection context in the UI. We don’t have a specific keystroke for change connection. That command is available via Alt-Q + C + h – Query menu -> Connection -> Change Connection command. Now, you can do something sneaky by locating the Change Connection toolbar button and right clicking on it and selecting the Customize command. Then do the following:

• Right click on the command again (while in customize mode) and select the Image and Text option.
• Right click on the command again and click on Name and change the name from C&hange Connection -> Chan&ge Connection. This make Alt-G a hot key for the command.
• Click Close on the Customize dialog to get out of customize mode.
•  From the Query editor, you can now press Alt-G to change the connection.

 Cheers,

Bill Ramos

First, I was a bit peeved by this "feature", too, so I was glad to see that there was an answer.  Second, I was extremely surprised that someone responded so quickly to this request.  This is great!

Further investigation showed just how configurable this new interface really is. Impressive!

I'm sure I'm not the only one to have come across pet peeves, nor am I the only one to implement a workaround.  If you have one you'd like to share, let me know.

Jon Baker

Did you know that, by default, there is a server side trace running on your SQL Server 2005 instance?  No, Microsoft is not Big Brother looking over your shoulder!  At least I don't think they are in this instance anyway :)

Rather, this is a light-weight trace that is always running to collect some data about what's going on in SQL Server.  For those of you that are familiar with the old BlackBox trace that you can turn on with a server side trace, this is NOT it. 

To start a BlackBox trace use the following (thanks to http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm)

DECLARE @TraceID int

EXEC CreateTrace
@Blackbox = 1,
@OutputTraceID = @TraceID OUT

EXEC StartTrace
@TraceID

The Default Trace is something completely new that Microsoft has implemented to audit certain events in the system, which you can report on via Management Reports (More on these in a future post). 

To prove that the trace is running, execute the following

select * from ::fn_trace_getinfo(default)

You will see something like the following

traceid     property    value
----------- ----------- ----------------------------------------------------

1              1              2
1              2              E:\sql2005\MSSQL.1\MSSQL\LOG\log_4.trc
1              3             20
1              4             NULL
1              5             1

(5 row(s) affected)

What does this information mean?  Essentially, this output tells us a little bit about the trace that is currently running.  Trace Property 1 value 2 indicates that the trace file will rollover when it reaches a certain size.  That size is set in Trace Property 3, in this case, 20MB.  Property 2 is the path for the output file.  The output above shows that there are 4 trace files.  You can actually open up these trace files located in the \\...\MSSQL\Log to see what's been going on with your server.  Trace Property 4 is the stop time, which is set never to stop.  Finally Trace Property 5 indicates that the trace is currently running. For more information on trace properties and server side traces see fn_trace_getinfo and sp_trace_create in BOL.

If you are concerned that this trace is too heavy, and you want to want to get every last ounce of performance out of your SQL Server, you can turn it off.  Now, you are probably thinking you can use good ol' sp_trace_setstatus 1, 2 to stop and close the trace, right?  Well, in the immortal words of Lee Corso, "Not so fast my friend!"  If you try this, you will receive the error message

Msg 19070, Level 16, State 1, Procedure sp_trace_setstatus, Line 1
The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off.

Just as the error message says, you can use this command to turn it off

sp_configure 'default trace enabled', 0

But should you turn it off?  Before we make that determination, let's take a closer look at what is being captured.  By opening the log file in the new Profiler (which I am a huge fan of), we can see in the event matrix exactly what is being captured.  Below is a list of the events the default trace is capturing.  It appears that all columns available for the trace events are being captured. 

Database

• Data file auto grow
• Data file auto shrink
• Database mirroring status change
• Log file auto grow
• Log file auto shrink

Errors and Warnings

• Errorlog
• Hash warning
• Missing Column Statistics
• Missing Join Predicate
• Sort Warning

Full-Text

• FT Crawl Aborted
• FT Crawl Started
• FT Crawl Stopped

Objects

• Object Altered
• Object Created
• Object Deleted

Security Audit

• Audit Add DB user event
• Audit Add login to server role event
• Audit Add Member to DB role event
• Audit Add Role event
• Audit Add login event
• Audit Backup/Restore event
• Audit Change Database owner
• Audit DBCC event
• Audit Database Scope GDR event (Grant, Deny, Revoke)
• Audit Login Change Property event
• Audit Login Failed
• Audit Login GDR event
• Audit Schema Object GDR event
• Audit Schema Object Take Ownership
• Audit Server Starts and Stops

Server

• Server Memory Change

This trace seems to me to be fairly light-weight.  I'm not sure, but I think some of the auditing alerts are triggered from this trace, as well, in addition to the extremely cool, Management Reports dependence on these traces.  For me, the benefit this trace provides outweighs the relatively low overhead for it.  I'll be leaving it on.

Tell me what you think about this new feature.  Will you be leaving it on, or are you trying to squeeze the last little bit out of your SQL Server?

Jon Baker

It's almost time! The (really!) long awaited release of SQL Server 2005 is nearly here.  Are you ready?  There is so much to learn, sometimes I feel like my brain is going to implode.  Last night was one of those nights.

It started with the Central Ohio SQL Server Users group, which  I help manage.  We had a Product Teamer, Tanmoy Dutta, over live-meeting talking about Security changes in 2005.  Good stuff!  I thought he would spend much of the time talking about schemas and users, password enforcement, etc.  Instead it was heavy on encryption on the wire, authentication handshaking, and a lot of other features I hadn't heard much about yet.  Very cool.

After that, I had to race home to hear Kimberly Tripp present content for the launch event.  I'm co-presenting the Managing and Monitoring session at the Detroit, Mi. event on November 8.  This was a "How do you present this information" session.  If you've ever heard Kimberly speak, you know she puts a lot of energy into her presentations, even at 9:00 at night (I guess that is 6:00pm her time).

I'm very excited about the content for the launch event.  If you are not planning to attend one in your area, think about it.  There is a lot of really good information about how you can benefit from SQL Server 2005 immediately, even if you don't upgrade your database engine!  Profiler, and SQLCMD are great tools, with some awesome new features, many of which you can use immediately.  Management studio of course blows SEM away.  There's so much to talk about, I can't do it all in one post.  Look for future posts on new features that I'm excited about (I'm working on one for Management Reports right now!).

If you are planning to go to the Detroit event, send me an email (jon@evergreencco.com).  Maybe we can hook up and chat. 

Happy Upgrading!

Jon