SQL Server - Admin
Somebody has to keep those devs in check
Two of the cool new features that we talk about in Essential SQL Server 2005 and Guerrilla SQL Server 2005 are:
- Data Definition Language (DDL) Triggers, which allow us to
catalog code that gets fired synchronously with event that trigger it. This is
useful in cases where you want to hook into the processing sequence for
commands like CREATE TABLE, ALTER VIEW or DROP ASSEMBLY, potentially logging
who did what or issuing a ROLLBACK to cancel that work.
- EVENT NOTIFICATIONS, which route information about events
asynchronously to Service Broker queue after the event has been processed.
EVENT NOTIFICATIONS are available for 193 specific events, including the events
we can use for DDL triggers.
This processing behavior difference is critical. EVENT NOTIFICATIONS
cannot easily be used to cancel work before it gets done, which a DDL trigger
can do. On the other hand, DDL triggers can’t be written for all 193 possible
events. So there are times we find ourselves wanting to use a synchronous
trigger for non-DDL events. For example, consider the routine situation where --
at the end of the month -- a given SQL Server needs to be dedicated to running
long-running and demanding batches and procedures to close out the financial
records. Normally, a "black-out" period is set by management for some interval (like
8:00 PM on the last day of the month) until work resumes the next day. The
question becomes: how can we easily automate the enforcement of this policy?
Well, if we could inject code into the logon process, it
would be easy. However, SQL Server hasn’t really made it easy to do that. At
least before Service Pack Two for SQL Server 2005. In this service pack a new
type of trigger is introduced: the LOGON TRIGGER. Let’s see how they work by
creating one.
What I’d like to do for auditing and troubleshooting
purposes is first create a database with just one table in it that allows me to
record logon activity. There’s nothing new about coding that up:
use master
go
if not db_id('audit') is null
drop database audit
go
create database audit
go
use audit
go
create schema admin
go
create table admin.LogonAudit(eventData xml,denied bit default 0)
go
use master
go
Let’s also create a user to test with:
if not suser_sid('userLogin') is null
drop login userLogin
go
create login userLogin with password = 'someEasyPassword'
go
Our trigger is actually fairly simple to write:
-- Drop our trigger just in case an older version exists...
if exists(select * from master.sys.server_triggers where name like 'noUserLogins%')
drop trigger noUserLoginsDuringMonthEnd on all server
go
-- Name it
create trigger noUserLoginsDuringMonthEnd
-- Applied at the instance level
on all server
-- Execute this code as the person who cataloged it
with execute as self
-- New, fires when a logon is attempted
for logon
as begin
-- Get the range of the backout period
-- starts on the last day of the month at 8PM
-- and ends at 9 hours later
declare @bob datetime,@boe datetime
select @bob = dateadd(m,1,getdate())
select @bob = convert( datetime,
cast(datepart(yyyy,@bob) as char(4))+'-'+
cast(datepart(MM,@bob) as char(2))+'-'+
'01 00:00:00'+' ', 120)
set @bob = dateadd(hh,-4,@bob)
set @boe = dateadd(hh,9,@bob)
-- If its currently in the back period and
-- a disallowed login is tried...
if (getdate() between @bob and @boe) and
(original_login() = 'userLogin')
begin
-- Stop the login
rollback
-- Record the attempt
insert into audit.admin.logonAudit values (eventData(),1)
-- See what happens to messages...
raisError('Attempted user login during month-end blackout',16,1) with log
end else
-- Record the attempt
insert into audit.admin.logonAudit values (eventData(),0)
end
go
So what happens if somebody tries to logon using the
userLogin we created during the black-out period? They get a message like this:
Personally, I have mixed feelings about this. It is great
because it works and does a nice job NOT displaying my potentially
security-sensitive error message (from RaisError) to the user. However, the
error message shown gives away two pieces of information it doesn’t need to:
the fact that a trigger nixed the log-on ("due to trigger execution") and that
there might very well be some database on the system called "master." I’m not
really sure why the SQL Server team decided to have this dialogue show those,
so I filled a bug about it. If you agree that this isn’t good, you can show
your support by voting for my bug by voting in it at https://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=237008
That said there’s no doubt that this is a very nice addition
to SQL Server 2005 and your bag of tricks.
Just in case you've not already read it on Gert's or Cameron's blogs, CTP6 for "Visual Studio Team Edition For The Database Professional" -- AKA DataDude -- just got released. If things hold true to form, this will hopefully be the last CTP before we start seeing RCs. Looks to me like they've addressed a number of the things that kept me from using "Dude" as my primary SQL Development environment. Gert listed the improvements as:
- Full support for SQL Server 2000 & 2005 objects, the parser work has been completed (so I can't wait to test it again with SQLCLR Assemblies and Service Broker code).
- Extended Properties support, we know import and deploy all your extended properties (so my database dictionary programs will resume working again).
- Inline constraint support, if you do not want to separate them out, we allow them inline as well (cool for check constraints)
- Pre- and post deployment scripts population during Import Script
- Full support for command line build & deploy and Team Build
- A new Import Schema Wizard which is also integrated with the New Project Wizard to make project creation and import a single stop shop (Thank goodness!)
- Synchronize your database project from Schema Compare, compare your project with a database and pull the differences in to the database project
- Schema refactoring is now allowed even if you have files in a warning state
- Resolve 3 and 4 part name usage when the referenced database is locally present, same for linked servers
- The product no longer installs SQL Express; you can pick your own SQL Server 2005 Developer Edition or SQL Server 2005 Enterprise Edition instance on the local box. When you first start the product for the first time we will ask you to choose an local instance to use (YEAH!)
- Display detailed Schema Object properties in the VS Property Window for selected objects in the Schema View (Can't wait to kick the tires on this)
- Separation of user target database settings through user project files, this allows users to work against a different target instance without changing the main project file.
- We made great progress on the overall stability and performance of the product across the board, project creation, importing your schema, reloading project and making changes to your schema
- And last but not least we fixed many reported customer problems!
You can read the full text of Gert's announcement here or get this bits directly from here.
I think of one the most mystifying error messages that you can get in SQL Server 2005 is when try to catalog the same assembly twice into the same database:
Msg 6285, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name "...".
This raises a couple of interesting questions.
a.) What the heck is an MVID and why do we care?
b.) How can we check these MVIDs for ourselves?
a.) MVID stands for Module Version ID and is documented in section 21.27 of ECMA-335 as "simply a Guid used to distinguish between two versions of the same module." Although an Assembly can contain many modules, there is a principal MVID assigned to it in the manifest -- the Manifest MVID. One of the checks that SQL Server performs when cataloging an new assembly is to get it's Manifest MVID and compare that to all of the other Manifest MVIDs for assemblies already cataloged in the current database. When a duplicate is found, SQL Server 2005 rejects the inbound assembly.
b.) Checking any non-database assembly for its Manifest MVID is fairly simple. Here's a short snip of code that returns it:
Assembly asm = Assembly.Load(BUF);
Module m = asm.ManifestModule;
return m.ModuleVersionId;
Where BUF is assumed by an array of bytes holding the assembly you want the MVID for. You could also use Assembly.LoadFromFile to an exsiting Assembly-as-DLL file into the snip, however, that method is not allowed within SQLCLR. An easier way to get the MVID for an Assembly already cataloged into SQL Server 2005 is to use the AssemblyProperty statement in T-SQL, e.g.:
select assemblyproperty(assembly name,'mvid')
Time to fire up your favorite RSS feed reader and get to subscribing:
http://blogs.msdn.com/sqlqueryprocessing/rss.xml
No SQL Developer should leave home without it.
A commonly asked question is "how can I load an existing XSD file into an XML Schema Collection?" It's pretty easy:
declare @don xmlselect @don=bulkcolumn from openrowset(bulk 'c:\users\ktegels\some.xsd',single_blob) as screate xml schema collection dbo.collectionName as @dongoSubsituting the location for your file following the BULK provider in the OpenRowSet source.
Are you Datamart Builder with few bones to pick? Are your cubes too slow?
Ok, so I can't do anything to help you with that. But you can help Microsoft help you. You see, they are in the planning phase for the next version of SQL Server. Your input counts! Visit the SQLTIPs blog for a link to the online surveys.
One of the three best PSS SQL Server Engineers -- Bob Ward -- has written up a great set of white papers on SQL Server I/O. More about them on the SQL Blog.
A few months ago, I published a step-by-step procedure for using certificate to sign an assembly so an external access or unsafe assembly could be run without having to set the hosting database to trustworthy. The major problem with that process was its weight: a lot of steps and some still requirements in terms of certificate management. Turns out that yes, there is an easier way to make it work.
Part one -- preparing the master database
- As needed, start a series of queries against the MASTER database.
- Create the target database (meaning execute a CREATE DATABASE query).
- Code and compile the assembly you want to deploy. Make sure you sign the assembly with a strong name key file. This can be done with a PFX file generated by Visual Studio.
- Create an asymmetric key from from the compiled assembly using a statement like this: create asymmetric key <key_name> from executable file = '<path_to_dll_file>'
- Create a login based on that asymmetric key using a statement like this: create login <login_name> from asymmetric key <key_name>
- Grant that login the right to create either or both an unsafe or external access assembly (as needed) using: grant unsafe assembly to <login_name>
Part two -- preparing the hosting database
- As needed, start a series of queries against the desired user database.
- Create a user in that database mapped to the login created in part, step 5. (e.g. create user <db_user> from login <login_name>)
- Give that user the right to catalog an assembly, e.g.: grant create assembly to <db_user>
- Catalog the desired assembly using the now trusted asymmetric key with a statement like: create assembly <assembly_name> authorization <db_user> from '<path_to_dll_file>' with permission_set = unsafe
From there on, its just a matter of mapping your methods and classes to the desired T-SQL objects.
Note: the following is just to help Google find this post.
is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. RegistryReader
"order has shipped."
My copy of Bob and Dan's "A Developer's Guide to SQL Server 2005 has finally departed from whatever cave Amazon loaded them into. Can't wait to see how different it is. Its going to be a lot of reading too, its accompanied by Johnson's ADO.NET Advanced Topics book that I'm also looking forward too.
Is it time for me to take up the writing effort again? Maybe. I've got ideas, but what would you be interested in?
Doh! Sorry to be late getting this on the wire but the Heartland Developers' Conference for 2006 is has been announced. This year its in Omaha and runs from 26 October and 27 October. Hopefully I won't find myself in China again...
In its 3rd year, HDC06 is the largest independent annual professional Microsoft development conference in the nation, organized by user groups, and focused on .NET development and other emerging Microsoft technologies. HDC provides regional developers the opportunity to experience sessions usually reserved for Tech-Ed and PDC at a much lower cost while retaining the same nationally known presenters. Over 2 days, 23 sessions, 2 tracks, and several networking opportunities, HDC06 is where you’re find top knowledge experts that can prepare you for next level development.
For only $125 Early Bird/$175 standard, each Attendee Pass includes:
1 Conference pass to 2 keynotes and all 23 ninety minute sessions
- Meals including breakfast, lunch, snacks, and coffee breaks
- Access to the “Developer Lounge” expo and activity center
- Wireless internet access throughout conference area
- Pre conference networking party including drinks and food
- Thursday night “Developer Jam” including food, drink, and a casino for prizes!
- Attendee Kit and a chance to win a monster 64 Bit Alienware computer
The speaker list is looking awesome too: Microsoft's own Jeff Brand, Don Bryner and Jacob Cynamon. MVPs Dave Donaldson, Robert Hurlbut, Rocky Lhotka, Javier Lozano and Andrew Troelsen and community rockstars Craig Utley, Phil Wolfe, Robert Boedigheimer, Tim Gifford, Matt Milner and Nick Parker. I'm planning of giving a couple of talks on SQL Server 2005 for Developers.
Register here. And remember, we're talking a mere $125 one of the best Microsoft-focused technical conferences in the Midwest.
Conor Cunnigham kicks it off with:
On behalf of the Query Optimization Team for Microsoft's SQL Server product, welcome to our humble virtual abode. We decided that we'd start a blog to help people better understand query plans, physical schema design in databases, making your application perform better, and anything else related to query optimization. Furthermore, we'll be posting tips and tricks that we learn from our work with customers so that others can benefit from those exchanges.
They are coming out of the gate strong, too. There's already an article up about usimg Computed Columns to optimize certain types of queries in SQL Server 2005.
RSS Feed Here.
As usual, some of the best questions come from the newsgroups. And yet again, there was one that piqued by attention. The question essentially boiled down to “can you put a trigger on a login?” That is, the questioner wanted to execute some arbitrary T-SQL code whenever a user logged into a given instance of SQL Server. I’m not sure how you’d go about doing this in SQL Server 2000, but in 2005, two features make this possible – if you don’t mind building a bit of your own plumbing. These features are EVENT NOTIFICATIONS and the Service Broker.
Event Notifications are essentially fixtures that your create – using T-SQL – that allows SQL Server to dispatch messages about system events like logins, data definition language statements, and some trace events to Service Broker Services and Queues. The Service Broker Service acts as an address for the messages, while the Queue holds the messages until some a process like a Stored Procedure process them. Some number of instances of that Stored Procedure will be spawned by a process called Activation when a message arrives in a Queue. So these two features work together to deliver information about system events to a stored procedure that gets executed in response to that event, much like how a trigger gets fired in response to events.
What started out as fairly short post turned into a fairly long item, so I've contued this over here, while the annotated source code (with bonus features) is over here.
LAMP: Linux, Apache, MySQL and PHP. A pretty nice combination of technologies for easily building interactive and Database-backed Websites. I love the concept, its one that Microsoft didn't really captialize on with .NET 1.x. Sure, you had Windows as the OS, not free, but affordable for businesses. You also have IIS which is easy enough to configure and has improved considerable with the introduction of IIS6. And, yes, ASP.NET 1.x was most certainly a great web applicaiton platform. But where was the bit that that competed with MySQL? Well, that was MSDE. Enough said. Little wonder why there's been a lot of interest in SQL Server 2005 Express Edition.
But something about that offering just didn't make out the gate in time. Namely:
- Like the MSDE story, there wasn't a free GUI for working with yet. For better or worse, XM didn't make it, and SSMSEE wasn't ready at RTM. I'm glad they didn't hold up for it, but its still somewhat holding the ASP.NET 2.0 platform for going head-to-head with LAMP, IMHO.
- Towards the end of the development cycle of SQL Server 2005, the SQL Team decided that Express Edition really should support Reporting Services and Full-Text search. However, that decision was really made too late in the cycle to include them in the RTM. Microsoft said these features would be available with SP1.
And so they are. Mission almost accomplished. It turns out that a CTP version of SSMSEE is now available at [0], so you can start getting a feel for how tool will help you write great applications. Books On-Line has also been updated to reflect the changes in it. You can download that from [1]. You also download the first CTP for Express Edition with Advanced Services from [2].
I believe the March 2006 CTP version of SQL Server 2005 will show as 9.0.2040.0. Don't worry, it still runs with .NET FX 2.0.50727.42.
[0]: http://www.microsoft.com/downloads/details.aspx?familyid=82afbd59-57a4-455e-a2d6-1d4c98d40f6e&displaylang=en
[1]: http://www.microsoft.com/downloads/thankyou.aspx?familyId=19db0b42-a5b2-456f-9c5c-f295cdd58d7a&displayLang=en&oRef=http%3a%2f%2fwww.microsoft.com%2fsql%2fctp_sp1.mspx
[2]: http://www.microsoft.com/downloads/details.aspx?familyid=57856cdd-da9b-4ad0-9a8a-f193ae8410ad&displaylang=en
Oh, did I mention that if you want to win yourself $10,000 in the Made In Express contest, you can? Sounds like a heck of an opportunity for somebody not only write some really cool code but also put some green in bank.
As Greg and Wally have reported, I've been thinking about bringing back Take Outs as a PodCast. I'm pleased to announce with the the hosting support offered by Steve Wynkoop of SSWUG, its now a reality. The need-to-knows:
The RSS feed: http://feeds.feedburner.com/sswugorgradio
iTunes friendly feed: http://feeds.feedburner.com/SswugorgRadio?format=pcast
Episode one show notes and download file.
Let's roll!
The start of the new year always makes me think I want to do new things. This year I'm trying to eat more healthily, drink less and blog more. While you probably don't care so much about the first two of those, you're probably interested in the third. Afterall, consider what your doing right now...
Effective today, I'm shifting my blog focus from SQLJunkies (what you're reading right now) over to GeeksWithBlogs. Here's the new feeds and their RSS URLs.
I'll keep posting references to stuff about .NET and SQL Server on this site for some time to come, of course.
Just in case you live under a rock (like I seem to have been doing recently), my DevelopMentor cohort Niels Berglund has shipped updated versions of his custom SqlClr Project and his GUI for Service Broker. Go check 'em out.
Thanks to fellow DevelopMentor "Brain" Kevin Jones (RSS) for pointing this one out.
These problems left me stranded for days, then today, on another machine, I hit a different set of problems I kept getting the following "Failed to generate user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." and if I set "User Instance" to false the following "An attempt to attach an auto-named database for file C:\[app_path]\ASPNetDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
..
If you don't want to follow the link, the short answer is that SQLExpress creates a directory per user in "c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" that it uses to store information. Deleting this directory has fixed both of my problems.
Sorry, I couldn't resist. And if you're a Perl Monger like me, you shouldn't either. SQL Server MVP Erland Sommarskog has just spun out MSSQL::OlleDB, a mod for working with MSSQL Server using OLE-DB (thus the name). There's a ton of documentation provided too, with examples.
Enjoy!
Yep, Wally went and did it. I'm his guest on this weeks installment of the ASP.NET PodCast. We did this interview a while ago, so there's a few things I wanted to add/update/correct: