|
|
As I’ve mentioned previously, I’ve been using FastForms for years and I have one basic rule that I never, ever break. I never make changes in FastForms with users in the system. M2M says you are safe to do so, but painful experience has shown me that this is unwise.
The problem is that the program, and it’s underlying database system (Visual FoxPro), are simply too unstable. Once you have a problem with it, you’re forced to take drastic measures to fix it. I’ll explain in a future article how to completely remove a botched FastForms customization.
Well, I also have an unwritten rule that I’ve always followed when performing FastForms customizations, and that is to make a backup of the entire Util Folder when I do so. Now, I know that technically I only need to make a backup of FormXMaster and FormXItems, but it is simply easier to make a copy of the entire folder. In most installations the Util folder shouldn’t be 100MB anyway.
Why dedicate an entire post on this topic? Well, it’s simple. FastForms’ bugginess burned me earlier this week, and I had not followed my unwritten rule. Further, because a problem occured with my backup server, I had to get the Util directory directly from tape. This takes a considerable amount of time, and I could have avoided that by taking 5 seconds to copy the Util directory. Luckily however, I had followed rule number one and performed this customization after hours. Otherwise, my M2M would have been down for nearly an hour while I scrambled to fix the problem.
So, learn from my mistakes. Whenever you are customizing FastForms, VBA, VFP Reports, etc make a copy of them before editing. It’ll save you time and headaches in the process.
 Hoping to find an everlasting Gob Stopper. Last year I pined away reading all of the tweets from last year’s PASS.
It was cute, but I meant what I said. I made a promise to myself that I would go to PASS 2010. Well, last night I pulled the trigger and purchased my ticket to go to PASS. I am thrilled and can’t wait to go.
Last night I was busy writing up ideas and things I want to do at PASS. This is what I have so far.
- Meet Paul Randal and Kimberly Tripp, see if they do indeed have Jedi powers.
- Spend time with the Brian and Devin Knight and ask some technical questions about a “secret” project I’m working on.
- Meet Buck Woody and see for myself why he’s so “infamous.”
- Try not to make too big a fool of myself.
So, now I have to make flight and hotel reservations, buy a good netbook to take notes, umm….
Should I buy a kilt?
Can’t wait to see you folks there. More to come!
Some DBAs would cringe at what I’m about to tell you, but I often use T-SQL to format my data prior to presenting it in a report. Many experts feel that data formatting should always be done in the presentation layer. There are a couple of reasons why I do this.
- I re-use as much code as I can. I create base templates for most of the main modules in M2M (Sales, Purchasing, Accts Payable, Etc) and these templates include the formatting code. This results in a professional, standardized image to my reports.
- I report using many different programs as my presentation layer and I want to re-use my scripts for all of them.
Consider the following script:
SELECT fsono, fenumber, frelease, fpartno FROM m2mdata50..sorels The first few rows of data from the M2M educational database return:
000001 001 000 CP2080
000001 002 000 FF204W
000002 001 000 WF201R
000002 001 001 WF201R
000002 001 002 WF201R
000002 001 003 WF201R
000002 001 004 WF201R Do you notice the distraction of the leading zeros? When searching for a sales order number, does anyone really enter “000003″? These are a problem in most of the modules of M2M. As I mentioned before, many DBAs feel that you should remove them via formatting in the presentation program of your choice. However, I regularly create reports in SQL Reporting Services, Crystal Reports, Visual FoxPro, and Excel. This means that if I don’t pre-format with T-SQL, I will have to adapt the presentation later manually, which and the technique will be different for each reporting media. This is time consuming and error prone.
As always, please read my standard disclaimer before implementing any code from my site. Using T-SQL, you could strip the preceding zeros like this:
SELECT SUBSTRING(fsono, PATINDEX('%[^0]%',fsono), 10) SONo,
SUBSTRING(fenumber, PATINDEX('%[^0]%',fenumber), 10) SOItemNo,
SUBSTRING(frelease, PATINDEX('%[^0]%',frelease), 10) Release,
fpartno PartNo FROM m2mdata50..sorelsWhich yields this:
1 1 000 CP2080
1 2 000 FF204W
2 1 000 WF201R
2 1 1 WF201R
2 1 2 WF201R
2 1 3 WF201R
2 1 4 WF201R That works, but it’s too wordy for me. A better option is to use a User Defined Function (UDF) to clean up the data. I’ve demonstrated how to write these in the past.
Consider the following:
CREATE FUNCTION RemoveZeros(@ZeroField VARCHAR(25))
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @ZerosRemoved VARCHAR(12)
DECLARE @Length INT
SET @length = LEN(@ZeroField)
SELECT @ZerosRemoved =
CASE
WHEN @ZeroField = '000'
THEN
'M'
WHEN @Length > 0
THEN
SUBSTRING(
@ZeroField,
PATINDEX(
'%[^0]%',
@ZeroField),
@Length)
ELSE
''
END
RETURN @ZerosRemoved
ENDWhich produces output like this:
1 1 M CP2080
1 2 M FF204W
2 1 M WF201R
2 1 1 WF201R
2 1 2 WF201R
2 1 3 WF201R
2 1 4 WF201R Can you see the possibilities? You can use this UDF in other modules such as shipping and invoicing as well. It works particularly well in shipping because the item numbers are 6 digits. Have any of you ever had a shipper with a million line items on it?
SELECT fshipno, fitemno, fpartno FROM m2mdata50..shitem
000001 000001 CP2080
000002 000001 FF204W
000003 000001 L20100
000003 000002 L20200
000004 000001 EF120100P Becomes:
SELECT dbo.removezeros(fshipno) ShipNo,
dbo.removezeros(fitemno) ItemNo,
fpartno PartNo
FROM m2mdata50..shitem
1 1 CP2080
2 1 FF204W
3 1 L20100
3 2 L20200
4 1 EF120100PThe performance impact is negligible, and the both the script and the output are well formatted.
What other UDF’s do you think would be useful for M2M reporting?
 Free Education Rules Anyone who reads this blog knows how passionate I am about learning. Learning is your path to job and financial security, brings feelings of accomplishment, and is just plain fun. I’m constantly amazed and grateful that I belong to one of the best technical communities on the planet. This is one of the few communities and careers where people are more than willing to teach you for free. Some excellent free educational opportunities hit my inbox lately that I wanted to share with you.
24 Hours of Pass
The 24 Hours of Pass recordings have been posted and anyone can freely download them. All you have to do is sign up for PASS, which is free. Go back and read the Top Ten Sessions for M2M Administrators article and download the videos.
Virtual Training for SQL Server Performance Tuning
Quest Software, maker of the fine database administration tool TOAD for SQL Server is offering a full day of free virtual training on Performance Tuning and Wait Events. The lessons will be given by three brilliant presenters, Brent Ozar, Kevin Kline, and Buck Woody. I’ve never watched a bad training event offered by Quest, and this one should be excellent as well.
Free Webinars from Wrox and Pragmatic Works
The experts at Pragmatic Works have been busy in a partnership with Wrox Publishing and have been cranking out free webinars covering everything from Datawarehouse Development to SAN administration. Once again, you will have to sign up but everying is completely free.
SQL Saturday Dallas Session Recordings – Coming Soon!
The good folks over at MidnightDBA will be posting all of the presentation recordings taken at SQL Saturday Dallas. I’ll follow up with another post as soon as they are released.
As an aside, the photographer, Emad Kamel, who graciously volunteered to take pictures of SQL Saturday Dallas has put them online. Peruse through them and see if you can find pictures of yours truly. 
I know that it seems like I’ve been blogging solely about learning and community lately, and I promise that is going to change. I have some big projects that I have been working on which I will be sharing with you in the near future.
 Devin Knight, Wes Brown, and yours truly.
It’s 8:30am on SQL Saturday, and I feel like I’ve taken a two by four to the head. I’m standing outside the front of the building leading the registration team at the front tables, squinting in the sun, sweating profusely, and wondering why I feel so good about it. What happened since only 4 hours previously, when I dragged my ass out of bed? Suddenly I receive an epiphany about my life, career, and the path it is going to take. I’d like to share it with you….
Grasshopper, Quickly as You Can, Snatch the Pebble From My Hand.
Almost three years ago, I moved to Dallas to give my career a kick in the pants and decided to focus on SQL Server. In my search for knowledge, I came across Brent Ozar’s excellent blog post about Perfmon. I e-mailed him a question and was rather surprised that he responded and that his response was very thorough. It was obvious that he really wanted to help me. This led to a very good friendship, and I’ve asked his advice on more items, technical and non-technical, than I can count. He encouraged me to seek out the community, get involved, and create a brand for myself.
The existence of this blog, as well as my involvement in the community can be attributed as much to him as to my own efforts.
Shall We Play a Game?
Let me preface what I’m about to say with the following. I have never been a selfish person. Throughout my adult life, I have volunteered in one way or another. For example, I taught Aikido twice a week for the better part of a decade without any compensation whatsoever. I have always been a teacher and mentor. However, since I made the decision to jump-start my career, my attitude changed. I was “involved” in the community, but something was always missing.
I’d watch Brent and others such as Paul Randal, Mike Hotek, Tim Mitchell, Sean McCown, and Brian Knight’s Team freely give their time and share their knowledge with others. I analyzed what they did, the motives behind their actions and tried to determine how to become successful like they were. However, I could not understand why all of them gave as much as they did. It was almost as if I had turned my career into a game of Risk.
It wasn’t that I didn’t see the value of giving; it was just that I was so strategic about it. In my view it was about exposure, getting credit, and focusing on the goal of building a brand. Brent and others tried to explain to me that I should be focusing on the community, the people who I was trying to reach. It may seem obvious to you, but I just didn’t get it. I asked several people that I trusted for advice about my career strategy and why they gave so much. They would talk about the joy of helping someone learn something, of helping them better themselves. I went to dinner one night with Trevor Barkhouse, and he became very animated about how great it felt when people would ask him questions at User Group meetings regarding the topics he previously taught. It was obvious how much he enjoyed it, but I kept looking for his “angle.” Why was he really doing this? On Saturday, my viewpoint radically shifted; it all came together.
You Must Unlearn What You Have Learned.
The process of putting on a SQL Saturday is arduous. Until you try to plan something like this, you have no idea how much time and effort it takes. I went three weeks without eating lunch (an amazing feat for someone of my size), because I was arguing with sign people about banner graphics every day. My girlfriend kept teasing me about visiting “the other woman” every Saturday because of all the planning meetings. Basically, I felt like I was working a part time job.
However, things changed when I got to the Thursday NTSSUG meeting and hung out with Sri and Tim outside discussing last minute details. Even at this early stage I felt like I belonged there, like I was part of the group rather than just a dude who showed up to the meetings. The team had all gone through this thing together, and we were comrades.
After the meeting, we had so many people volunteer to pack attendee bags that we were bumping into each other. Several weren’t even going to SQL Saturday; they simply wanted to help. In my “strategic” mindset, this made no sense. Many people showed up Friday including several people from the Houston User Group. It wasn’t even their User Group, and they were willing to do anything to help.
Saturday morning I woke up at the ungodly hour of 4am to get ready for the event. I was the first to the site, stood outside in the dark, took some deep breaths, and was wondering why I felt so good even though I was already exhausted.
Afterward, I ran around like a crazy man getting stuff ready. My team, most of whom I had not met before Saturday, rallied together. They were all looking at me for direction as if I knew what I was doing. Let me assure you, dear reader, that even though I had put in months of planning for this event, I had very little idea what I was doing. For one thing, the event didn’t start until 8:30, and we had attendees already there at 7am. What the heck? 7am? Everyone, attendees and my team, was totally patient while I got everything together for the group.
My team worked hard and made up for my nervousness and occasional constant lack of organization. They handled every problem, and there were no lines at all. Tons of people complimented me on my effort, and I know it’s a cliché where the leader gives all the credit to the team, but in this case it was true. Truth be told, there was a reason I was unfocussed. I was trying to figure out what I was feeling. It was exhilarating, my pulse was racing, and I wondered if something was wrong with me. This was the shift.
Just after 8am, Sri asked the core team to introduce ourselves during the opening session and state our contributions in planning the event. This was very classy and was the moment I would have focused on the most in my strategic mindset. This was my time to shine, to get credit for what I had done, the payback for all of the hard work.
However, truth be told I was distracted because I was worried about my team, that there may be last minute problems in which they would need me.
Sri continued informing the crowd how to get lunch, to wear their wristbands, etc and would occasionally tell people to see me if they were missing something from their bags. At least I think that’s what he said, because my mind was not there. When I heard my name, I raised my hand, but I wasn’t really listening. I turned and told another Core Team member that I was going outside to the team and I was out.
Now we’ve come full circle. I’m standing outside and my team is handling everything perfectly. Three women on my team impress me in particular (though every volunteer is valued). They are Hema Sridharan, Rachel Mitchell, and Aditi Tendulkar. They aren’t SQL Admins, they are wives of volunteers. These ladies gave their entire Saturday to be there, had the most boring jobs sitting the welcome desk, and never complained. They didn’t want to meet Joe Celko, Devin Knight, etc; they weren’t attending sessions, weren’t trying to be noticed. They simply gave their time and effort because it was the right thing to do. So, I’m watching them and there’s a lull in the activity and I stop to think while sweating in the sun. They were having as much fun as I was.
 The infamous Joe Celko and myself. We’re On a Mission From God.
BAM! In that moment, I got it – everything Brent, Trevor, Tim and other people had told me more times than I can count. Volunteering with the community is fun! There’s a reason they all work so hard at it, they enjoy it. It feels good to do it.
In my previous article, I listed the benefits of volunteering; however I got them in the wrong order. What I realized on Saturday is that volunteering with your user group makes you feel good about yourself and is fun to do. The rest of it- networking and branding- are much less important.
Of course, I took the opportunity and networked. I had in depth conversations with amazing guys like Devin Knight, Wes Brown, and Allen Kinsel. In fact, at the speaker’s party, I told a story to the great Joe Celko, which left even he at a loss for words. However, that stuff was just icing on the cake.
I went to the after party at Humperdinks and mingled with the people at the after party. It felt great, and I didn’t want it to end. I was there, nearly to the end, talking with Allen Kinsel, a long time PASS volunteer. I was talking about changing this blog and I was surprised when he and Ryan, another core team member, told me that they already read it and that I shouldn’t change it. I explained to Allen how I felt about the volunteer experience at SQL Saturday, and he looked at me as if to say “well, duh. It took you this long to figure that out?” Like I said before, I can be dense at times.
You Had Me at Hello.
What does this have to do with my career? I know where I am going and how I’m going to get there. I’m going to get more involved, participate in these kinds of events as much as possible, and give as much as I can to the community. I’ve already volunteered my help at SQL Saturday Houston and am working on two different presentations to give at future events.
It took me a long time, but yeah I finally get it.
I took the plunge this year and started volunteering for the North Texas SQL Server User Group. Originally I started attending because I wanted to learn more about SQL Server, but it didn’t take long before I realized that there was so much more to be gained from being there.
Why should you volunteer with your local user group?
You get to meet really cool people.
I realize that I don’t know most of you personally, but I think most of you are like me. The reason you read this blog is because you want to know more about your profession than the average person. That’s likely how you found me in the first place. The folks at your user group are just like us. They are passionate about their craft, fascinated by technology, and want to be the best they can be. However after you get to know them better, you’ll also find that many of them are caring and really fun to be around.
I can hear you saying, “Yeah David, but I can get all of that by going to the monthly meetings, why should I bother to volunteer?” I attended this user group for almost 2 years before I volunteered and had no idea who these people really were. I had talked to them of course, but you only get to know them as you work WITH them. You become a member of the club, feel like you belong, and they let their hair down.
You can network with some of the best minds in SQL Server.
Some of the best minds in SQL Server present at SQL Saturdays. They fly in at their own expense to share their knowledge and network with you. These are the people who write the books on SQL Server, MVPs and other luminaries. When you volunteer, you can get exclusive access to them.
Also, remember those smart, motivated folks in your local area that I mentioned above? On the volunteer committee I’m working side by side with two Microsoft MVPs and more folks who are on their way to becoming MVPs. I’m spending weekends volunteering with them and getting to know them well. These are the folks who can help you find your next job.
Do you want to eventually give SQL presentations? You should start by volunteering.
When I chose the name Made2Mentor, it wasn’t without a great deal of thought. I have always taught others and mentored them in something that I was good at. I’m learning SQL Server rapidly and intend to begin presenting on various aspects of it.
If you’ve already volunteered, and I intend to continue to do so, approaching the leader of the User Group is an easy matter because you’ve already worked extensively with him. I may even present a session at the next SQL Saturday.
You can learn so much from the other volunteers, maybe even find a mentor or two.
I’ve already received a ton of help from people on the volunteer committee that I would not have been able to get had I not volunteered. While I know you can get basic help from forums or Google searches, you get so much more from people with whom you’ve built a relationship.
Once you get to know them, they’ll take the time to look at your problem personally and guide you in the right direction. It’s not just technical help either. Many of us are very bright technically, but lack the soft skills. Many of the people who run these groups have already mastered the soft skills of interpersonal communication, and you can learn from them. It is only natural that they eventually become your mentors.
Volunteering makes you feel good about yourself.
I know that I often focus on the career aspects of networking, but it’s not all about your personal gain. It feels good to give to the community, to help the other volunteers without worrying about what you’ll get from it. There’s a real sense of achievement which comes from hosting an event like SQL Saturday and knowing that you had a part in it.
So, I encourage you to join your local SQL Server User Group and volunteer. If you have the opportunity to volunteer for a project such as a SQL Saturday, jump in and do your best. There are so many benefits, that you’ll find it’s totally worth it.
I’d like to my fellow members of the planning committee Sri Sridharan, Sean and Jen McCown, Tim Mitchell, Vic Prabhu, Ryan Adams, as well as the rest of the volunteers for SQL Saturday for making the experience great.
By the way, if any of you are coming to SQL Saturday Dallas, then look me up. I’ll be running all over the place, helping to get things done.
PS. To my regular readers, I apologize about my lack of blogging lately. Obviously I’ve been busy with SQL Saturday and I’ve had a great deal of pressure with my work the past few weeks. I’ll be back on track and will continue with the disaster recovery theme next week.
If you are a M2M Administrator, you should be studying SQL Server. I encourage anyone who works with M2M to study the SQL programming language, how SQL Server works, advanced reporting techniques, etc. In my opinion, if you don’t have at least a basic understanding of all things SQL, you cannot effectively administer M2M. I like to think that’s why many of you read this blog.
The Professional Association for SQL Server wants to help. On May 19th, they will offer 24 one hour long sessions on various aspects of SQL Server. These are some of the best minds in SQL Server and you can watch them free. Yes free, as in no strings attached, absolutely free. You can register here.
To encourage as many of you to participate as I can, I’ve listed the 10 sessions that I think M2M Admins should attend, and more importantly why.
- Manage Your DBA Career, Don’t Let It Manage You. This is what I’ve been talking about. Take control of your career. Run twice as fast. Don’t become cheap furniture. Protect yourself in this awful economy.
- Database Design Fundamentals Every SQL Administrator should have a grasp as to how to create databases, tables, and the rules of normalization. Watch the session and see how many fundamentals that the M2M Database violates.
- Introduction to PowerPivot PowerPivot is a hot new technology for Excel 2010 and SQL Server 2008 R2. Yes, I know you don’t have those yet, none of us do. However, PowerPivot is incredibly slick and powerful and umm… it makes Interactive Dashboards.
- Solving Common Business Problems With Microsoft PowerPivot. Wouldn’t it be nice if your users could create their own reports directly in Excel?
- PowerPivot for Financial Decision Makers Want your boss to do back flips? Show him/her PowerPivot with the slicer options and stand back.
- Easier-Than-Ever Report Authoring in SSRS 2008 R2 You’re not using SQL Reporting Services yet? Why not? I’m in the process of creating some instructional videos for you to see how to use it with M2M. In the meantime, get your feet wet with this demo.
- Reporting Services Enhancements in SQL Server 2008 R2 Aren’t you sick of those awful Visual FoxPro reports by now?
- High-Performance Functions Every admin needs to learn T-SQL. I’ve shown you how to create some simple functions in the past. Creating your own functions is an absolutely necessary job skill.
- Top 10 Mistakes on SQL Server Anyone who has ever used SQL Server has committed some of these. Watch this session and learn how to avoid them.
- What’s Really Happening On Your Server? 15 Powerful SQL Server Dymanic Management Objects Ever get the complaint, “M2M is so slow today?” Well, learn how to figure out why.
As an aside, you should all be members of PASS. Take advantage of these opportunities to learn. Join your local user groups and attend a SQL Saturday. Those are typically free as well. Volunteer for your local group, like I do. We’re having a SQL Saturday in Dallas later this month.
Network yourself, grow your skills, meet other geeks, recession proof your career, and have fun.
Something happened at work this week, which put a fine point on the current theme of most of my articles, which is preparedness; being able to recover from a disaster.
I currently support two different business units which use M2M, each separated by approximately 1400 miles. Yesterday morning we started getting reports of computers at our remote company going down. Approximately half of them simply dropped off the network. Normally, this kind of thing would not be my issue, but since the remote company uses M2M from our base via the internet, and the ERP system is absolutely vital, I was involved.
The computers would boot up, but would not “see” the network, etc. They also would not allow systems restore and other Windows functionality was crippled. What do you do in a situation like this? Well, I see it this way.
- Don’t panic, no matter who is yelling at you to fix it. Take a deep breath and form a plan before doing anything. You must remain focused to fix the problem and agitation will only slow you down.
- Ask yourself this simple question, “What’s changed?” A similar question is what is the difference between the computers which failed and those still working? Sure enough, if 30+ computers were working fine yesterday, and are dead today, something widespread has changed.
- Resist the urge to “try things” to fix the problem, regardless of whether they are a logical cause. This wastes time, and you may very well break something else while looking for a fix for the original problem.
- Don’t forget to check error logs, if applicable.
So, what did we do? Following those principles, we came to the following conclusions.
- It wasn’t an internet issue as half of the company was still working perfectly.
- Why was the remote company heavily hit while my site only had one machine die? This is not a hard and fast observation because the machine which died on my site was a laptop and all of the remote machines were desktops. It might have just been a coincidence. However, it turned out to be a crucial clue. It’s important to note that the remote company is two hours behind us.
- We eliminated the possibility of network switches being bad. We had someone at the remote site swap a bad and good computer for each other and both remained consistent.
- We took one of the bad machines and re-imaged it. This didn’t take long, and proved the problem was software.
- We started looking for viruses, though we have enterprise virus protection. Since half of the company “got it” it sure looked like virus activity. We found none.
- We started looking at our Windows Update server to verify that an update didn’t coincide with the outage.
- We checked our McAfee anti-virus software and found out that an update had recently been pushed.
Well, was the culprit and their anti-virus attacked essential services in Windows XP. People who know read this blog should know that I am really anal about updates. I don’t apply M2M, Crystal, or SQL Server Updates without testing. However, one doesn’t think that they have to guard themselves from their own anti-virus software.
What can be learned from this?
- Trust no one. Suspect every piece of software no matter how rock solid the reputation.
- Be prepared for anything. Most of us have backups of our servers, but are they any good if we lose our desktops? We have images for every computer we own and can roll them out at a moment’s notice, even 1400 miles away.
How long would it take you to re-image (or install manually) all of your desktops? We have around 75, and the time to re-image… about an hour.
This is the fourth of a series of articles discussing backup and recovery for M2M businesses.
Once again, I’m giving you a very high level overview of these concepts and I am not going to cover every option available, only those which are important to companies that use M2M. If you want to dig deeper into today’s topic, and for that matter any topics regarding SQL Server administration, I recommend you read articles written by the great Paul Randal on his blog In Recovery, and this article he wrote for TechNet Magazine last year. I’ve “borrowed” liberally from many of his articles to write this one. However, be warned that he’s a genius and he sometimes explains topics in such detail that his articles can induce brain aneurysms. As with everything in database administration, when I am asked questions about backup and recovery my answer is “it depends.” To make things easier, I’m going to discuss backup and recovery with a basic SQL Server set up as an example.
Before we talk about recovery models and backup types, we need to briefly touch on the basic structure of a SQL database.
Every SQL Server database has at least two files, a data file and the transaction log. Basically, every change made to your database is first made in the transaction log and is then written to the data files later. The transaction log is a sequential list of transactions and their effect on your data.
For our example, we’re going to assume that your transaction log and database files are on separate volumes (not just named drives but on separate RAID arrays.). For the sake of simplicity I will call those Drives, though they represent a set of drives working together in an array. So, for our example the server has the transaction log on one drive and the data files on a second drive.
Backup Types
Full Backup
A full backup creates a complete image of the database, and the database can be restored from just one file. The backup can be restored either on the original server or another SQL Server entirely. Every Recovery Model includes the use of full backups. In the examples which follow we are going to take our full backups every night at 10pm, but when you actually schedule them is up to you.
Differential Backups
Differential backups can be used with all three Recovery Models and they can be very helpful if your database is large and takes a long time to backup. Differential backups only contain data which has been changed since the last full backup. It’s a bit more complicated than that, but as I said, this is a high level overview. Differential backups are not incremental, they are cumulative. Consider our example above where we are taking full backups every day at 10pm. If we took a differential backup every 6 hours, and the database was being updated continuously, then the differential backup at 4am would contain 6 hours worth of data. However, the 10am differential backup would contain 12 hours of database changes, not just an additional 6. There is a diminishing rate of returns with Differential Backups for this reason. Differential backups are particularly useful with the Full Recovery Model (more on that later).
Transaction Log Backups
Unlike differential backups, transaction log backups are incremental and are a record of all transactions against a database since the last transaction log backup. Transaction log backups are only necessary with the full and bulk logged recovery models.
Recovery Models
To explain the effect of each Recovery Model, let’s assume that we are taking a full backup of the database at 10pm every night, and we’ve had an issue at 4pm during a work day. The issue could be software related, user error, or a hardware failure that requires a restore from backup.
There are three recovery models for SQL Server, but we are only going to discuss two of them (simple and full) because the third is unlikely to apply to M2M companies.
Simple Recovery Model
In the Simple Recovery Model, the transaction log is cleared automatically as data is committed to the data files. There are some exceptions, but remember that this is a high level overview. This makes backup and restore easier because you do not have to worry about backing up the transaction log. However, with this model you can only recover to your last full or differential backup, there is no point-in-time restore.
So, your server fails at 4pm and you attempt to restore your backups. The best case scenario is 18 hours of productivity lost. For some businesses, this is acceptable. If this is an acceptable risk for your business, then feel free to use the Simple Recovery Model.
Full Recovery Model
In the Full Recovery Model the transaction log space is not cleared until it’s backed up. Once again, it’s a bit more complicated than that, but I’m trying to simplify concepts for this article. If the transaction log isn’t backed up, it simply grows until it either runs out of disk space (most likely option) or reaches the threshold set for its maximum size. M2M and other programs using SQL Databases become unusable until it’s corrected.
Using our example, let’s assume you were taking transaction log backups every fifteen minutes. Each transaction log backup is incremental, which means that each backup must be retained, and in the event of failure, must be applied in order which is called a log chain. Each chain must start with a full backup and continues until broken or until the last transaction in the log. If any one of those transaction log backups is bad, then the chain is broken and you cannot restore further than that.
If your database server loses the drive containing the database file, but the transaction log drive and the drive where the t-log backups are located are fine, you should be able to restore back to the moment of failure, and committed transactions would not be lost. If both your database file drive and transaction log drive failed, and your full backup and transaction backups were viable and on a separate server, then the most you would lose would be no more than fifteen minutes of work, which is acceptable to most M2M companies. Let’s go through the steps to recover in the above scenario. The admin would need to:
- Create a “tail of the log” backup. This backs up any remaining transactions since your last transaction log backup.
- Restore the last viable full backup.
- Restore the entire log chain since that last full backup including the “tail of the log” backup. This requires restoring 72 (18 hours worth) of log files which is time consuming and tedious.
Differential backups can particularly important in the full recovery model. Remember that they are cumulative. This makes the restore path much shorter. For example, what would happen if we were doing differential backups every 2 hours and we lost the data drive at 4pm? Well, you would do the following:
- Create a “tail of the log” backup. This backs up any remaining transactions since your last transaction log backup.
- Restore the last viable full backup from last night.
- Restore the last differential backup which was taken at 2pm
- Restore 8 transaction logs to get you back to 4pm.
Differential backups restore much more quickly than parsing through the corresponding transaction logs and it’s a more straightforward process to boot. However, since most M2M databases are not very large, most companies I’ve dealt with do not use differential backups. In fact, in my experience the vast majority are in Simple Recovery Mode though my polls don’t reflect it.
Backup Location
The last important aspect of database backups is where to place them. Many experts I’ve talked to over the year have differing options, but my method is this. Create your backups in a local share on the server, but then immediately copy them to a network share, not a local drive. If your server dies, and your backup files were on it, you’ve got a serious problem. Save your backups on your file sharing server (or another server) so that if your database server dies, you can quickly restore to another.
In a future article I’ll discuss database file and backup corruption issues. Yes, I know that you were told that data corruption was a thing of the past when you migrated from Visual FoxPro to the SQL versions of M2M, but that is patently untrue. Stay tuned for details.
In the last article in this series, I discussed the commonly used options for Backup options and stressed that off-site backup were critical for disaster recovery. However, I also pointed out that the size of your backups combined with how many past backups you need to retain can be cost prohibitive. In this article, I’ll provide a high level overview of compression, security, and encryption of backups in SQL Server. I’ll cover SQL Server 2000 through 2008, since I know that a very large percentage of us are still using SQL 2000.
What is available natively in SQL Server?
If you are working with SQL Server 2000, your options are limited. SQL 2000 offers neither database nor backup encryption natively. Why does this matter? SQL backups can be read by an ordinary text editor. It’s not as easy as reading a Tom Clancy novel or anything, but it’s not that hard either. While it is true that you can password protect your backups, this is of little value. To demonstrate, I created a practice company (M2Mdata33) with the M2M sample database. I backed up the database with the following script. Notice that I’ve applied the password ‘bleh’ to it.
BACKUP DATABASE M2Mdata33 TO DISK='c:\M2MPass.bak' WITH MEDIAPASSWORD = 'bleh' I opened the file in Notepad and found the following:
I can hear you saying, “Umm… David, you said it was human readable.” Actually all of the data in the file is readable. For example, when I try to search for any of the customer names in the educational database such as “Johnson Electric”, the following pops up:
Not exactly secure, huh? While it is true that if you attempt to restore the database without the password, you will receive an error; it’s not really secure. Unfortunately things didn’t improve much for SQL Server 2005. Database encryption was added, but it is applied on a column by column basis and from what I’ve been told, it’s a pain in the rear to administer. Backup encryption and compression were not addressed.
In SQL Server 2008, Transparent Data Encryption (TDE) was added which allows automatic encryption of your entire database. As the name would suggest, the encryption is transparent to both the users and the application, so in theory M2M would have no idea you encrypted its database. According to the demonstrations I’ve seen, the performance impact is negligible as well. I have not tested this and I only mention this option because I’ve received a couple of questions about it, but it doesn’t really pertain to M2M companies. TDE is an Enterprise Edition only feature, and I’ve never known a M2M customer to use SQL Enterprise as it’s prohibitively expensive. Backup compression is included in 2008 as well, but again only in the Enterprise Edition.
What about 3rd party products? What is the right way to handle backup compression and encryption?
There are two ways to compress and protect backups. There are several 3rd party options for SQL Server backups, and two of the most widely used are RedGate SQL Backup and Quest LiteSpeed. To be fair, I’ve not used either in production and have only experimented with these products on test machines. They both offer the following basic benefits:
- Increased Speed for backup and restore.
- Backup encryption is performed on the fly so the backups cannot be read without authorization.
- Backups are compressed around 90% according to the preliminary testing I performed on M2M Databases.
- Specific database structures such as tables can be restored rather than the entire database.
- Backup and restore process is simplified. More on this in a future article.
The only drawback is the cost of these products. Expect to pay anywhere from around $300 to $1500+ per SQL server for them.
What is the second way to handle backup compression and encryption?
What can you do if your boss won’t pony up and you need compressed and/or encrypted backups? Well, you could use 7 Zip, a free program. In testing, it compressed my backup file more than 90% and allows AES-256 Encryption and password protection. It compressed a 4gb database to 276MB, but took 20 minutes doing so. However, it is important to note that testing was performed on a retired PC with IDE drives. This product can be run from command line so it can be scheduled with the proper parameters and run every night after your backups complete. I’m setting up a system using this so long term backups can be stored on site rather than on off-site tape.
Other encryption options.
Most online backup companies support encryption, but this doesn’t protect the backup files that remain on your server on site. Also, some tape backup systems offer automatic encryption as well, but have the same drawback.
That’s great, but you still haven’t told me how to backup a database yet.
In the next article, we’ll discuss the Database Recovery Models for SQL Server and how to choose which is right for you.
What about you? Are you concerned with your backup security or the space required to store backups?
Page 2 of 20«12345»...Last »
|
|
Popular Articles