Six Keys to Avoiding Data Loss

At Roundbrix, we take data very seriously. After all, it’s the only thing we really cannot replace. Given that, there’s a few safeguards that we recommend so your data sleeps as well as you do!

1. Know Where Your Data Lives. This may sound silly, but what if you had nightly backups running, lost something, and went to restore the data only to find out you weren’t backing it up? Folks, this happens more than you would ever want to know. Often times data is on desktops when it should really be on a server where it gets backed up. Good company policy and procedures with occasional QA here is best practice.

And remember, if you install a new program on the server, create a new volume on a server or a new database, know that it has to be selected to be backed up. Just because it’s on the server doesn’t mean it’s backed up!

2. Backup Nightly with a Verify Pass. This is key to ensure that your data is restorable from the media. We used to call this “read after write” as it simply verifies that what is believed it wrote is indeed what can be read back. A simple check box verifies that all is well come ‘restore‘ time!

3. Take a Backup Offsite at Least Weekly. This is where you have to think through your pain threshold for data loss. In other words, if you’re building burned down, would losing a week’s worth of data make it even more painful? If the answer is an emphatic ‘YES’, then that means you need to take a tape offsite more frequently than weekly. Do remember a couple of things about taking tapes offsite. First rule is they don’t survive well in heat or moisture so get a proper transport and storage container. Second rule is ensure it is secure as the tape includes all your company information and if misplaced, creates a liability. You’ll want to account for your tapes periodically to ensure one didn’t ‘fly the coup’. If you’re a Roundbrix client, you can use our secure biometric-secured facility to store your backup data as frequently as you would like.

4. Periodic Reviews of What is Being Backed Up. This little step that we like to do every quarter to catch new areas created. Face it, we get busy and things fall through the cracks as we’re only human. Also, we occasionally find new databases that not only aren’t being backed up, but not set up properly for maintenance, safety and controlling growth. To know how to set up a database maintenance plan, see the article we previously wrote here.

5. Redirect My Document Folder to Server using Group Policy. Desktops and laptops will fail, but what’s important is that it does not take sensitive company data with it. Also, if it gets stolen or misplaced, has this just put your entire company at risk? Folks, keeping autonomy between the company data and the desktop/laptop device any more than absolutely required is simply good business. One of these failing should not send dangerous ripples through an organization like “Oh, Ed had all the company financials and client lists on his computer and now they are gone!”

6. Know How You Would Restore From Scratch. In a crisis situation, this is not the time to “figure things out”. We have “been there and done that” so rest assured we can get you back to where you need to be is short time. After all, the name of the game is first prevention, and secondly restoration. If the restorations took a month, what would be the point?

Current Projects

  • Access to SQL Database Conversion
  • Server upgrades
  • High Availability Firewall upgrades
  • Network Engineering and Performance Monitoring
  • – MAS Migrations

SQL Database Best Practices!

SQL MAINTENANCE PROGRAM:

Having a good solid maintenance strategy as part of your SQL Server database is a necessity for any company with a business critical application. It can be the difference of being down for a few minutes vs. being down for an entire day or several days. It can also manage space on the server better and if done correctly, can improve performance of the database dramatically.

The first part of a good database management strategy is to plan your backups. For a database that is frequently used and important to the company we recommend the following standard maintenance plan. In general it’s a good idea to do a full backup twice a day and transaction log backups hourly so you can easily restore the database up to the hour within minutes.

1. Back Up Full Databases Twice Daily

There are three Recovery Models

a. Full Recovery Model   –   What Roundbrix mostly uses. Safest mode of operation for production systems.

b. Bulk-Logged Recovery Model   –   Has minimum logging for bulk import operations. Space allocation and deallocation is only logged for bulk import operations. Basically a few limitations.

c. Simple Recovery Model   –   No transaction log maintenance needed. Recoverability of the database is very limited to a specific time frame.

If a database doesn’t change often, Simple Recovery Model may be an option. (i.e. a database that imports all the data from Quickbooks from previous day, etc.). The data is only used for reporting, so the Simple Recovery Model would work fine if timed properly. (after data is inputted from QB).

2. Back Up Transaction Log Files Hourly

A database has two components, data file(s) and transaction logs. A transaction log captures the modifications made to the database. A SQL server must have at least one transaction file.

With the Full Recovery Model, it is important to backup the transaction logs frequently so the database can be restored up to the point in time when the problem occurred. A transaction log backup will truncate the inactive portion of the transaction log.

The transaction log can get large and the file size will not be reduced when it is truncated after a transaction log backup. This simply shifts the pointer within the existing file as to where the space is to be freed up.

3. Rebuild Indexes Weekly

Rebuilding indexes should be done weekly during off-hours as this can sometimes be intensive and slow down the database. It is important to rebuild the indexes to optimize performance of the database. You can think of this like defragmenting your hard drive. After it is completed, everything runs just a little smoother and faster because space is optimized properly (doesn’t take as long to figure out where a record goes during an insert or update).

4. Update Statistics Daily

It is also a good idea to create a maintenance plan to update statistics daily. This optimizes space in the tables and can have an impact on improving performance of the database. If this never gets done (which sometimes happens in databases that are not maintained) performance can slowly degrade until it becomes a serious problem, adversely affecting application performance.

TYPICAL BACKUP PLAN FOR A DATABASE THAT IS BUSINESS-CRITICAL:

PROPER AND OPTIMAL DATABASE DESIGN:

One of the most common problems we’ve seen is applications where a query takes over 30 seconds only to find that one of the fields in that query has not been indexed. Beyond looking at the queries in your application to make sure the fields that are in your “WHERE” clauses are all indexed, there are some tools that can help. Using Microsoft SQL Server Profiler in combination with Database Engine Tuning Advisor is the easiest way to find slow queries and see what indexes may be missing, which could help improve the performance of your application.

For SQL Server Profiler first, select “Tuning”. Next, select the “Events Selection” tab, and then the “Column Filters…” option and “Duration”. Select the Greater than or equal to 3000, as we want to see queries taking over 3 seconds.

After about an hour of running the profiler, stop it. You should save the file to one that can be imported into a “Database Engine Tuning Advisor”. Run it to have the tool point out what can be optimized. Usually you can accept most of the suggestions from Database Engine Tuning Advisor and see a dramatic increase in performance.

The other important thing to look at in a database is how well normalized it is. Normalization is important so that information is not duplicated. One example is a company we worked with used 12 different excel sheets to keep track of similar information. However data that was supposed to be consistent throughout the different excel files was spelled differently and listed multiple times where it should have referenced by a table with a unique ID and one consistent value. We moved these 12 excel sheets into one database and created separate tables for the columns so information was normalized. This allowed the customer to do more sophisticated and accurate queries on their data.

The information above, when properly normalized, should be put into three tables as shown below. This way, “Hardy Boys” only needs to be updated once.

PROPER NAMING CONVENTION IS ALSO IMPORTANT:

Naming a foreign key differently from the primary key that it references can be very confusing to programmers. If the fields are named properly and referenced properly, it can save countless hours of confusion. I typically name the primary key as the table name with “ID” after it. So if Book is the table, BookID is the primary key. This is a pretty common way to name primary keys which will make it easier for programmers to understand more quickly.

It is also important to make sure you keep an ER (Entity-Relationship) Diagram. This is very easy with SQL Server by right clicking on Database Diagrams and selecting “New Database Diagram”. Then you can link the primary/foreign keys here and also add/update fields to the database-making database management tool very easily. Make sure all the tables are in the diagram and connected properly to the other tables that they should be connected to.

By following the tips above, you should have a solid robust database that will allow your company to grow without having to worry about database issues. For a free database analysis call the experts at roundbrix at 949.273.5200.

Ed

Ten Ways to Slash IT Costs!

Internet Circuit – Important to know is which carriers provide service to your building and their transport offerings. For reliability, fiber rules the roost. But some areas have more than one carrier so you need to compare offerings. Also realize that you don’t have to wait for one contract to expire to re-up at a better rate, if you plan on staying with the same carrier. Additionally, know what offerings are available as a change in architecture may make doing business more cost effective. For example, using offerings like Cox’s Metro-E offering which connects one site to another via Layer 2 Ethernet.

Unused phone lines – Go through the bills and call every number on there. Remember you’ll have a few lines that may be dedicated to your burglar alarm, elevator, or fire notification system. Other than that, you need to know where they all go and lose the ones that are not in use.

SIP Trunks or Voice PRI versus CO Lines – On the telephony side, if you have more than 10 lines, you may want to compare the cost to see what SIP trunks or a dedicated PRI may cost. If you have to add a piece of equipment, often times the carrier has a promotion and will chip in here. Remember, their goal is to get you off of analog lines as we move to the all-digital world.

Server Warranties – Deciding out of the gate when purchasing a server how long you will keep it will always benefit you, as opposed to extending at a higher price a couple years down the line. Face it, when cutting the initial deal to get the server, it’s more aggressive discounting. But once you’re on it, you lose the leverage on price negotiation as they know it would always be less expensive to renew a contract for server maintenance then bite-off another server deployment.

Deployment Turns – Getting these reduced makes a ton of sense. Let’s look at a 10-year period. If you replace servers every 3 years, it’s three turns in that ten-year period, but every 5 years, it’s only two turns. If you push a server to seven years, it’s only one turn every ten years for the first couple decades. Exchange servers, as one example, are typically goof for 5-7 years.

Printer Consumables – Face it, the cost of the printer is rarely an issue. This day in age, with paper reduction a strategic initiative, it makes sense. The goal should be have fewer, strategically-placed printers going forward and limit color. And it’s not just the cost of the paper and paper-handling printers, it’s the ink and the time for finding that one piece of a paper in our paper jungle. Time to kick this paper habit.

Multi-Year Domain and SSL Renewals – Instead of doing this every year, do it every so many. You not only get a break for multi-year, you’re not revisiting the same task every year. SSL certificates should only be extended until the end of the useful life of your Exchange server.

Buy Second Hand Equipment – Yep, you heard me right. Often times, we run into situations on two-to- three-year old hardware. We had an APC environment monitoring system that crapped out. We probably spent $1,500 on the unit, $500 on different probes for water detection, humidity, and others, plus programmed it meticulously, saving the configuration file of course. It simply crapped out. To replace it would cost me about $2k plus the time to learn a new system, and reprogram/fine tune it. Instead, I found a used one on eBay for $100 and voila, the out-of-pocket cost to get back to where I was came to a couple hundred bucks, labor included and we simply had to reload the our configuration. Also, try the folks at MetrolineDirect.com for used phone equipment.

ElectricityAll the newer UPS and Servers can run on 208VAC. This will save about 15-20% on your electric bill as it pulls down half the amps or less. Also, if PC’s can be turned off, especially on weekends when interior air conditioning may not be operating and interior office temperatures can soar, causing PC failures. Printer and copying machines nearly all have a sleep mode as well that can save a bit.

Labor Smart – Often times, I see a high-end IT tech pulling cables, or doing more menial tasks, even though their backlog is huge. This just does not make business sense. Reviewing to ensure you have your most cost-effective resource, even if that means getting someone outside to perform the task. Face it – you don’t use a pickup truck when a Prius will do!

We also hope you will all enjoy a great Fourth of July weekend!

Ed