One of the new products that Red Gate Software has released recently is SQL Source Control. I saw a demo of this product back in March as it was nearing completion, and I thought it was pretty cool. I have struggled with source control for years, and build a process that was mainly manual, and one that I’ve seen others use over the years.  It worked well, but it was prone to errors unless I was allowed to chastise developers on a regular basis for not adhering to the process.

Today I acted as emcee and moderator for a semi-private webinar that demo’d the product to the Friends of Red Gate, a group that is by invitation only, but lets some customers interact with Red Gate similar to how the MVP program grants some people access to Microsoft.

This was the first time I actually acted as a moderator, having been an attendee or presenter in the past. It went well, I think, though I definitely had a little dead air and struggled to keep up with questions and find a smooth way to interrupt the presenter. I would rather be the one presenting and let someone else handle the other work for sure.

I think we’ll be scheduling more of these on a regular basis for some Red Gate products to get the word out more and show how they can be used to solve problems. This is one of the few that I’d purchase myself if I was doing DBA work, so we’ll start here and see what happens.

I’m also hoping to do some other more regular webinars from SQLServerCentral, on more technical topics, across the next year.

I have a few people ask about this recently, so I thought I’d blog about it, and maybe get an explanation out there.

When we started accepting bloggers on SQLServerCentral, it was a challenge to get people to move their blogs. We had some people that just started blogging with us, but others that were already blogging elsewhere. At first I wasn’t sure what to do, but when SQLServerPedia started syndicating bloggers, using feeds, I requested similar functionality for SQLServerCentral.

We built it, and while it’s a little crude, and we’ve modified things a few times, here’s how it works:

• You send us a note with
  • Your SQLServerCentral registered email
  • A feed URL
• You blog in English (sorry, English only)
• We read the feed and publish your blog on SQLServerCentral.

The Scary DBA has syndicated his blog, so you can see how the different things look on our site v the source. Some things don’t come across cleanly, and I’m working on getting some of those things fixed.

Frequency

We used to read feeds once a day, now we do it once an hour. So if you blog about something, it ought to be on the SQLServerCentral site in under 2 hours. If you think there is an issue, please contact the webmaster.

Hints

I use Live Writer to actually publish this blog on Blogspot. It lets me test things, but also gives me a backup of my blog and control over what I write. I think it’s a good idea to own your content, as well as keep a backup of it on your own blog.

Live Writer is a great piece of software for drafting blogs, scheduling them, working offline, etc. I actually sync my Live Writer folders  across my desktop and laptops. That gives me some additional capabilities to draft stuff on one machine and finish it on another.

I highly recommend people tag stuff they want to syndicate with a specific tag and then send us the feed of that tag. That allows you to still blog about something that you want to publish, but not syndicate it if you don’t want to. I know some people do this to publish other stuff in other languages, or they mix their personal/career blog and don’t necessarily want to send everything to SSC.

Setup

If you want to get setup, send a note to the webmaster at SQLServerCentral.com and we’ll get you syndicated.

The hardest thing, I think, for most new organizers of a SQL Saturday is the fundraising. A few years ago I heard Jerry Jones, owner of the Dallas Cowboys, give his 5 rules for being a good salesman.

Number one is ask for the money. He couldn’t remember the other four :)

I think that’s a fine set of rules, but for many people, it’s really hard to ask for money. Surprisingly they are scared to do it. Maybe not surprisingly since I find it hard as well. It’s not though, as I’ve learned over the year, but I still feel trepidation that I’m asking for more than I’m giving at times.

I caught a great blog from one half of the Midnight DBA, Jen McCown (Blog|Twitter) on SQL Saturday sponsorships. It’s a great read, and there’s another good one from Ryan Adams. Sponsorship is important to a degree because there are some costs with a SQL Saturday. You might get free space, but often have to pay insurance. There’s lunch, though you can charge people. There’s signage, badges, other incidentals, but they still cost money and your significant other might not be thrilled with you paying out of pocket.

I don’t think it’s hard to get some sponsors, and I know that my company, Red Gate, is usually happy to help out some, PASS gives a little, a few other vendors will likely chip in. There are two keys that I’ve seen from talking with people.

1. Communicate often and clearly
2. Negotiate and be willing to take less (and give less).

You might think if you don’t get a response that your email was clear and that someone just doesn’t want to sponsor you. I will tell you that if you send 10, I bet that 7 of them aren’t clear to the receiver, or they aren’t read. 2 or 3 people might need to think about it and then will forget it. That means maybe one positive response from your email.

What do you do?

Send more emails. Send them repeatedly. Not botheringly-often like every day, but every week to ten days repeat them. Try new sponsors, try making a call or setting up a 10 minute meeting to talk about it. Use clear materials like Jen lists, and then be willing to work with them. Someone might not want to sponsor you for $1000, but they might be willing to do $200. If that’s the case, offer less. Be willing to shift slightly on your rate card, and negotiate with them. Ryan has good advice for being sure you deliver on your promises, keep them happy, and have them sponsor you again.

And have fun with it. It’s not the end of the world if someone walks away, and there are usually lots of sponsors to choose from. You don’t need a huge budget, and I’d plan for a small one. You can add things in easier than taking them away.

I keep seeing lots of debate about Android v the iPhone 4. I have friends talking about how cool Windows Mobile 7 will be, and even some talk about Nokia making a comeback. However it seems that so many people are just missing what’s cool about the iPhone.

It just works.

Not perfectly, not all the time, but it’s just so simple to make things happen. And if you want to try something else, there’s likely a dozen apps that do that one, quirky thing you want, and you can pick the one that works for you.

As an example. I was on the phone with a friend today and they recommended Career Warfare to me. He told me the author and while he spent 2-3 minutes explaining what he’d learned from it and why he’d recommend it, I surfed over to Amazon, found the book in a Kindle edition, selected my phone, and shot the book to it. A quick $12 purchase for me, some money for the author and Amazon, and befoer I got off the phone, I clicked the Kindle app and saw it download through wi-fi.

There’s nothing magic here, and Kindle is on Android now, but that platform, with things being that easy, is what makes the iPhone cool. Most apps are that easy to find, and I can find them in iTunes, see large screenshots and read reviews easily in a way that I couldn’t in the early Android Marketplace. Maybe it’s easier now, and maybe I can “send” things to my phone, and if so, they’re learning.

That cool, simple, seamless platform that just seems to work so often and intuitively, is the reason I love the iPhone.

One thing I learned years ago in C programming was the elegance of using operators and simplifying expressions. This sometimes involved doing two things in a single statement, if it makes sense. For example,

x=y++;

Those of you that have done some programming will recognize this as incrementing y and assigning a value to x at the same time. Well not the same time, but in one line.

I knew that you could some things like this in T-SQL, but it didn’t trigger in my mind when someone asked about building their own sequencing table. This person wanted to update a table with an incremented value and return the value to the calling program without causing any locks/blocks.

Paul White posted this fantastic piece of code that illustrates this:

UPDATE  dbo.GPK

SET     @NewID = next_value = next_value + 1


WHERE   table_name = @table_name;

In this code the GPK table is being updated, with the next_value column being incremented. At the same time, @NewID, a variable that is an output parameter for a stored proc in this example, is assigned the value from next_value.

The value assigned is the incremented value, so if next_Value contains a 1 before this is run, @NewID will get 2. The increment/update occurs first, with the new value being assigned to the variable.

I think this is really cool, and it’s something I need to remember for future T-SQL problems.

 

The application form mentions that the quite a bit of what you include will be made public. While I won’t want to repeat everything, I’ll include a few of my responses about why and what I want to do with PASS and take some early comments.

I have to give thanks to Andy Warren, Andy Leonard, and Joe Webb for agreeing to write recommendation letters for me. I greatly respect the opinions of those three men and am honored they were willing to lend me support on this endeavor.

I am including the question in bold/italics and then my answer below.

Please discuss why you would like to serve on the PASS Board of Directors:

I have been a member of PASS since 1999 and have worked with the organization in various ways over the years. It is an organization that I both support and believe in, and I would like to see the organization help the SQL Server community and the professionals that work with SQL Server more. I have been frustrated at times by the way that the organization has been run and would like to try and influence PASS to be more relevant in the lives of its members as well as grow the organization’s membership to encompass more SQL Server professionals by contributing to the decisions made by the board of directors.

If elected to the PASS Board, what goal would you set to contribute to the benefit of PASS? How would you work to accomplish that goal?

I think that one of the failings of PASS over the last decade has been a lack of communication with its members. While PASS has matured as an organization in the last decade, it has also appeared to stagnate in its membership growth. I have felt that the lack of communication has been one of the issues that has slowed growth in the SQL Server community. I would like to work towards finding ways to better engage and speak with the community about what PASS does for them, and what they would like to get out of a professional organization. I feel this would go a long way towards helping PASS grow its membership. One of my contributions would be to use my skills in communicating and inspiring people to spread the message about PASS.

I also feel that the SQL Server community is much larger than PASS, but I don't think it needs to be. I would like to see more events, engagements, and/or benefits from PASS that convince more people working with SQL Server to join PASS. I think that PASS can help provide some additional certification resources, either with their own certification/testing process or with formal study groups/sessions/materials for MS certifications. These would add member value. I would like to see more training opportunities alongside the current SQL Saturday/SQL Rally/Summit choices, some of which could be paid events and help grow PASS revenue. These could be separate events, perhaps Beginner 101 days or boot camps or pre-conference sessions at other events. Having helped to get SQL Saturday off the ground, and working with other events in the past, I think that some of the volunteer efforts and speaker enthusiasm could be channelled into other events, and that the board of PASS is in the position to help promote those events. I would be willing to help organize and promote these events.

I also feel that PASS has at times not been relevant to the average SQL Server professional on a regular basis. There has not been a reason to be interested in PASS aside from the annual Summit in the US. I would like to see PASS doing more to engage with SQL Server professionals and becoming a more valuable part of their lives each year. Not every week, but at least once a quarter I'd like to have more people finding some reason to interact with PASS. I am not sure of what the best way to accomplish this is, but one thing I think needs to happen is more interaction from the board, and committee volunteers, with the average person and soliciting and responding to the feedback they may have. One of my strengths over the years has been communicating with people, motivating them, and encouraging them to move forward in their careers. I think I can bring that same efforts to PASS communications and find ways to make PASS more relevant in their career.

Please describe your past involvement with PASS:

• I have presented sessions at PASS Summits and SQL Saturdays.
• I have hosted numerous SQL Server user groups to help them function as PASS chapters when PASS was unable to provide the hosting support in its early years.
• I have both presented at, and sponsored the PASS Camp events in Colorado in an effort to reach out to the local community.
• I participated in the first 24 hours of PASS as a speaker.
• I worked with PASS to provide the printed version of the SQL Server Standard to PASS membership and attended a few board meetings. I was responsible for coordinating content and advertisements with the PASS management group.
• My company, SQLServerCentral, has had a promotional arrangement for the PASS Summit for many years in which we drive significant traffic to PASS and host an opening night party that has become very popular.
• I worked with Andy Warren to create SQL Saturday, grow it, and then arrange for its transition to PASS.
• I served on the Program Selection Committee for the 2010 Summit.

Please describe your volunteer experience with PASS, including an example of a project you worked on, that you followed through to completion. Describe how your specific contribution has benefited the PASS organization:

My main volunteer experience with PASS has been on the Program Selection Committee. I had complained about the selections the previous year and decided to volunteer and try to improve the process. I attended a training session, communicated with other volunteers and speakers to ensure that my criteria and process were similar to others. I completed my ratings in a timely manner and then debated the selections in real time with the committee and made selections.

Throughout this process I also sent feedback to volunteers and PASS about issues or potential improvements that I found in the process. A final follow-up call is being scheduled.

I also publically have blogged about the process and some thoughts, getting feedback from the community: some good, some bad. I think that overall that I have pushed PASS to re-examine the way things are done, and look to improve the process. I don't know if that was done in the past, so I cannot comment on whether or not PASS is better for my efforts.

-----

This isn’t necessarily related to PASS, but I’ll include it as it gives a little basis for why I might be qualified.

Please describe your management and leadership background, listing specific examples of teams or projects:

My life has been a constant shift between working and leadership positions. I have been a technical lead in development projects, a manager of people, the owner of a company, and a member of a board of directors.

I took a management position at a company that required me to both manage development resources and operational resources. Since this was a new company for me, I was able to assume charge of the group without and prior relationships with people. Without disrupting existing processes, I did require people to begin explaining the reasons for and documenting their actions. From there I worked with them to re-examine their beliefs and habits to try and build better processes. I was able to, with lots of help from my staff, create a much more stable environment and ensure a few large clients did not leave the company because of the way the IT group had been run previously.

Specifically we had developers that previously had administrator access in production. Early on I found them making a change the resulted in the wrong image appearing for a client. I removed access, apologized to the client, and showed them the change in permissions. I explained that this would not happen again, but balanced that by saying some requested changes would be slower to implement. This large client (40% of our sales) was mollified and ceased negotiations with a competitor. I also had a case where my developers and system administrators were arguing with a large NY bank about network issues. I knew that there fundamentally was a problem and brought in a senior consultant that I knew would be able to explain the issue and have more credibility than I with the bank. They were able to solve the issue in an afternoon since both sides could listen to a third party more objectively.

I worked for a large Fortune 1000 company as a DBA at another time. We were purchased by another large Fortune 500 company and I was promoted to be the operational DBA manager of 10 other people, including two that I had previously worked with. This team was dispersed across two states, which required me to travel monthly to another location and also manage each part of my team remotely at times. I also had to learn to step back and work with these people to get objectives accomplished without doing the work myself. Prior to this I had always been able to step in and lend a hand when needed, but in this role I had to step back and strictly manage people.

I also had to manage former peers, which required a changing of the relationship. I learned to ensuring we could work together and I respected my peers' opinions, but also that I balance that with the need to make decisions, sometimes unpopular. I had to bring people in to work late hours at times, and required them to complete unfinished documentation, something that I had not liked doing myself. I also had to manage people that made more money than I and were older than I was with more experience on their own systems.

We had large DB2 and Oracle systems that were critical to the company, so I had to manage these people without understanding what specifically they did. My technique when we had technical issues (scalability problems on DB2 and technical issues with Oracle) was to probe their knowledge, their technical decisions and get them to question themselves. Since we often had scalability issues and the corporate culture was to gather a large crisis group together and yell at vendors, I learned how to mediate issues between groups and get people to focus on the technical issues, proceeding more methodically without assumptions than they were used to. I also learned to brief executive management (VP/CIO) on the status of issues while ensuring that I kept more immediate management (director level) in the loop. I am not sure I was that successful at changing the culture, but I did learn to accept small victories and move on.

My HOA experience is listed above in general, but specifically I worked on the architectural committee in discussion a homeowner's decision to build a large structure on his property. This was approved prior to my election, but constructed during my tenure. As a result I had to listen to complaints, sometimes yelled complaints, from other homeowners, and then explain that this had been approved. I also had to inspect the construction, which the homeowner was not pleased about and expressed his displeasure loudly as well. This was a position that was unwinnable, necessary, and forced me to learn tolerance and patience in working through a problem without quitting. I also had to negotiate changes to a new development adjacent to our community that required our approval. I received concerns from homeowners, solicited others by explaining the potential issues to them and then presented those to the developers, negotiating some changes to their plans that would ensure our community was not adversely affected by parking placement, a proposed golf course, and the obstruction of sight lines.

-------------------

I would like any comments you have, preferably on your own blog if you have them and a link posted here. The comment section below doesn’t really do well in allowing a nice, well thought out response or comment.

I’ll look to add in responses and thoughts to the blog every week based on what people ask or want to know.

I send my board of directors nomination application to PASS and asked for a confirmation. I always worry that something like this might get “lost in the mail,” so hopefully I’ll get a response today. I announced it on Twitter as well, just to be safe.

I saw this on the 37 Signals blog as “I’ve already got the prize" and found it to be both true, and a little annoying. It refers to the Nobel Peace Prize, who Richard Feynman is apparently in consideration for.

The speaker comes across a little arrogant, and a bit of an ass, but what he's saying makes sense. He’s not necessarily a fun guy to be around, but I think many brilliant people, especially teachers and researchers, aren’t.

However he’s saying that the journey, the fact that you are learning and figuring things out, is what’s important. It’s not the award, it’s not being a part of some group that recognizes your efforts that’s important.

I agree with that. It is nice to be recognized, to get kudos, maybe some reward for your effort, but if you shoot for that reward and it’s the best part of your work, then I think you are going to be constantly disappointed.

Enjoy the journey, smell the roses, take pride in your effort and what you do. If there is some recognition or reward later, that should be a small part of you enjoying what you do.

I’ll announce it here first, along with some reasons why I’m waffling from an earlier post:

I’m going to run for the PASS Board of Directors in 2010.

I am finalizing my application and getting reference letters before I send it to the Nominating Committee.

Why

I’ve always admired my wife for her passion with horses. If she could quit her job tomorrow and train horses (and their riders) full-time she would. She dreams about getting there, and spends as much time as she can with the big beasts. I support that effort, sometimes with a shovel, and hope she gets there some day.

However I haven’t felt any great passion in the past. When I was 35, my wife and I talked about retiring for 6 months when we each reached 40. When I sold SQLServerCentral, she asked me if I wanted to still try retirement. I declined since I didn’t have anything I really wanted to do. No great passion drove me. I enjoy hobbies, and I enjoy life, but nothing seemed to really move me enough to want to do it full-time.

I was wrong.

Without realizing it, something had crept up on me. Recently my wife and I were discussing whether I should run for the PASS board, and she was asking my reasons for doing so. What would I gain? Does it help my brand? What can I do? Why bother? As I tried to give her answers, I let my mind randomly wander across the last decade and my involvement with PASS. The things I’ve enjoyed at the Summit. The numerous times I’ve been ready to throw my hands in the air and completely divorce myself from PASS. The way that I’ve tried to influence and push PASS in ways that would benefit the community. When I finished talking to her, she told me this.

“It’s a no brainer. Run”

She told me that I had displayed a passion that she hadn’t seen before either. A real drive to make a difference that shone through in my words. She had concerns over the time commitment, but she did think we could manage that this was important.

I slept on our conversation and then made the decision to move forward the next day.

If my application is approved, it will be posted on the PASS site, but if not, I’ll post it here so you can judge for yourself if I am someone that is a good candidate for the PASS board of directors.

I am both excited, and a little nervous, about the this process moving forward. I look forward to meeting a few of you on the campaign, err, SQL Saturday trail.

Why would you do this?

select distinct(hostname),
(select count(*) as criticalCnt 
 from @temp where severity_guid='0168A833-1732-411E-8205-C2F6CD91737D' 
 and hostname=t.hostname
 group by hostname),
(select count(*) as criticalCnt 
 from @temp where severity_guid='CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' 
 and hostname=t.hostname
 group by hostname),
(select count(*) as criticalCnt 
 from @temp where severity_guid='C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' 
 and hostname=t.hostname
 group by hostname)
 from @temp t

I ran into this on a post where someone had asked about how to basically call a CASE statement. The posted didn’t know how and someone posted this as a way to tally the various counts of alerts.

I had glossed over it when I saw it, but when someone else replied with this statement, saying performance was better, I decided to look at see how much better.

select hostname,
sum(case when severity_guid='0168A833-1732-411E-8205-C2F6CD91737D' 
 then 1 else 0 end) as [Count_Of_0168A833-1732-411E-8205-C2F6CD91737D]
,sum(case when severity_guid='CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' 
then 1 else 0 end) as [Count_Of_CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5]
,sum(case when severity_guid='C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' 
 then 1 else 0 end) as [Count_Of_C4CF8A23-A106-4617-BAB0-94DA3CA74EF1]
 from @temp 
group by hostname

I set the statistics on for these queries and found these results.

Query 1:

Table '#45544755'. Scan count 52, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query 2:

Table '#473C8FC7'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

That’s a huge difference. If this were to be run with any significant frequency on a system, you’d be using (4 x the number of rows) as many scans of the data than you needed to. Even though they are logical scans, it’s still CPU and memory movement you are requiring, and if this were a significant amount of data.

You can also see a drastic difference in the execution plans:

I’ll let you figure out which plan goes to which query.

This is a simple example, but it shows where someone really can get poor performance over time with badly written SQL. It’s nice to have various ways to solve problems, but you also want to choose the appropriate tool. Subqueries make sense at times, but this isn’t one of them.

We are trying to buy another property here at the ranch. My wife had to leave town in the middle of this, and asked me if I could sign the contract and then scan it in to email to the other party. It was late at night, I was tired and didn’t want to mess with it that night, so I said sure.

The next morning I hooked up our ancient Epson Perfection 610 to my machine, a process that involves dragging a large piece of hardware out of the closet, plugging it in the wall with the cord running across the floor to the scanner on the floor, and plugging a USB cable into my desktop. I used to do that every month or so for expenses, but apparently I haven’t in a few months. Guess I know why Accounting is a little annoyed with me.

The scanner didn’t work.

Windows 7 x64 didn’t recognize it, and when I tried downloading 2 different drivers from Epson that didn’t work I stopped. I had a virus on my machine recently and am nervous about making too many changes without really thinking about it. I plugged the scanner into my Windows 7 x86 machine and it didn’t work their either. So much for extensive Windows 7 support of older hardware.

Since I had committed to doing this for my wife, and I do need a scanner, I decided to look for one. I had a conference call, but a cell phone and a mute button let me drive to Best Buy and grab a new scanner for $90. I decided on an Epson Workforce 610 because it was wireless, did what I needed, would handle copies and faxing as well, and the 18 year old at Best Buy said he had one and it worked great.

I brought it home, basically removed all the plastic wrap and tape, loaded ink cartridges, and it was ready. I think I pressed 3 buttons for it to auto detect my wireless network and connect. I ran a CD install of 3 or 4 programs (to handle printing, management, scanning, fax) and then my Win 7, x64 machine detected the printer and sent a test page over. That was cool, but scanning was what I needed.

I brought up the scanner utility, put the first page of the contract on the bed and hit scan. Across the room, through the magic of wireless, the image appeared saved on my hard drive. I added the rest of the contract in the document feeder, and soon I had 16 jpgs in a new folder.

That was cool. Scanning has always been a bit of a hassle, like printing, but this was a very cool device.

And my daughter thinks that the color inkjet is “the bomb” for printing pictures of cats.

I think this is a good idea, and I’ll give you some reasons, but every SQL Saturday ought to consider having a few sessions an hour before the main event starts.
When the Rocky Mountain TechTrifecta v2 came to Denver this past February, I was one of 7 or 8 people that was asked to run an early morning session. From 7:30-8:30, before the 9:00am kickoff, we had a number of sessions in various rooms that were essentially discussions. I actually picked up someone else’s session since they couldn’t make it, and had about 10 slides, but it was really me leading a discussion with 6 or 7 people in the room.
I wondered what was the point, but as I talked with Julie Yack, the organizer, she said that the previous year they’d had lots of people show up early to register and then stood around, not knowing what to do. I’ve seen that in other events, including many SQL Saturday’s as well. It takes time to register 200+ people, even if you are just giving out a name badge, and so the first person to come in has a lot of time to waste.
There’s also a lot of people that come to SQL Saturday that are very new beginners. They don’t understand a lot about SQL Server, and they’ll get lost during other sessions. Or they are experienced in one area, but not another.
This is the time for a basic foundation for SQL Server. Honestly I would suggest that every SQL Saturday recruit 4 or 5 speakers, and offer these basic sessions, a very, very junior level session, before the main event.

1. Introduction to SQL Server
2. Introduction to Reporting Services
3. Introduction to Querying in T-SQL
4. Introduction to Integration Services
5. Introduction to Analysis Services (if you have speakers in this area)

Set up a few rooms, start them at 7:30 if you start the event at 9:00 and publicize them for beginners. You will have some very, very happy attendees.

One of the things I've been wanting for awhile is the ability to embed videos here on the blogs. It's not that I use them a lot, but there are times when I'd like to have an example of a video, and want it to appear inline, as opposed to a link. I asked my IT group, and they did it. Or they said it should work, so here's a basic test.

I'm embedding a video that talks about motivation. If nothing else, it has some very cool animation, but I think it's worth watching, and it's based on Dan Pink's book, Drive.

I'd be curious to know what you think. I'm reading his book and it's inspired me to write a few editorials and blogs.

I’ve never had to do this in production, and I’ve only practiced it a few times, but I think this is a core DBA skill. Along with being able to backup and restore your databases, you should be able to recover to a point in time. That can mean a tail log backup.

I read this in Paul Randal’s blog recently and decided to practice it. So I created my own new database and added a few transactions:

create database db5
go
use db5
go
create table MyLog
( Txt varchar(max)
, LogDate datetime default (getdate())
)
go
insert MyLog select 'No backup', GETDATE()
go
backup database db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_full.bak' with init
go
insert MyLog select 'Full backup complete', GETDATE()
insert MyLog select 'Misc Transaction', GETDATE()
go
backup log db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn' with init
go
insert MyLog select 'Log Backup Complete', GETDATE()
go

I’ve added a couple backups here. If I were to restore this full, I ought to have 1 row in this table. If I add the log restore, I’ll have 3, but I’ll be missing the last line that says “Log Backup Complete”. Now I’ll wreck the database, as per Paul Randal.

use master
go
alter database db5 set offline
go

I then rename the mdf file from db5.mdf to db5xxx.mdf, essentially “deleting” it from view by the SQL Server service. When I set this db online

alter database db5 set online
go

I get

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db5.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 945, Level 14, State 2, Line 1
Database 'db5' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

As expected, we have a problem. How do I backup the tail of the log? Remember that file is still visible. We’ll use Paul’s trick to add NO_TRUNCATE to the command:

backup log db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn' with init, no_truncate

I get a successful backup, so let’s test. Here’s my restore script, restoring this db as a new database on this instance.

RESTORE DATABASE [db7]
FROM
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\db5_full.bak'
WITH
    MOVE N'db5' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7.mdf'
,  MOVE N'db5_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7_1.LDF'
,  STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7.BAK'
,  NOUNLOAD,  STATS = 10
GO
select * From db7.dbo.mylog
go
RESTORE log [db7]
FROM disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn'
with standby = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK'
go
select * From db7.dbo.mylog
go
RESTORE log [db7]
FROM disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn'
with standby = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK'
go
select * From db7.dbo.mylog

When I go through this, I get result sets of 1, 3, and 4 rows respectively. I see all my inserts, so despite having a corrupted, destroyed, or renamed (in my case) MDF, I can get all my data back with the log.

Learn and practice a tail log restore. It’s worth it.

And don’t forget to set the db active:

restore database db7 with recovery

It’s time for another T-SQL Tuesday, the brainchild of Adam Machanic (Blog|@AdamMachanic) of SQLBlog.com. This time we have a SQLServerCentral author, and MCM, Robert Davis, running the show.
How To LearnI’ve had a lot of school in my life. 12 years of primary education, 5 years of undergraduate level education, 2 years of graduate school education, 7 semesters of calculus, and even a few programming classes since then. In all those hours, I have learned a few things, but the most important things were learning how to learn.
A high school degree (US) or an undergraduate degree (BS/BA) doesn’t really teach you a lot of practical skills, IMHO. However what they subtlety teach you is how to learn. You have to develop the skills more and more to teach yourself, research, analyze information. Those are the skills you will need as you move into a career. Even engineers, who learn a larger percentage of skills in skill, need to learn more in the workplace, and successful students tend to have an easier time picking things up later.
Not that average/below average students can’t. Sometimes it’s just finding something that brings out your passion, which often isn’t school.
How I LearnSo how do I learn? As much as I like lectures, and I listen to many of them at SQL Saturdays or other conferences, it’s not necessarily helping me learn or build a skill. It increases my knowledge base, gives me the ability to think more laterally when confronting issues or searching for a solution, and it inspires/excites me. It gets the juices flowing.
However to actually learn something, to build a skill, I need to do. I typically learn by actually writing SQL statements. Setting up mirroring, testing restores. Those skills, just like muscle memory from performing a task, are built for me by repetition and practice.
That’s one reason that I have taken relatively few classes in my career. Taking a week out and being immersed in something like VB or SQL hasn’t helped me nearly as much as having hours a day across months to actually write code and try to solve a problem. This blog, at least the T-SQL parts, have tended to be focused on rebuilding those skills for me. Practicing things that are new, or that I haven’t spent much time on.
I prefer working with books to classes, but it’s the same thing for me. I retain some snippets from books, but if I don’t practice the skills, actually get hands on time, I haven’t learned much.
How You LearnI don’t know. I think you have to try some different things and then evaluate if they work. If you attend a class, see if you use those skills across the next couple months. Did you really learn something? Try something you learned a couple weeks afterwards. Did it stick? If not, try something else. Read about it and do the same test. Work through examples from a blog post/article/book/class, and see if that helps.
Learn how you learn. It’s one of the most valuable things you’ll ever do.

I have a great job. Actually it’s a fantastic job for me. I have flexible hours, I can work almost anywhere, which means that I can do things like go with my kids to events, even trips out of town and still work. I get to express myself, and talk about interesting things.
However there are hassles, it can be a grind, and there is pressure to come up with something interesting on a regular basis. I do enjoy it, but I have had times where I thought about going to be a DBA somewhere and letting someone else point out work for me to do.
Then I get notes like this:
I've been in this business since 1980, starting with CDC punch cards, so you can see how much 'experience' has accumulated in what seems like the wink of an eye. I don't get the chance to read all your editorials, but from the ones I have read, you have the gift ( because it is a gift ) of seeing the whole picture!
... Your insight and knowledge of not just 'work' but of people and careers is the best, more so than any other I've read. Don't lose that deep appreciation of life/work, care and intelligence your editorials have come to be.
That’s cool. It arrived in my Inbox recently and made my day.

I read a blog recently that was titled: Time to Stop Settling. I think that the author (a friend) was intending to motivate you to move forward, and try to change things when you see a way to make them better instead of living with them.

I agree, but….

My caveat here is that you also need to find balance in your life. One of my favorite quotes is “God grant me the strength to accept the things I cannot change, the courage to change the things I can, and the wisdom to know the difference.” I think that quote is telling you to strive for balance, and realize that you cannot change everything, but you can change some things and you ought to think about those that you can.

We all have tough times in our lives, especially at work. Even though I have the best job in the world, I have bad days, I have things I don’t like, things that I can’t change. I strive to balance the stress and workload with the good things, and while I try to move forward, I move forward in different ways. Some periods might involve more time with my wife, some more vacation with family, some more speaking, some more writing, but they all are a part of life.

Don’t settle when you don’t need to, but don’t try to change everything. Pick your battles and look to make headway, make things better, not “fix” everything.

Up early, the only one in the house, so I made coffee, sat down to check on SSC and the news of the world, and got caught up answering questions.

A few interesting threads caught my eye:

• Design Question
• SSIS import changes

Went back and forth on a few threads, making the day get started in an enjoyable day. I just enjoy the SQL stuff.

I was querying the SQLServerCentral database for something else today, and I decided to take a minute and double check on my posting goal. I didn’t check a few weeks ago when I wrote the update post, but I had been paying attention and noticing my 30 day running average, which seemed to be always above 300.

But was it?

So I did, and got these results:

Month Posts

7     72
6     377
5     352
4     442
3     277
2     372
1     280

Pretty good for me, lower than last year, but a nice average.

310.2857

Just enough to be sure that I’m meeting my goal, and I have a bunch of time in July to be sure that I’m there.

One of the things that got us to switch OSes from W2K to WinXP when I was an administrator at JD Edwards was the Run As command. We had two accounts, regular and privileged, and needed to switch between them on a regular basis. I didn’t like the policy, but with WinXP, we had the “Run As” command when we right clicked things like the SQL MMC plugin, so we could access servers under the privileged account.
In Windows 7, if you right click, you see “Run as Administrator”, which works great on my desktop. However what about a domain? What if you have two accounts and want to run something for testing or because you need different rights?
I found a cool trick I wasn’t aware of since I haven’t needed it. If you hold down Shift when you right click the item, you get a new Context menu item. The Run as different user.

Give it a try. I bet you find it handy.