Subscribe: TRUNCATE TABLE master..sysdatabases
Added By: Feedage Forager Feedage Grade B rated
Language: English
create  identity primary  idol  int null  null identity  null  pass  primary key  server  speaker idol  speaker  sql server  sql 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: TRUNCATE TABLE master..sysdatabases

TRUNCATE TABLE master..sysdatabases

...and other neat SQL Server tricks

Copyright: Most Valuable Yak (Rob Volk)

PASS Summit Speaker Idol 2017

Mon, 25 Sep 2017 22:48:23 GMT

I’m extremely honored to have been chosen once again to participate in Speaker Idol at PASS Summit 2017! I’m in deep trouble too, considering the contestants this year: I’ve previously blogged about why Speaker Idol is such a great opportunity: …and have shared my notes and feedback from the first time I’ve participated: …and my good friend Shabnam wrote about her experience in 2016: …and for those interested you can see the Speaker Idol 2015 presentations on YouTube: The 2016 Speaker Idol videos are available on the PASS Summit recordings package for purchase or download from PASS. If you attended Summit that year you should be able to view them. Also check with your local SQL user group, if they are a PASS Chapter they probably have the videos available. Denny Cherry has a video chat for contestants with a lot of great advice: ...and there's also this really helpful commentary by some previous judges: If all of this seems overwhelming, don’t be alarmed! There’s a few key things I’d recommend focusing on that have helped me and others: The 5 minute time frame is the killer feature of Speaker Idol. The more attention you pay to meeting this limit the better you’ll do Aim your rehearsal to hit between 4:30 – 5:00, 4:45 is best. This leaves some buffer time for you to handle unforeseen circumstances or technical difficulties. Don’t go below 4:30 when you actually present, you can add a summary or a simple item to fill the remaining time instead. As a rehearsal aid, or even as your presentation method, consider the Ignite Talk format: Ignite talks are 5 minutes (the length for Speaker Idol presentations) but the slides automatically advance every 15 seconds (20 slides total). It’s a great format to help with timing, and it keeps the presentation flowing. You can always tweak your timing (either duplicate a slide you need more time for, or simply adjust as needed as long as you end at 5 minutes). If nothing else it’s a good starting point if you’re adjusting a longer presentation or starting fresh with new material. Zoomit! If you really dig into the advice and videos you’ll think that using ZoomIt or similar tools is clichéd and taken for granted. It’s not. Slide visibility is a consistent complaint at PASS Summit and you DO NOT want to be the lone Speaker Idol contestant with small fonts. I prefer NOT using ZoomIt. I increase the PowerPoint font sizes, and install SSMS 2016 or higher which has built-in zooming and presentation modes. I also avoid using laser pointers, instead using some underlining, color highlights or basic animation to emphasize something on the slides. Shabnam’s blog has some advice on font sizes that you should follow. Rehearse, Rehearse, Rehearse! Like the old saying “How do you get to Carnegie Hall?”, practice makes perfect. If you read my blog notes from 2014, I did much better in my 2nd round because I took a few hours to run through my presentation entirely for timing (and just before the final round too!) If you’re worried that the presentation may become rote or stale from multiple deliveries, I’m confident that it won’t, because you’ll have an audience and you will react differently in their presence. The rehearsal is there to m[...]

T-SQL Tuesday #72: Data Modeling

Tue, 10 Nov 2015 15:45:08 GMT

It's that time again: T-SQL Tuesday! This month the lovely and amazing Mickey Stuewe (b | t) is hosting and asks us to write about the trials and tribulations of data modeling. Below is a tale that occurs somewhat frequently (in my humble experience anyway), I’ve changed some of the details so as not to embarrass anyone besides myself. Any resemblance to actual data models, living or dead, is really unfortunate. (Yes, the excessive code is deliberate) I really love ice cream! I decided to open my own ice cream parlor and design the database I'd need to support it: CREATE TABLE IceCream(IceCreamID int NOT NULL IDENTITY(1,1) PRIMARY KEY, IceCreamName nvarchar(255) NOT NULL); Naturally we need to support some accessories: CREATE TABLE IceCreamToppings(IceCreamToppingID int NOT NULL IDENTITY(1,1) PRIMARY KEY, IceCreamToppingName nvarchar(255) NOT NULL); And of course we need data: INSERT IceCream(IceCreamName) VALUES ('vanilla'),('chocolate'),('strawberry'),('Neopolitano'),('pistachio'),('mint chocolate chip'); INSERT IceCreamToppings(IceCreamToppingName) VALUES ('chocolate sprinkles'),('candy sprinkles'),('chocolate chips'), ('blueberry sauce'),('whipped cream'),('butterscotch'),('m&ms'),('oreo crumbles'); Business took off into the stratosphere! My customers also love putting ice cream on breakfast foods, so I offered pancakes and waffles! CREATE TABLE Pancakes(PancakeID int NOT NULL IDENTITY(1,1) PRIMARY KEY, PancakeName nvarchar(255) NOT NULL); CREATE TABLE PancakeToppings(PancakeToppingID int NOT NULL IDENTITY(1,1) PRIMARY KEY, PancakeToppingName nvarchar(255) NOT NULL); CREATE TABLE Waffles(WaffleID int NOT NULL IDENTITY(1,1) PRIMARY KEY, WaffleName nvarchar(255) NOT NULL); CREATE TABLE WaffleToppings(WaffleToppingID int NOT NULL IDENTITY(1,1) PRIMARY KEY, WaffleToppingName nvarchar(255) NOT NULL); INSERT Pancakes(PancakeName) VALUES ('Homemade'),('frozen'); INSERT Waffles(WaffleName) VALUES ('Belgian'),('Eggo round'),('Eggo Square'); INSERT PancakeToppings(PancakeToppingName) VALUES ('chocolate chips'),('blueberries'),('blueberry sauce'),('maple syrup'),('apples'),('cinnamon sugar'),('powdered sugar'); INSERT WaffleToppings(WaffleToppingName) VALUES ('chocolate chips'),('blueberries'),('blueberry sauce'),('maple syrup'),('apples'),('cinnamon sugar'),('powdered sugar'); WOW! They loved this so much I started offering other breakfast options: CREATE TABLE Omelettes(OmeletteID int NOT NULL IDENTITY(1,1) PRIMARY KEY, OmeletteName nvarchar(255) NOT NULL); CREATE TABLE OmeletteStuffings(OmeletteStuffingID int NOT NULL IDENTITY(1,1) PRIMARY KEY, OmeletteStuffingName nvarchar(255) NOT NULL); INSERT Omelettes(OmeletteName) VALUES ('whole eggs'),('egg whites'); INSERT OmeletteStuffings(OmeletteStuffingName) VALUES ('sausage'),('mushrooms'),('green peppers'),('ham'),('spinach'),('feta cheese'),('onion'),('red peppers'); And then lunch offerings soon followed: CREATE TABLE Pizza(PizzaID int NOT NULL IDENTITY(1,1) PRIMARY KEY, PizzaName nvarchar(255) NOT NULL); CREATE TABLE PizzaToppings(PizzaToppingID int NOT NULL IDENTITY(1,1) PRIMARY KEY, PizzaToppingName nvarchar(255) NOT NULL); CREATE TABLE Burgers(BurgerID int NOT NULL IDENTITY(1,1) PRIMARY KEY, BurgerName nvarchar(255) NOT NULL); CREATE TABLE BurgerToppings(BurgerToppingID int NOT NULL IDENTITY(1,1) PRIMARY KEY, BurgerToppingName nvarchar(255) NOT NULL); CREATE TABLE Sandwiches(SandwichID int NOT NULL IDENTITY(1,1) PRIMARY KEY, SandwichName nvarchar(255) NOT NULL); CREATE TABLE SandwichToppings(SandwichToppingID int NOT NULL IDENTITY(1,1) PRIMARY KEY, SandwichToppingName nvarchar(255) NOT NULL); INSERT Pizza(PizzaName) VALUES ('deep dish'),('sicilian'),('regular'); INSERT PizzaToppings(PizzaToppingName) VALUES ('pepperoni'),('sausage'),('mushrooms'),('green peppers'),('ham'),('spinach'),('feta cheese'),('onion'),('pineapple'); INSERT Burgers(BurgerName) VALUES ('beef'),('kobe'),('angus'),('turkey'),('chicken'); INSERT BurgerToppings(BurgerToppingName) VALUES ('bacon'),('mayo'),('mustard'),('lettuce'),('tomato[...]

Notes from Speaker Idol 2014

Sat, 24 Oct 2015 23:09:17 GMT

When last I blogged, I recommended everyone who was eligible to sign up for PASS Summit Speaker Idol 2015. The contestants have been announced: They also announced the judges in case you want to try the bribery route: To help this year's participants I will be blogging my notes from when I presented for Speaker Idol 2014, and I'll also add some specifics about my experience in a separate post. I also recommend that you read up on the previous contest and some follow up from a few of the judges: The notes that follow are transcribed from handwritten notes. I'm listing anonymized comments from the judges, primarily because I haven't gotten their permission, but also to avoid bias on either theirs or the contestant's side.  In no particular order (red were items to improve, green were items that were well done, my personal comments in parentheses): Need a story about how (your presentation topic) saved you time or improved your job Mentioned great benefits (of presentation topic) without providing evidence or comparison Too much information for a 5 minute presentation Don't condense a 1 hour presentation to 5 minutes The faster you need to go, the slower you should talk (don't rush through explanation) Voice cut out when turning head away from microphone, turn whole body instead Test demos, make sure they don't fail Use scripts only, DO NOT TYPE CODE (typing is not the same as a demo) No bullets on slide template (plain formatting) Too many bullet points on slides Avoid using periods at end of bullet points (they aren't sentences) Small graphics on slides Good to paste code on slides to avoid needing SSMS, also good font and color for code Great time management with 23 seconds left (should finish between 4:30 and 5:00 minutes) Not sure what topic was about, need to make clear in beginning; not much meat in first 2 minutes Good to engage audience at beginning of 5 minute presentation ("ballsy" according to 1 judge, but not generally recommended) If asking question, be sure to answer/respond, repeat question for audio/audience, and repeat show of hands to audience Make sure images on your slides have proper attribution (author/source and license type), also for quotations/citations (several contestants were missing these) Good recovery from technical problems (projector, mouse/remote, audio, demo issues; 3 speakers were commended for this) Presentation felt like "Books Online" Presented on floor rather than podium ZoomIT use mostly poor (only 2 speakers had favorable comments, 3 had negative) Never really explained/defined the topic, rushed to demo after long intro, could have explained better within time limit Font and background color on slides hard to read (it was noted that slides were templates and presenter may not be able to change) Allow enough time for audience to read slides Don't repeat slides verbatim Good eye contact and gestures (not too big, not too small), good energy and enthusiasm Make sure to move with purpose, avoid moving without purpose Careful of posture and foot placement, don't rock on feet, don't lean against objects; don't put hands in pockets Presentation agenda was disjointed (should follow logical flow) Too much code on one slide, hard to read Wait for answers to questions (asked audience question and moved on too quickly) Laser pointer discipline, do not move it excessively, use as little as possible (more comments from me in future post) [...]

PASS Speaker Idol 2015

Mon, 28 Sep 2015 18:47:35 GMT

In 2014, PASS offered a new type of session at their Global Summit event: Speaker Idol.

Initiated by Denny Cherry (b | t), the concept is:
  • To provide an opportunity for community speakers
  • Who haven't yet presented a regular session at PASS Summit
  • A chance to present to a panel of judges - like American Idol, but without singing. (Sadly. Or not.)
  • For a guaranteed regular session at the next PASS Summit!
This is a FANTASTIC idea, and I was fortunate enough to participate that year. And I encourage EVERYONE who is eligible to participate this year and every year they can.

  • You get a chance to present at Summit, even if you don't get to the final round
  • You are getting free feedback from PASS Summit speakers
  • Who know exactly how you feel up on the stage
  • Who WANT to see you deliver a full session next year
You can find out more about the 2015 Speaker Idol here:

There is absolutely no downside.

Even if you don't get chosen, I recommend that you attend as many Speaker Idol sessions as you can. The feedback from the judges will help you become a better speaker, and would cost you hundreds of dollars from a professional speaking coach (and may not be as useful since they're probably not a PASS speaker).

What are you waiting for? You can enter using this form:


T-SQL Tuesday #53-Matt's Making Me Do This!

Tue, 08 Apr 2014 21:18:56 GMT

Hello everyone! It's that time again, time for T-SQL Tuesday, the wonderful blog series started by Adam Machanic (b|t). This month we are hosted by Matt Velic (b|t) who asks the question, "Why So Serious?", in celebration of April Fool's Day. He asks the contributors for their dirty tricks. And for some reason that escapes me, he and Jeff Verheul (b|t) seem to think I might be able to write about those. Shocked, I am! Nah, not really. They're absolutely right, this one is gonna be fun! I took some inspiration from Matt's suggestions, namely Resource Governor and Login Triggers.  I've done some interesting login trigger stuff for a presentation, but nothing yet with Resource Governor. Best way to learn it! One of my oldest pet peeves is abuse of the sa login. Don't get me wrong, I use it too, but typically only as SQL Agent job owner. It's been a while since I've been stuck with it, but back when I started using SQL Server, EVERY application needed sa to function. It was hard-coded and couldn't be changed. (welllllll, that is if you didn't use a hex editor on the EXE file, but who would do such a thing?) My standard warning applies: don't run anything on this page in production. In fact, back up whatever server you're testing this on, including the master database. Snapshotting a VM is a good idea. Also make sure you have other sysadmin level logins on that server. So here's a standard template for a logon trigger to address those pesky sa users: CREATE TRIGGER SA_LOGIN_PRIORITY ON ALL SERVER WITH ENCRYPTION, EXECUTE AS N'sa' AFTER LOGON AS IF ORIGINAL_LOGIN()<>N'sa' OR APP_NAME() LIKE N'SQL Agent%' RETURN; -- interesting stuff goes here GO   What can you do for "interesting stuff"? Books Online limits itself to merely rolling back the logon, which will throw an error (and alert the person that the logon trigger fired).  That's a good use for logon triggers, but really not tricky enough for this blog.  Some of my suggestions are below: WAITFOR DELAY '23:59:59';   Or: EXEC sp_MSforeach_db 'EXEC sp_detach_db ''?'';'   Or: EXEC msdb.dbo.sp_add_job @job_name=N'`', @enabled=1, @start_step_id=1, @notify_level_eventlog=0, @delete_level=3; EXEC msdb.dbo.sp_add_jobserver @job_name=N'`', @server_name=@@SERVERNAME; EXEC msdb.dbo.sp_add_jobstep @job_name=N'`', @step_id=1, @step_name=N'`', @command=N'SHUTDOWN;'; EXEC msdb.dbo.sp_start_job @job_name=N'`';   Really, I don't want to spoil your own exploration, try it yourself!  The thing I really like about these is it lets me promote the idea that "sa is SLOW, sa is BUGGY, don't use sa!".  Before we get into Resource Governor, make sure to drop or disable that logon trigger. They don't work well in combination. (Had to redo all the following code when SSMS locked up) Resource Governor is a feature that lets you control how many resources a single session can consume. The main goal is to limit the damage from a runaway query. But we're not here to read about its main goal or normal usage! I'm trying to make people stop using sa BECAUSE IT'S SLOW! Here's how RG can do that: USE master; GO CREATE FUNCTION dbo.SA_LOGIN_PRIORITY() RETURNS sysname WITH SCHEMABINDING, ENCRYPTION AS BEGIN RETURN CASE WHEN ORIGINAL_LOGIN()=N'sa' AND APP_NAME() NOT LIKE N'SQL Agent%' THEN N'SA_LOGIN_PRIORITY' ELSE N'default' END END GO CREATE RESOURCE POOL SA_LOGIN_PRIORITY WITH ( MIN_CPU_PERCENT = 0 ,MAX_CPU_PERCENT = 1 ,CAP_CPU_PERCENT = 1 ,AFFINITY SCHEDULER = (0) ,MIN_MEMORY_PERCENT = 0 ,MAX_MEMORY_PERCENT = 1 -- ,MIN_IOPS_PER_VOLUME = 1 ,MAX_IOPS_PER_VOLUME = 1 -- uncomment for SQL Server 2014 ); CREATE WORKLOAD GROUP SA_LOGIN_PRIORITY WITH ( IMPORTANCE = LOW ,REQUEST_MAX_MEMORY_GRANT_PERCENT = 1 ,REQUEST_MAX_CPU_TIME_SEC = 1 ,REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 1 ,MAX_DOP = 1 ,GROUP_MAX_REQUESTS = 1 ) USING SA_LOGIN_PRIORITY; ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.SA_LOGIN_PRIORITY); ALTER RESOURC[...]

Full-text Indexing Books Online

Tue, 17 Sep 2013 22:33:04 GMT

While preparing for a recent SQL Saturday presentation, I was struck by a crazy idea (shocking, I know): Could someone import the content of SQL Server Books Online into a database and apply full-text indexing to it?  The answer is yes, and it's really quite easy to do. The first step is finding the installed help files.  If you have SQL Server 2012, BOL is installed under the Microsoft Help Library.  You can find the install location by opening SQL Server Books Online and clicking the gear icon for the Help Library Manager.  When the new window pops up click the Settings link, you'll get the following: You'll see the path under Library Location. Once you navigate to that path you'll have to drill down a little further, to C:\ProgramData\Microsoft\HelpLibrary\content\Microsoft\store.  This is where the help file content is kept if you downloaded it for offline use. Depending on which products you've downloaded help for, you may see a few hundred files.  Fortunately they're named well and you can easily find the "SQL_Server_Denali_Books_Online_" files.  We are interested in the .MSHC files only, and can skip the Installation and Developer Reference files. Despite the .MHSC extension, these files are compressed with the standard Zip format, so your favorite archive utility (WinZip, 7Zip, WinRar, etc.) can open them.  When you do, you'll see a few thousand files in the archive.  We are only interested in the .htm files, but there's no harm in extracting all of them to a folder.  7zip provides a command-line utility and the following will extract to a D:\SQLHelp folder previously created: 7z e –oD:\SQLHelp "C:\ProgramData\Microsoft\HelpLibrary\content\Microsoft\store\SQL_Server_Denali_Books_Online_B780_SQL_110_en-us_1.2.mshc" *.htm Well that's great Rob, but how do I put all those files into a full-text index? I'll tell you in a second, but first we have to set up a few things on the database side.  I'll be using a database named Explore (you can certainly change that) and the following setup is a fragment of the script I used in my presentation: USE Explore; GO CREATE SCHEMA help AUTHORIZATION dbo; GO -- Create default fulltext catalog for later FT indexes CREATE FULLTEXT CATALOG FTC AS DEFAULT; GO CREATE TABLE help.files(file_id int not null IDENTITY(1,1) CONSTRAINT PK_help_files PRIMARY KEY, path varchar(256) not null CONSTRAINT UNQ_help_files_path UNIQUE, doc_type varchar(6) DEFAULT('.xml'), content varbinary(max) not null); CREATE FULLTEXT INDEX ON help.files(content TYPE COLUMN doc_type LANGUAGE 1033) KEY INDEX PK_help_files; This will give you a table, default full-text catalog, and full-text index on that table for the content you're going to insert.  I'll be using the command line again for this, it's the easiest method I know: for %a in (D:\SQLHelp\*.htm) do sqlcmd -S. -E -d Explore -Q"set nocount on;insert help.files(path,content) select '%a', cast(c as varbinary(max)) from openrowset(bulk '%a', SINGLE_CLOB) as c(c)" You'll need to copy and run that as one line in a command prompt.  I'll explain what this does while you run it and watch several thousand files get imported: The "for" command allows you to loop over a collection of items.  In this case we want all the .htm files in the D:\SQLHelp folder.  For each file it finds, it will assign the full path and file name to the %a variable.  In the "do" clause, we'll specify another command to be run for each iteration of the loop.  I make a call to "sqlcmd" in order to run a SQL statement.  I pass in the name of the server (-S.), where "." represents the local default instance. I specify -d Explore as the database, and -E for trusted connection.  I then use -Q to run a query that I enclose in double quotes. The query uses OPENROWSET(BULK…SINGLE_CLOB) to open the file as a data source, and to treat it as a single character large object.  In order for full-text indexing to work pro[...]

Criminals and Other Illegal Characters

Wed, 19 Jun 2013 20:50:17 GMT

SQLTeam's favorite Slovenian blogger Mladen (b | t) had an interesting question on Twitter: I liked Kendal Van Dyke's (b | t) reply: And he was right!  This is one of those pretty-useless-but-sounds-interesting propositions that I've based all my presentations on, and most of my blog posts. If you read all the replies you'll see a lot of good suggestions.  I particularly like Aaron Bertrand's (b | t) idea of going into the Unicode character set, since there are over 65,000 characters available.  But how to find an illegal character?  Detective work? I'm working on the premise that if SQL Server will reject it as a name it would throw an error.  So all we have to do is generate all Unicode characters, rename a database with that character, and catch any errors. It turns out that dynamic SQL can lend a hand here: IF DB_ID(N'a') IS NULL CREATE DATABASE [a]; DECLARE @c INT=1, @sql NVARCHAR(MAX)=N'', @err NVARCHAR(MAX)=N''; WHILE @c<65536 BEGIN BEGIN TRY SET @sql=N'alter database ' + QUOTENAME(CASE WHEN @c=1 THEN N'a' ELSE NCHAR(@c-1) END) + N' modify name=' + QUOTENAME(NCHAR(@c)); RAISERROR(N'*** Trying %d',10,1,@c) WITH NOWAIT; EXEC(@sql); SET @c+=1; END TRY BEGIN CATCH SET @err=ERROR_MESSAGE(); RAISERROR(N'Ooops - %d - %s',10,1,@c,@err) WITH NOWAIT; BREAK; END CATCH END SET @sql=N'alter database ' + QUOTENAME(NCHAR(@c-1)) + N' modify name=[a]'; EXEC(@sql); The script creates a dummy database "a" if it doesn't already exist, and only tests single characters as a database name.  If you have databases with single character names then you shouldn't run this on that server. It takes a few minutes to run, but if you do you'll see that no errors are thrown for any of the characters.  It seems that SQL Server will accept any character, no matter where they're from.  (Well, there's one, but I won't tell you which. Actually there's 2, but one of them requires some deep existential thinking.) The output is also interesting, as quite a few codes do some weird things there.  I'm pretty sure it's due to the font used in SSMS for the messages output window, not all characters are available.  If you run it using the SQLCMD utility, and use the -o switch to output to a file, and -u for Unicode output, you can open the file in Notepad or another text editor and see the whole thing. I'm not sure what character I'd recommend to answer Mladen's question.  I think the standard tab (ASCII 9) is fine.  There's also several specific separator characters in the original ASCII character set (decimal 28-31). But of all the choices available in Unicode whitespace, I think my favorite would be the Mongolian Vowel Separator.  Or maybe the zero-width space. (that'll be fun to print!)  And since this is Mladen we're talking about, here's a good selection of "intriguing" characters he could use.[...]

SQL Saturday #220 - Atlanta - Pre-Con Scholarship Winners!

Tue, 07 May 2013 19:18:36 GMT

A few weeks ago, AtlantaMDF offered scholarships for each of our upcoming Pre-conference sessions at SQL Saturday #220. We would like to congratulate the winners!

David Thomas SQL Server Security
Vince Bible Surfing the Multicore Wave: Processors, Parallelism, and Performance
Mostafa Maged Languages of BI
Daphne Adams Practical Self-Service BI with PowerPivot for Excel
Tim Lawrence The DBA Skills Upgrade Toolkit

Thanks to everyone who applied! And once again we must thank Idera's generous sponsorship, and the time and effort made by Bobby Dimmick (w|t) and Brian Kelley (w|t) of Midlands PASS for judging all the applicants.

Don't forget, there's still time to attend the Pre-Cons on May 17, 2013! Click on the EventBrite links for more details and to register!

(image)     (image) (image)

SQL Saturday #220 - Atlanta - Pre-Conference Scholarships!

Mon, 22 Apr 2013 19:59:56 GMT

  We Want YOU…To Learn! AtlantaMDF and Idera are teaming up to find a few good people. If you are: A student looking to work in the database or business intelligence fields A database professional who is between jobs or wants a better one A developer looking to step up to something new On a limited budget and can’t afford professional SQL Server training Able to attend training from 9 to 5 on May 17, 2013 AtlantaMDF is presenting 5 Pre-Conference Sessions (pre-cons) for SQL Saturday #220! And thanks to Idera’s sponsorship, we can offer one free ticket to each of these sessions to eligible candidates! That means one scholarship per Pre-Con! One Recipient Each will Attend: Denny Cherry: SQL Server Security Adam Machanic: Surfing the Multicore Wave: Processors, Parallelism, and Performance Stacia Misner: Languages of BI Bill Pearson: Practical Self-Service BI with PowerPivot for Excel Eddie Wuerch: The DBA Skills Upgrade Toolkit If you are interested in attending these pre-cons send an email by April 30, 2013 to and tell us: Why you are a good candidate to receive this scholarship Which sessions you’d like to attend, and why (list multiple sessions in order of preference) What the session will teach you and how it will help you achieve your goals The emails will be evaluated by the good folks at Midlands PASS in Columbia, SC. The recipients will be notified by email and announcements made on May 6, 2013. GOOD LUCK! P.S. - Don't forget that SQLSaturday #220 offers free* training in addition to the pre-cons! You can find more information about SQL Saturday #220 at View the scheduled sessions at and register for them at * Registration charges a $10 fee to cover lunch expenses.[...]

Database Mirroring on SQL Server Express Edition

Mon, 01 Apr 2013 23:00:39 GMT

Like most SQL Server users I'm rather frustrated by Microsoft's insistence on making the really cool features only available in Enterprise Edition.  And it really doesn't help that they changed the licensing for SQL 2012 to be core-based, so now it's like 4 times as expensive!  It almost makes you want to go with Oracle.  That, and a desire to have Larry Ellison do things to your orifices. And since they've introduced Availability Groups, and marked database mirroring as deprecated, you'd think they'd make make mirroring available in all editions.  Alas…they don't…officially anyway.  Thanks to my constant poking around in places I'm not "supposed" to, I've discovered the low-level code that implements database mirroring, and found that it's available in all editions! It turns out that the query processor in all SQL Server editions prepends a simple check before every edition-specific DDL statement: IF CAST(SERVERPROPERTY('Edition') as nvarchar(max)) NOT LIKE '%e%e%e% Edition%' print 'Lame' else print 'Cool' If that statement returns true, it fails. (the print statements are just placeholders)  Go ahead and test it on Standard, Workgroup, and Express editions compared to an Enterprise or Developer edition instance (which support everything). Once again thanks to Argenis Fernandez (b | t) and his awesome sessions on using Sysinternals, I was able to watch the exact process SQL Server performs when setting up a mirror.  Surprisingly, it's not actually implemented in SQL Server!  Some of it is, but that's something of a smokescreen, the real meat of it is simple filesystem primitives. The NTFS filesystem supports links, both hard links and symbolic, so that you can create two entries for the same file in different directories and/or different names.  You can create them using the MKLINK command in a command prompt: mklink /D D:\SkyDrive\Data D:\Data mklink /D D:\SkyDrive\Log D:\Log This creates a symbolic link from my data and log folders to my Skydrive folder.  Any file saved in either location will instantly appear in the other.  And since my Skydrive will be automatically synchronized with the cloud, any changes I make will be copied instantly (depending on my internet bandwidth of course). So what does this have to do with database mirroring?  Well, it seems that the mirroring endpoint that you have to create between mirror and principal servers is really nothing more than a Skydrive link.  Although it doesn't actually use Skydrive, it performs the same function.  So in effect, the following statement: ALTER DATABASE Mir SET PARTNER='TCP://' Is turned into: mklink /D "D:\Data" "\\\5022$" The 5022$ "port" is actually a hidden system directory on the principal and mirror servers. I haven't quite figured out how the log files are included in this, or why you have to SET PARTNER on both principal and mirror servers, except maybe that mklink has to do something special when linking across servers.  I couldn't get the above statement to work correctly, but found that doing mklink to a local Skydrive folder gave me similar functionality. To wrap this up, all you have to do is the following: Install Skydrive on both SQL Servers (principal and mirror) and set the local Skydrive folder (D:\SkyDrive in these examples) On the principal server, run mklink /D on the data and log folders to point to SkyDrive: mklink /D D:\SkyDrive\Data D:\Data On the mirror server, run the complementary linking: mklink /D D:\Data D:\SkyDrive\Data Create your database and make sure the files map to the principal data and log folders (D:\Data and D:\Log) Viola! Your databases are kept in sync on multiple servers! One wrinkle you will encounter is that the mirror server will show the data and log f[...]