Call for speak…ehm… photographers for #SQLSat230!

So, dies ist er, mein erster Blog Post in Deutsch. Und warum? Weil es um die deutsche SQL PASS “Community” geht. Und um etwas genauer zu werden: der zweite SQLSaturday in Deutschland steht an.

Wer noch keine Idee hat, worum es sich bei dem SQLSaturday handelt, der guckt sich kurz meinen vorangegangenen Blog Post an und natürlich die offizielle Seite www.sqlsaturday.com und dann SQLSaturday #230 Rheinland.

Der so genannte “Call for speakers” findet ja auf der Eventseite statt. Wer also gerne eine Präsentation zum Thema SQL Server (DBA, BI, DEV) halten möchte, der kann einen Session Vorschlag einreichen.

Ich mache hingehen den Ruf nach Fotografen. Warum? Nun, ich bin Anfang des Monats von einem Organisator (um genau zu sein: von Tillmann (b|t)) des ersten und auch des kommenden SQLSaturdays in Deutschland gefragt worden, ob ich nicht dieses Event mit der Kamera begleiten möchte.

Da ich ganz gerne mal zur Kamera greife, werde ich natürlich das Event, so gut es geht, in Fotos festhalten.

Da es an dem Tag mehrere Session Tracks geben wird und ich nicht in jeder Session zugegen sein kann suche ich ein wenig Unterstützung.

Konkret ist Folgendes geplant:

· Fotos aller Sessions

· Fotos während der Pausen des Events

· Fotos der Sponsoren

· Fotos von den Preisverleihungen (Die Sponsoren, welche mit Ständen vertreten sind, bringen gerne mal das ein oder andere Gimmick mit)

Optimal wäre es, wenn sich 2-3 Leute (gerne auch mehr) finden, die ebenfalls Spaß haben, den SQLSaturday mit der Kamera zu begleiten.

Zwei wichtige Punkte muss ich aber noch erwähnen:

· natürlich sollt Ihr auch von der Konferenz an sich etwas mitbekommen. Aber je nachdem, wie viele Freiwillige sich finden, kann es sein, dass Ihr nicht jede Wunsch-Session mitnehmen könnt. So oder so muss man hier aber noch den Session Schedule abwarten. Vielleicht passt es ja auch auf Anhieb.

· die Mitarbeit erfolgt unentgeltlich. Die SQLPass lebt ja primär von der freiwilligen Mitarbeit, daher kann Ich Euch kein Honorar anbieten. Die Leute, die mitmachen, würde ich dann aber schon auf ein Bierchen oder Ähnliches einladen

Zum Thema Equipment: Es ist keine High End Ausrüstung notwendig. Eine Kamera mit einem Standard Zoom und Blitz (oder ein lichtstarkes Objektiv) sollte passen. Wer im Besitz eines leichten Statives ist kann dieses auch mal mit einpacken. Freihandaufnahmen in etwas dunkleren Konferenzräumen sind nicht immer so einfach.

Wer also den SQLSaturday besuchen will und auch noch Spaß daran hat diesen in Fotos festzuhalten, der melde sich doch einfach kurz bei mir. Sei es hier als Kommentar oder per Email, Twitter, Xing, FB.

The 1st SQLSaturday in Germany – I was there!

A few days ago (ok, almost two weeks now) the first SQLSaturday in Germany took place.  And I can only say: it was an awesome event. And why? Well, first of all: it was completely free. You did not had to pay any attendance fee, you just had to invest some time on this Saturday. Secondly: You could choose from a variety of interesting sessions which covered BI, Dev and DBA topics. And the, in my eyes, most important reason: the chance to get in touch with a lot of skilled people, start discussions, exchange knowledge and/or experience you’ve made.

For me it was also the first time that I not only attended such an event, but also volunteered as well.

So, for me the event already started on Friday where I helped with the preparations. The main task was to pack the SQLSaturday bags, which was really time consuming. On the other hand I still had time to take a few pictures of the preparations. To get an idea, please take a look here.

The Saturday itself was, in the beginning, a bit stressful for me. Why? Well, I also helped at the registration (e.g. handing out the swag bags, badges, t-shirts)  that morning and the people came in waves. But hey, when the first sessions started at 9 a.m. everything was fine and I could also attend (with a little delay of 15 minutes ) the first session that day “Execution plans in SQL Server” by Michael Rother .

Then it was Andreas Wolter’s turn, talking about Tracing with Extended Events. Really an interesting session and a requiem for SQL Server Profiler (“Hasta la vista, Profiler”). Andreas combination of his knowledge and humour made it fun session.

Without going in too much detail, just a little sum-up of the other sessions I have visited: SSIS Change Data Capture hosted by SQL Server MVP Allan Mitchell (twitter). This session was well-attended and and a good mixture of slides and demoes.

After the lunch break (pizza

time!), I just made a quick visit to Oliver Engels and Tillmann Eitelbergs session, when they were talking about Data Quality Services, followed by Hugo Kornelis’ (blog|twitter) “Everything you always wanted to know about MERGE” session which also was one of my highlights (UPSERTS,DUPSERTS and “we get the maximum of two…. things”).  After a little photo session break where Tobiasz Koprowski and I took some pictures of all speakers it was time for the last session of that day. I went to the “Introduction to (MERGE) Replication” session by Johannes Curio.

But the SQLSaturday did not end with the last session. There were some raffles since the sponsors had a lot of prices to give and I have to say “Thanks” to Quest Software Germany for the Amazon voucher I got .

To wrap it up: The first SQL Saturday in Germany was a great experience for me. I have met many people I only knew through twitter or their community work. Just to mention a few: Torsten Schüssler, Niko Neugebauer, Hugo Kornelis.

Thanks to all the other Volunteers, it was a lot of fun working together and hopefully not the last time.

Last, but not least, thank you to all the speakers who spent their time to present at the SQLSaturday. What an awesome crowd:

A very special thanks to the most important persons of this SQLSaturday: Kostja Klein, Oliver Engels and Tillmann Eitelberg, who invested a lot of time to make this event possible.

---

P.S. You find all of my pictures from this event here.

I cannot name the 3 DMVs I use on a regular basis, but….

Did you recognize Jen McCown’s  blog post “Are you a functional DBA” last week? Well, without John Sansom’s Something for the weekend I’d probably missed Jens post.

Jen asked a lot of interesting questions and I have to confess, I couldn’t really answer all of them without hesitation. One of her questions/tasks was to name three DMVs you use on a regular basis.  Luckily I wasn’t the only one who said: “I can’t”. It was Brent Ozar (blog | twitter), the well-known community-contributor, MVP and MCM.

So, his reply regarding the DMVs was:

Name three DMVs you use on a regular basis. – I can’t. I’ve had the same scripts in use for years, and even though I use it at least three times a day, I can never remember if it’s sys.dm_db_index_usage_from_behind or whatever. That’s what script files are for. If you’re typing the DMV name by hand multiple times per day, you’re doing it wrong.

This answer made me feel a bit better and I got the idea to write this post.

To answer now Jen’s question: I cannot name the three DMVs I use regularly, but I can name the three scripts/procedures I’m using. These are:

• Glenn Berry’s Diagnostic Information queries (you’ll find them on his blog)
• Brent Ozar’s Blitz Script
• Adam Machanic’s Who is Active

To have Glenn’s script quick at hand or to call the two procedures immediately, I use the SQL Snippets functionality from the SSMS Tools Pack by Mladen Prajdić (blog|twitter). For me, this is one of the most useful features, since I can call complete code blocks just by typing in a shortcut and hit enter.

If you have the SSMS Tools Pack installed, just go to the SQL Snippets option

Here you can define your snippets and put your sql code, custom scripts etc. pp. in it.

If I now want to open the Diagnostic queries in my query window, I just type glenn28_new and I get the whole script:

I’m calling the Blitz script and Who is Active in a similar way.  Wia for example just opens
EXEC sp_whoisactive,

but with wia2 I get

EXEC sp_whoisactive @show_sleeping_spids = 2

This is just a quick example how I call my scripts I use on a regular basis.

Performance Dashboard 2012,SQL Server 2008 and a non-working Recent CPU report…

Hi all,

today I’ve spent little time installing the new SQL Server 2012 Performance Dashboard (you can find it here).  Then I picked a test server (still SQL Server 2008 R2, build .2500) and ran the setup.sql to provide all the necessary procedures for the reports.

When I tried to open the “Recent_CPU” Report by clicking on the blue bar

I got the error “A data source instance has not been supplied for the data source ‘CHECK_DEPENDENCIES’. After a refresh of the report a 2nd error showed up: Error converting data type nvarchar(max) to datetime. My first thought was: “Hey, post a quick question at ask.sqlservercentral.com and wait for answers.”  Then I got ambitious and started SQL Profiler. Since it was only a quick trace I ran the GUI. Normally I would have created a server-side trace. You still don’t know what a server-side trace is? Take a look at Stairway to Server-side Tracing.

I catched a user error saying “Error converting data type nvarchar(max) to datetime.” right after this statement:
EXEC sp_executesql @stmt=N'exec msdb.MS_PerfDashboard.usp_QueryStatsRecentActivity @WithActivitySince',@params=N'@WithActivitySince NVarChar(max)',@WithActivitySince=N'27.07.2012 19:52:17'


That was an interesting information. So I opened the recent_cpu.rdl in BIDS2008 and checked the Parameters and guess what? The parameter ‘WithActivitySince’ was declared as string. Since I already made my experience messing up reports with BIDS 2008 when the origin report was created with the 2005 XML schema I just opened the rdl file with notepad++ and changed the defined data type  from “String”

<ReportParameter Name="WithActivitySince">

 <DataType>String</DataType>

  <AllowBlank>true</AllowBlank>

  <Prompt>WithActivitySince</Prompt>

</ReportParameter>

to “DateTime”

<ReportParameter Name="WithActivitySince">

 <DataType>DateTime</DataType>

  <AllowBlank>true</AllowBlank>

  <Prompt>WithActivitySince</Prompt>

</ReportParameter>

After this little change I was able to run the recent CPU report without any problems and double-checked that the report still worked on a SQL 2012 as well.

Regards and have a great weekend

A quick thought regarding "orphaned users"

Hi there,

this is jut a quick thought (or a reminder) regarding “orphaned users”: There are several scripts out there on how to find an orphaned user account inside a database (I just stumbled across a script a few hours ago, that’s why I’m writing down these words).

Most times DBAs have to deal with an orphaned user when they restore a database to a different server. What then happens is that the SID of the user account won’t match any longer the one from the “old” server. Therefore you have to “re-map” the database user to a valid SQL Server login.  For further information take a look at BOL.

But why am I writing stuff you all already know? Well, there are a few scripts out there which do not only find orphaned users for you but also gives you the chance to delete them.  And now my reminder: You have to double check if the database user really isn’t used.

Do you’ve ever heard of a so called “loginless user”?  This is a user which only exists inside a database (e.g. there is no corresponding server account). Maybe this user is used as a replacement for an application role? That means a user connects through a application with his credentials to SQL Server, the app then initiates an “Execute as” and changes the context to our loginless user. If that’s the case then it wouldn’t be the best idea to drop that database user….

So be aware:  loginless user != orphaned user

"The specified network name is no longer available" – SQL Backup problem solved!

A few of you may (or may not) know that my colleagues and I are using Ola Hallengren’s famous Maintenance Solution. If you never heard of it before then please take a look: http://ola.hallengren.com/

For native SQL backups we have no dedicated disk space on our database servers. Instead we´re running native backups over the network and place them on a UNC path. I know that it is probably not best practice but it fits for our environment (or to quote André Kamman (blog | twitter): “It works for me!”) very well.

It is Murphy´s law that sometimes things can go wrong. So it happened on a few machines that the backup was created but finished with an error:

The operating system returned the error ’64(The specified network name is no longer available.)’ while attempting ‘FlushFileBuffers’ on ‘\\OurBackupServer\ourshare$\OurServer\OurDatabase\FULL\ OurServer_OurDatabase_FULL_20111021_041523.bak’

So we did a little bit of research and even talked to Ola Hallengren when we visited SQLBits 9. His assumption was some kind of timeout.

Since this error only occurred on some test machines we didn´t spent too much time investigating / solving the issue (we could live with it) until it happened also in a productive environment.

Then we came across a msdn forum entry (take a look here) and tried the mentioned solution to create a DWORD with name ‘Sesstimeout’ and value 360 in  HKLM\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters.

And that was the solution. Since we´ve modified the registry on the  involved SQL Servers the backups running fine without any issues.

SQLBits 9–”Oops, I did it again” or “Come together”

SQLbits 9 is over! I visited the conference (Oops, I did it) again, and again it was a great experience for me and worth the time and money.

My SQLBits “adventure” already began on Wednesday.  After a little stressful journey from Dusseldorf to Amsterdam Schiphol and then to Liverpool I was finally there, at the Adelphi Hotel. And what happened first? Nick Harshbarger from SQLSentry recognized me again and said “welcome, nice to see you”. It´s always good if you already know someone.

Once I dumped my luggage in my room it was time to get in contact with a few people. And this time it wasn’t that difficult for me since it felt quite familiar. Instead of wasting my time at a hotel bar I could even help a little bit by stuffing the “swag bags”or putting the SQLBit agendas into the plastic pockets. That was also the time when I met Mark Broadbent (twitter | blog) in person and I finally redeemed my beer token . I also had the chance to get to know Christina E. Leo (twitter|blog) since she also lend a helping hand on Wednesday evening.

All in all this was already a great evening and the conference did not even started yet…

 

Thursday – Training Day

I decided to go for “Upgrading your DBA Skills to SQL Server”. This session was hosted by Christian Bolton (twitter|blog)  one of very few Microsoft certified Architects. And Christian really did a great job when talking about the following topics:

• Introducing SQL Server Denali
• Upgrade Planning tools
• Utilizing Windows Server Core
• Security Enhancements
• Using Column Store Technology to revolutionize query performance
• ALWAYS ON Failover Clustering and other HA improvements
• ALWAYS ON Availability Groups – Deep Dive
• Advanced Troubleshooting with Extended Events

To write in detail about every module would just blast this blog entry. So here are only a few facts or interesting information taken from this session:

• Updates can be included during setup, in offline environments you can provide the setup routine a location where you have placed SQL Server relevant update packages
• Distributed Replay function (for upgrade scenarios, index tuning etc. pp.), you can now  run workload from different clients
• Windows Server Core support (keep in mind that not every component is supported, for example SSRS)
• user defined server roles (I already have so many ideas…)
• column store technology (Project Apollo) which is NOT ONLY for DWH environments, but optimized for scans
• AlwaysON Clustering and the usage of Availability Groups: yeehaaw, great stuff. Now you can have real read-only mirrors of a database (readably secondary!)
• Recovery Advisor: point the advisor to a backup folder and you’ll get a timeline of what you can restore (nice to have for those who aren´t that familiar with restore processes)

 

On Friday and Saturday I´ve visited different sessions. Here´s a short overview:

Friday

as a “light” starter I attended

• Simon Sabin’s (twitter|blog) session – “Performance tuning from the field”. Simon showed us common “bad” T-SQL practices and how to resolve them. And he really hates scalar functions.

followed by

• “READPAST & Furious: Transactions, Locking and Isolation” by Mark Broadbent. A really cool session about, well, Transactions, Locking and Isolation. I really have to try all the examples by myself. Mark also introduced his version of the lock compatibility matrix (thanks for sharing). I really like Marks approach of talking about topics he stumbled across in the past and then asked himself: What the hell happened here?

After lunch, it was time to visit one of the sponsor sessions. So I went for

• “Gruesome, Grotesque and Gory – Horrors Stories from the World” of IT presented by Quest and hosted by MVP Kevin E. Kline (twitter|blog). Like last SQLbits, it was great fun and Kevin really was an awesome host.

followed by

• “Advanced SQL Server 2008 Troubleshooting” with Klaus Aschenbrenner (twitter|blog). I used this session as a little refresh, since I visited Klaus’ full training day at SQLBits 8. But guess what? I still could learn something new. For example I wasn´t aware of the fact that a thread only exists for 4 ms on CPU and the other time is wait time (key word:Query lifecycle and total response time). Did you know that you can query the RingBuffer Connectivity to determine the authentication time for AD accounts?  Valuable from the beginning to the end. But next time Klaus needs a 90 min slot

Last session for me on Friday was

• “RoboDBA – How do you manage a large environment” by André Kamman (twitter|blog). This session also was a lot of fun (maybe again the most entertaining session). André is one of the most relaxed presenters I´ve seen so far. And I like his approach “It works for me”. I hope André doesn´t get angry if I share one of his pro tips: “First get yourself a coffee, then go to your desk” 

 

Saturday

Ok, I´m guilty. I did not attempt the first session this morning, because I was too tired (hey, there was a casino night before…)

So I started with

• “Working with Server Side Traces” presented by Christina E. Leo (twitter|blog). Christina was a little bit nervous in the beginning but for no reason. Well, there was a reason: It was the 2nd time ever that Christina was doing such a presentation. I could not believe it, since she did very well. And I got some ideas to improve my own trace scripts.

and then

• “SQL Server Upgrade: take help from tools and best practices” hosted by Satya Jayanty.   In my opinion the slides were a little bit overloaded, so you had too much to read while listening to Satya. But still there was much valuable information. I may have to watch the recordings.

Then it was time for

• “Finding the limits” by SQLCAT member Thomas Kejser (blog). What an outstanding session! Thomas showed us in a good mix of entertainment and professionalism that today IO isn´t a bottleneck anymore if you use the proper techniques.

During lunchtime it was time again for a Quest Software Trivia Quiz. A lot of interesting questions were you still could learn something.

Then the last to Sqlbits 9 sessions:

• “Through the virtual looking glass – monitoring virtualized SQL”. Gavin Payne (twitter|blog from Coeo really was a good host and there was everything in the session you need to know when you want to monitor virtualized sql servers.  Gavin already wrote a blog post, a sum up of his own session. You can find the demo scripts and the slides there was well.

• LogParser-quicker than SSIS, easier than BCP” by Jonathan Allen (twitter|blog) was the last session for this day. I never really used LogParser before although I have it on my client PC in the office. But since Jonathan showed a lot of tips and tricks I now want to give it a try. Jonathan also has a whole blog post series about LogParser.

So, these where the sessions I´ve attended this year….. or “Oops, I did it again”.

 

But what about the “Come together” in my blog headline?

Well, this year I got to know a lot of people at SQLBits. Not only during the short breaks in or between the sessions, but also at night at the awesome post event parties. And I think that´s also an important part why you should go to SQLBits. You can attend a lot of really good sessions about SQL Server, BUT you can also get in direct contact with a lot of people who share the same interest and you can exchange a lot of knowledge. And you can have a lot of fun as well  

Side note: those parties were really great. Chris Testa-O’Neill´s gig with his band Tripod was a night to remember and a lot of fun. But also the Casino night was a great idea. Ok, I lost all my tokens within 10 minutes but it´s the taking part that counts!

To get a few impressions, you can find some pics I´ve taken here.

 

So, now it´s time to thank the SQLBits committee for another awesome event. There´s a lot of blood, sweat and tears but you made it an outstanding event.

A special thank you to Mrs. & Mr. Fatherjack for the company on Saturday evening at the tapas bar. It was really a pleasure meeting you.

Also: thx to “the gang” who hang out with me after the tapas bar at “The Ship and Mitre”.

Last but not least: Mark, Thomas, Alicia, Christina & Ron, André, Klaus, Ola and so many other awesome people (look at all the pictures ). It was so much fun hanging out with you.  We will meet again at SQLBits X. <- (unwanted pun but I like it )