"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 )

Keep in mind: side by side installation SQL 2K8 – SQL 2K8 R2 & Replication Components

Hi all,

this is just a quick blog post as a short reminder regarding side by side installations. I just want to share my little experience I made yesterday. Maybe it is obvious for most of you, for me it was a little surprise.

Most of the database servers I support are SQL Server 2008 installations. Some of these also have snapshot replication enabled.

To prepare the launch of a new application / migration of an existing application we made a side by side installation. Beside an existing SQL Server 2008 there´s now a SQL Server 2008 R2 as well. And guess what? After the installation of  SQL 2K8 R2, replication jobs were failing on the SQL 2K8 instance. Also, in the job history of the corresponding SQL Agent Jobs there was an entry about a missing file. After a little research I figured out that the distrib.exe was no longer available.

So what happened? The R2 instances where installed without the SQL Server Replication components since these are no longer needed for the new application. So the installer  (for whatever reason) removed the existing replication components which where used by the SQL 2K8 instances.

The good thing is that I could easily reinstall the Replication components and now the replication jobs are running fine again.

SSMS Toolspack 2.0 is coming !

Long time no blog post….

But here´s an interesting announcement which I captured yesterday on Twitter:

What? You still do not know the recent SSMS ToolsPack by SQL Server MVP Mladen Prajdic ( blog | twitter ) ??

Then it is time to visit the webpage and take a look at the different features the tool can offer to you.

SQLBits 8–My review of an awesome event

So that´s it. Thursday afternoon and I´m still amazed about this great week I had. Or let´s say: this extended weekend. The weather god was on our side and I nearly got a sunburn. You really have to think about it: 4 days at England´s seaside and sunshine every day. It felt almost like holidays. But there was also a lot to learn as well.

I´ve registered for the whole conference and I have to say “thank you” to the organizers (especially Martin Bell (blog)) who made it possible that I could attend at Klaus’ (blog|twitter) all day session although it was already full. They just got a bigger conference room in the hotel next door. Great!

 

Thursday – Training Day!

So, the Thursday was all about SQL Server Monitoring and Troubleshooting. Klaus did a great session covering a lot of different topics which I cannot describe all in detail. I have written 8 pages of session notes and have to rethink about all the stuff I have learned. And there was really a lot to learn. Klaus did an amazing job and I can definitely say his session has a lot of light bulb moments. So here´s the the agenda of the session (hopefully my notes are correct    )

• SQL Server Query Lifecycle
• Performance Monitoring Methodology
• Perfmon
• SQL Server Profiler
• Wait Stats
• Exec Plans
• TempDB
• SQLDiag & SQL Nexus

I cannot explain every module of the session, but now I know a little bit more in detail what happens inside SQL Server when you execute a SELECT statement (keywords relational engine & storage engine) or an UPDATE statement. Also I have now my red line regarding Performance Monitoring. Establish baseline, identify bottlenecks, make one change at a time (you really have to keep that in mind!),  measure performance and repeat. Also, I´m now aware of the fact that an auto update statistics only happens when 20% of changes inside a table happened. Now think of large tables…  

Another (in my eyes) important thing: look out for auto created statistics. If you find a lot of these then SQL Server is missing indexes.

But there were tons of other stuff as well. Just a few keywords:

• most prominent wait types,
• how to read execution plans and understand the common operators,
• plan cache
• memory clerks
• query memory
• and so on…

And, as Klaus said, this session was only the tip of the iceberg! If you’re now curious, look at his webpage and what kind of training he´s offering.

 

Friday – 1st Conference Day!

After a really cool Keynote by Mark Souza it was time to attend to my first session that day.

And what should I say about Conor Cunningham’s (blog) Update query deep dive session? Let me quote his 2nd note: Most of this talk is beyond what CSS will support if you call them.  His session was really a deep dive into the Query Optimizer and I felt that I attended a Level 900 session! I really have to re-read his slides (the slides can be found here) as well as the chapter “The Query Optimizer” in the book SQL Server 2008 Internals. Maybe this will shed some light.

Next session for me was the Notes from the field: Cross team performance troubleshooting, presented by Justin Langford (blog|twitter) and Gavin Payne (blog) from Coeo. It was a practical guidance about the approach of a problem, e.g. how to identify the stakeholders and bring them together. I´m looking forward to get the presentation slides to take another look, maybe it´s possible. Justin and Gavin provided good information during that session. Now I have a new keep in mind: “Do not listen to hear-say or theories and do not fix issues on the fly”.

The last session session on that day was also presented by Gavin Payne: Mixing virtualisation with SQL Server – a checklist for success. Really interesting presentation where Gavin not only talked about good virtualization candidates, but also showed up a Virtualization 101 as well or what you should avoid or take care of when starting to virtualize SQL Servers (e.g. avoid RAID 5, think about resource pools inside the hypervisor…) You will find many useful question and hints in the slides.

 

Saturday – 2nd (but also last) Conference Day!

My Saturday began with the SQL Server Clustering for dummies – session presented by Mark Broadbent (blog|twitter). Wow, what a pace! But it was everything in the session you need to know when you want to start with clustering. And the slide deck is full of good advice, so take a look! As a reminder from Mark (100% ACK from my side): “The DBA should control the cluster.”  It´s quite easy to explain: Think about the business needs. No one will ask: What happened to my windows server? But: Where is my database?

The forthcoming session was Consolidation and Virtualization Strategies (including journey to the cloud) presented by Ross Mistry (blog|twitter). Now that man knows how to turn a presentation into a real experience. Ross did not only show up the three reasons why to consolidate (costs, control, consistency) but also talked about the key enablers, how to consolidate (database and instance consolidation) as well as the Utility Control Point and how UCP helps you to keep control of the resources. And it even got better once Ross started to talk about private and public cloud solutions. Especially the private cloud solution (combined with an automation layer and a management layer) was very interesting for me and I instantly began to realize different opportunities. Maybe I get a chance in the future to work with cloud solutions as well. And now I cannot wait until the slides are available online….

After a little break that I took to visit the SQLSentry booth (Hey Nick, where´s the promised email ?  ) it was time to visit Klaus Aschenbrenner again. This time Klaus talked about SQL Server 2008 Database Internals. I learned a lot about how SQL Server stores data physically, e.g. pages are grouped into extents, the difference between mixed and uniform extents and how SQL Server manages all data pages/extents with the help of GAM and SGAM pages. But Klaus also provided information about table metadata, allocation units and also gave a good insight into the structure of a data page and how important a good table design (e.g. column definition) is. The keyword is payload of a data page.

The next to last session I attended was about How DBAs can use PowerPivot for Performance Tuning /Troubleshooting. Andre Kamman (blog|twitter). Andre provided really good advice how to take advantage of PowerPivot to analyse performance data mixed with a good sense of humour (the short look onto the PowerPivot Client/Server architecture for example). Beside the sponsor sessions this was one of the most entertaining ones for me.

My last session of SQLBits8 was presented by David Morrison (blog) – The Dark Arts-a.k.a Performance Tuning, indexing and query plans. David showed a lot of examples regarding common misconceptions. One of the keyword here: SARGability. It is now a little bit easier to understand why functions break the SARGability and that sometimes subqueries are using the same exec plan as a join. Afterwards David disassembled a lot of query plans.

Marginal notes from SQLBits8

SQLBits8 was also a great chance to meet a lot of people in person I only know through their tweets and/or blogs. So I could thank John Sansom (blog|twitter)now directly for his great “Something for the weekend” blog series. If you don´t know his blog, take a look!

Also, I have met Mladen Pradić (blog|twitter) in person and I can say we had a lot of fun. Now I know what the main purpose of an IPad is….

It was also a pleasure to meet Ola Hallengren. We talked for about an hour about his maintenance script, which you can find here. He showed me an option which is implemented in his solution for quite a long time now, but I wasn´t aware of it. On the other hand Ola got also Feedback from me how I´m using his solution and what modifications I made in the setup script (outside his procedure) regarding the backup locations.

It would too long to mention all the people I have met but I think they know who I mean.

And a few last words as well:  Thank you to Jonathan Allen (blog|twitter) and his wife for the great idea to visit this awesome Mongolian restaurant on Saturday evening.

And also a BIG thank you to all SQLBits organizers. It was a great experience for me and I´ll try to be there next time.

IDERA SQL cruise contest

The adventure of the urgently needed test system

Let me tell you a story about a system administrator and DBA for a retailer.

His name: Dirk “Sherlock“ Hondong.

Our story begins on a early Monday morning in December…

It was a cold day, no sunrise yet and fog was all around the city.

“Sherlock“ went to the office at about 6 o’clock this morning, still very sleepy.

The first task this day for him was: Get into the kitchen and grab a big mug of coffee!

Then he arrived at his desk and a little flashing red light on the telephone showed him that he already missed a call. Who would call him so early in the morning?

He examined the caller list. There was only one entry: Mr. Black.

Mr. Black was also a administrator, who reside in a branch office. “Sherlock” was a little bit surprised. Mr. Black normally started to work at about 9 o’clock. Why was he so early in the office?

Only a phone call could solve this little riddle.

A nervous voice was on the other side of the phone: “Hello? This is Mr. Black speaking.”

“Hello Mr. Black, it´s me, Dirk Hondong”.

“For heavens sake! I need you right now. You have to help me. The deadline. The test environment. The….”

“Stop! Calm down.”, “Sherlock” interrupted Mr. Black, took a sip of coffee and resumed “First things first. Now, it seems you have a problem with a test environment. You now, normally I do not take care of your “special testing purposes” systems, since you often disregard our recommendations and in the SLA´s it is mentioned that you have to take care of your systems.”

“I know”, Mr. Black sheepishly replied “but now I really have a problem. The employees from the controlling department need our test system today for special reporting tasks which they do not want to run against the production environment. Therefore I have made backup of the production database on the weekend and restored it this morning on our test server. The restore was fine but when I tried to access the database I got an error.”

“What kind of error? You have to provide me a few more informations, otherwise it´s always a little bit difficult to help you.”

“Well”, Mr Black´s voice calmed down a little bit, “it is a access denied error. You know, I am no database administrator. I can run the setup.exe of SQL Server, click next, next finish and the SQL Server is there. And I also know how to use BackupExec to move a database from one server to another, but…” Mr. Black stopped speaking.

“But what?”

“…but that´s it. You know I do not often have to deal with the database servers. The controlling department asked for all the different access groups which are set on the production server. I am running out of time and no groups are there and…” Mr. Black´s voice became anxious again.

“Ok, I think I know how to help you quickly”, “Sherlock” replied. “I have this nice and handy SQL Admin Toolset available and now…” “Sherlock made a few clicks, started the Idera SQL Admin Toolset and then choose Login Copy “…tell me the name and the instance of the production server and the name and instance of your test environment server”.

“Ehm, yes, yes. Ehm. The the the production server is FinanceProd and the test server is FinanceTest” Mr. Black stuttered.

“Really fancy server names” “Sherlock” was amused, typed the server names into the Login Copy tool, selected all available groups and copied them to the test server.

“Ok, Mr. Black. That´s it. Now all your access groups from the production server also have access to the test server. Just as the controlling department asked for”.

Mr. Black was really happy again. The test server was available right in time.

“But wait!” “Sherlock said “If you want I can also run a check of your test server to see if there is anything else we may have to take care of.”

Mr. Black replied: “Do you really think that is a good idea? There is no much time left until the controlling department starts to work”

“I am really sure. If have a new colleague here in my office. He´s a doctor, brought to us by Idera. The SQL doctor. He will help us to analyse the performance of our SQL Servers as well as YOUR server configuration….” “Sherlock smiled again.

Here ends our little story and we all can be sure that there will be a great teamwork between “Sherlock” and the “Doctor”.

Only a name change

Nothing more, nothing less.

feleryan.wordpress.com turns to dirkhondong.wordpress.com and my twitter name is no longer @feleryan. Instead I will use my real name as well. So, on Twitter I’m @DirkHondong

Maybe now it´s a little bit easier to find  and to communicate with me.

The nickname Feleryan remains only on Xbox Live