Transaction Log File Size and VLF in SQL Server

This article by Kimberly Tripp is very interesting. Simply put, she says you want the initial size of your transaction logs set to 8 GB, with auto growth set to 8 GB. This should help keep your Virtual Lof File (VLF) sizes below 512 MB, improve performance, and make maintenance during backups much faster.

VLF Image

The article, in part, reads:

First, here’s how the log is divided into VLFs. Each “chunk” that is added, is divided into VLFs at the time the log growth (regardless of whether this is a manual or auto-grow addition) and it’s all dependant on the size that is ADDED not the size of the log itself. So, take a 10MB log that is extended to 50MB, here a 40MB chunk is being added. This 40MB chunk will be divided into 4 VLFs. Here’s the breakdown for chunksize:

chunks less than 64MB and up to 64MB = 4 VLFs
chunks larger than 64MB and up to 1GB = 8 VLFs
chunks larger than 1GB = 16 VLFs

And, what this translates into is that a transaction log of 64GB would have 16 VLFs of 4GB each. As a result, the transaction log could only clear at more than 4GB of log information AND that only when it’s completely inactive. To have a more ideally sized VLF, consider creating the transaction log in 8GB chunks (8GB, then extend it to 16GB, then extend it to 24GB and so forth) so that the number (and size) of your VLFs is more reasonable (in this case 512MB).

You should visit Kimberly’s blog entry for more information. You can also get more information about Virtual Log Files here.

Advertisements

Ransomware Lessons

USB Hacks - @SeniorDBA

Ransomware is malicious software that attacks a computer or your entire network to force you to pay a fee (ransom) to regain access to your systems. If the fee is not paid within a set timeframe, the criminals who now has access to your systems will wipe the data. Since those systems are unavailable to your organization most businesses are faced with a decision to pay the ransom and get back to business or refuse to pay the ransom and risk forever losing customer data.

Like any other virus or malware the ransomware is usually downloaded from the internet, most often by clicking a suspicious link in an email or on a website.

A recent report showed that victims of malware paid about $24 million in ransom to these cyber-extortionists in 2015. That doesn’t include the millions of dollars paid for securing the remaining systems, replacing damaged systems, training, etc. Since there is money to be made by criminals, this form of attack is not going away anytime soon.

What lessons have we learned that can help protect your systems?

  1. Backup Everything Your essential data should be backed up to prevent the loss of that critical information. For your personal systems, that probably means all your photos, documents, etc. need to be saved in a location that isn’t on your laptop or tablet. For business systems, that probably means all your customer data, documents, payroll data, and business knowledge needs to be saved in an off-site location. In the event of an attack, you simply wipe your computer systems and start from scratch via the last uninfected backup. Having a backup of all your data and files won’t protect you against being infected by ransomware, but it will significantly limit the damage from an attack that deletes or encrypts your data.
  2. Avoid Suspicious Links and Attachments – Criminals often rely on your curiosity to click on a link or attachment sent to you via email. You just need to ignore emails from people you don’t know, and never click on a link or open an attachment unless you are expecting the information and it comes from a trusted source. I know this is easier said than done, especially if you are in a position to get emails from strangers all the time. Be careful about clicking on any links that come via email, even if they appear to come from your bank or other trusted source. It’s safer to type in the URL directly into your browser so you’re absolutely sure you’re going to the correct site.
  3. Apply Vendor Updates Many people have an immediate reaction to a new malware outbreak: Why hasn’t someone prevented this attack before it hits my computer? They probably have figured out a way to block the attack and made it available to you for free, but you may not have applied the update to your system. Make sure your systems are configured to automatically apply vendor updates as quickly as possible.
  4. Anti-Virus Software – Install and update your anti-virus software. This software is never going to be 100% effective in blocking everything, but it can help prevent infection by common threats.
  5. Disconnect Infected Systems – Once you suspect your system may be infected, notify your IT department and get offline (unplug the network cable or disable Wi-Fi) as soon as possible. Once an infected system is quarantined it can’t be used to attack other systems on your network and that means you will save time and money during the cleanup process.
  6. Be Prepared to Wipe Systems – Assume you will get infected. Your fastest and cheapest option is to wipe the infected systems, reinstall the OS, and restore your important files from backup to the fresh systems. This means you must have a disciplined system for creating, verifying, and testing periodic critical system backups. You must also be prepared to reinstall client software, which means having easy access to installation software, license keys, configuration settings, etc. You may not be able to completely avoid this step by following the other steps listed above, but you can seriously reduce the likelihood of this option if you follow the other steps.

WannyCry - @SeniorDBA

Not being infected by ransomware today doesn’t mean you are safe. This should be a wake-up call that tells you that you are not safe. While there is no way to guarantee that you’ll be safe from the changing nature of cyberattacks, there are some pretty easy ways to minimize the risk to yourself and your business.

Spam and Outlook

Microsoft Outlook - @SeniorDBA

Many people don’t understand how a spam filter works, especially with the email software from Microsoft called Outlook. In my experience, people are confused about how emails are blocked, or how emails are filtered into the Junk Email folder inside Outlook.

Generally speaking, your email server is usually used to block common unwanted emails, known as spam. This means the email server has the ability built into the server software to detect and filter (block) emails from being delivered to your email interface, or there is some additional software installed and configured to perform that filtering process. This means less unwanted email is delivered to your inbox.

There is an additional feature built into Outlook that looks at the emails delivered to your Outlook client to determine if it should block the email and redirect it into your “Junk E-mail” folder.

Junk E-Mail - @SeniorDBA

Any email forwarded from your email server (usually Exchange, but could be Gmail, Yahoo, etc.) but identified as spam by our Outlook client will be automatically moved to your “Junk E-mail” folder. Depending on your spam filter settings inside the Outlook Options, you may find you missing emails in this folder. You may disable the filter, but that doesn’t mean all your emails will now be delivered to your Outlook inbox.

As we discussed already, the spam filter on the email server could have blocked the email, Outlook may move the email to Junk E-mail, or even your anti-virus software might have blocked the email. If you work with your team in you IT department, they have tools available that can tell them if the server ever received the email, if it was forwarded to our computer, if it was intercepted by your anti-virus software, etc. They will need to know the address of the person sending you the email, when it was sent, and the subject line (when known).

How can I disable the Outlook spam filter?

How can I mark emails detected as spam by Outlook as “not spam”?

Effective Disaster Recovery Planning

Server Stack - @SeniorDBA

In your business, you might be the only one tasked with understanding what types of disasters can strike your business and assigned the responsibility of planning to prevent those disasters from bringing down the business. As Alan Lakein said many years ago, “Failure to plan is planning to fail”. As an information technology professional, one of your many tasks is to understand the risks to your business systems and plan to prevent or overcome those risks from impacting your business.

About 40% of businesses do not re-open after a disaster and another 25% fail within one year according to the Federal Emergency Management Agency (FEMA). Similar statistics from the United States Small Business Administration indicate that over 90% of businesses fail within two years after a disaster.

Understand The Risk

Do you fully understand the risks to your business? Have you looked at the systems your business uses and depends on each day and thought about what would happen if those systems were unavailable? Have you thought about the common risks for the area? These risks could include tornadoes, earth quakes, hurricanes, floods, etc.

Disaster Map - @SeniorDBA

Maybe there are man-made risks unique to your location, like frequent power outages, dangerous break-ins, poor building construction, etc. Each of these unique threats can be just a dangerous as natural disasters. You don’t want someone stealing your servers or hard drives in the middle of the night, or cracks in the walls leading to mice chewing through your network or power cables.

Written Plan

You need to think about each of the risks scenarios, and write down your plan for how you and your team would address each scenario to keep the business up and running with minimal down time. You may have to adjust the plan to address concerns about cost and time, and there may be periodic changes as systems and risks change.

  1. List of Employees (what they do, when they do it, why they do it, etc.)
  2. Inventory Systems (office equipment, servers, laptops, etc.)
  3. Office Space Requirements (you will need space to restore your systems, but can everything be done remotely, or will the users need office space to access restored systems)
  4. Insurance and Budget Concerns (who will provide money during an actual recovery)
  5. Share The Plan (make sure you aren’t the only one with a copy of the plan, and make sure the plan can survive the disaster)

Testing

Just like database backups aren’t useful if you can’t restore them, a Disaster Recovery Plan is worthless if you can’t implement the plan. You should conduct a formal test at least once each calendar year, testing if the plan will work for one or more of the scenarios you are planning against. The test should be a realistic as possible, and make sure you have a method of measuring the level of success.

There will be issues, like a system that wasn’t included in the written plan or a technical issue that you didn’t know existed. An issue could be something a simple as unknown system passwords or a missing software installation key. But that is what a test is all about. You have to test to find those little things that were forgotten or unknown, and then update the written plan to make sure it isn’t an issue during the next test. Eventually you will have everything you need addressed in the plan, and the next test will go smoothly. That means in the event of a actual disaster, when your team is confused and under an elevated level of stress, you are more likely to get these core production systems up and running quickly.

Team Meeting - @SeniorDBA

Don’t allow your business to fail because of an interruption you could have resolved with the proper planning and some simple testing.

Economics of the Cloud

Cloud Economics

For most companies, maintaining a large IT presence implies large capital expenditures and a non-trivial amount of accounting and record-keeping to track depreciation, tax considerations, and so forth. When you purchase the hardware and the software, they become yours (in every sense of the word) and your long-term responsibility.  The traditional model of enterprise computing is a capital-intensive function that requires expensive data centers (electricity, air conditioning, servers, networks, storage, etc.) and operations staff (hardware swaps, networks, backups, OS updates, upgrades, etc.) to keep it all running effectively. With an on-premises data center, you must plan and provision for maximum utilization, which is financially inefficient.

Data Center

The appeal of cloud computing includes the ability of enterprises to pay for only what they use. If demand decreases and you no longer need the assigned capacity, you can turn off systems and you are no longer charged for those systems. Since the cloud is a subscription-based model, it is an “operating expense” model. Computing becomes a service for which businesses are billed a monthly charge that is metered by actual usage. The more (compute, network, and storage resources) that you use the more expensive your monthly bill. The less you use, the less you will be charged.

Another way to save money is cloud operations frees your enterprises of the costly tasks of system backups, routine network maintenance, software patches, etc. because you cloud provider can handle these tasks.

Azure Spend

Most IT organizations find wide variations in system utilization. Some applications are seasonal and other applications run for a short period of time before being shut down. You might have other applications that are simply unpredictable and you can’t apply a cost saving model.

Building your server infrastructure in a cloud environment can save your business money and allow for greater innovations for less money.

 

Best Hacking Tools Of 2017: Nessus Vulnerability Scanner

Nessus Vulnerability Scanner

Developed by Tenable Network Security, this tool is one of the most popular vulnerability scanners on he market. Tenable provides different versions, depending on your needs: Nessus Home, Nessus Professional, Nessus Manager, and Nessus Cloud.

You can use Nessus to scan multiple types of vulnerabilities that include remote access flaw detection, misconfiguration alert, denial of services against TCP/IP stack, preparation of PCI DSS audits, malware detection, sensitive data searches, etc. Nessus can also call a popular external tools.

Nessus is supported by a variety of platforms including Windows, Mac OS, and popular Linux distributions like Debian, Ubuntu, Kali Linux, etc.

You can get more information and download the Nessus Home (free) tool here. The commercial version is available here.