SQL Server Hall of Shame

Shame - @SeniorDBA

In a recent post by Adam Machanic, he asked his followers to send him the items they thought were the worst features of SQL Server. The list he developed is called the “SQL Hall of Shame”. He put together the following list:

  • In-Memory OLTP
  • English Query
  • Data Quality Services (DQS)
  • Master Data Services (MDS)
  • Auto-Shrink
  • Auto-Close
  • Auto-Grow
  • Notification Services (SSNS)
  • Query Notifications
  • Buffer Pool Extension (BPE)
  • Management Data Warehouse (MDW) / Data Collector
  • Lightweight Pooling / Fiber Mode
  • JSON
  • SQL Server Management Studio (SSMS)
  • Connect to SSIS from SQL Server Management Studio
  • DROP DATABASE IF EXISTS
  • Columnsets (and Sparse Columns in general)
  • SQLCLR
  • Utility Control Point (UCP)
  • Raw Partitions
  • Uninstall
  • Service Broker (SSB)
  • Not Freeing Allocated Memory Except Under Pressure
  • Database Engine Tuning Advisor (née Index Tuning Wizard)
  • DBCC PINTABLE
  • Virtual Interface Adaptor (VIA) Network Protocols
  • Mirrored Backups
  • SQL_VARIANT

If you read the article by Adam Machanic, you’ll get the detail for each item on the list.

Microsoft Plans Office 365 Upgrades

Office 365 - @SeniorDBA

A few months ag0 Microsoft announced that Windows 10 would receive major updates just twice a year, scheduled for September and March. Based on feedback from enterprise customers wanting a more tolerable schedule, Microsoft moved to make their release schedule more predictable.

What some people missed is that they also announced an identical schedule for corporate subscribers to Office 365. They aligned the update schedule with Windows 10. Microsoft says they plan to deliver and support Office 365 ProPlus updates, starting in September.

Microsoft also extended support 50% from 12 months per update to 18 months. The additional six months means your IT team can choose to push updates just once or twice a year.

Office 365 update channels, showing the new update channel names and release cadence

The twice-a-year feature updates will be named Semi-annual Channel (Pilot) and Semi-annual Channel (Broad), each describing how Microsoft envisions them being deployed in the enterprise. Most people will probably just refer to them as simply “Pilot” and “Broad”.

You can get more information here.

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.

Free Download: SQL Server Management Studio 17

SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

Microsoft has announced the latest release of SQL Server Management Studio (SSMS) in May. This release features improved compatibility with previous versions of SQL Server, a stand-alone web installer, and toast notifications within SSMS when new releases become available.

Get it here:

Download – The version number for the latest release is 14.0.17099.0

New in this release

  • Over 100 connect issues fixed
  • SQL Server PowerShell module moved out of SSMS and into the PowerShell gallery
  • Icons updated to support high-DPI display modes
  • Numerous performance improvements in Object Explorer
  • WSUS support for upgrading to future 17.X versions
  • Improved Multi-factor authentication support
  • Enhancements to Availability Group functionality (Support for SQL Server on Linux, direct seeding, endpoint URL handling, and more)
  • Showplan enhancements including new feature to analyze actual existing showplan to help diagnose issues
  • New DAX Query Windows
  • Numerous other enhancements for Power Query support

Ransomware: WannaCry Malware Review

WannaCry Malware

The WannaCry ransomware was first noticed on May 12, 2017 and it spread very quickly through many large organizations, infecting systems worldwide. Unlike other ransomware, this sample used the SMBv1 “ETERNALBLUE” exploit to spread. “ETERNALBLUE” became public about a month prior when it was published as part of the Shadowbroker archive of NSA hacking tools.

Prior to the release of the hacking tool, Microsoft had patched the vulnerability as part of the March 2017 Patch Tuesday release. The patch was released for only supported versions of Windows. In response to the rapid spread of WannaCry, Microsoft eventually released a patch for later versions of Windows as part of MS17-010, going back to include the still popular Windows XP and Windows Server 2003.

One way to detect the spread of the malware was the significant increase in activity on port 445. The increase was caused by infected systems scanning for more victims. It is still not clear how the infection started. There are some reports of e-mails that included the malware as an attachment, but at this point no actual samples have been made public. It is also possible that the worm entered a corporate network via vulnerable hosts that had port 445 exposed to the internet. The WannaCry malware itself doesn’t have an e-mail component.

At startup, the malware was first checking if it can reach a specific website at http://www.iuqerfsodp9ifjaposdfjhgosurijfaewrwergwea.com, but it can no longer be assumed that newer versions will still demonstrate this behavior. This was a simple “kill-switch”, since if it found the site it would stop operations.

Eventually, the malware would create an encryption key and encrypt all the user files on the infected PC to prevent normal user access to those files. The idea is to force the user to pay a fee to recover the files they no longer could access.

Encrypted files use the extension: wncry. To decrypt the files, the user is asked to pay $300, which increased to $600 after a few days. The ransomware threatened to delete all user files after a week waiting period.

In addition to encrypting files, the malware also installed a “DOUBLEPULSAR” back door. The backdoor could be used to compromise the system further. The malware will also install Tor to facilitate communication with the ransomware author.

New variants have already been reported with slight changes to the kill switch domain and other settings. There is also a decryption key that can be used on many systems, but prevention is always better than sarching for recovery options.

If your version of Windows was supported and you installed all available patches from Microsoft, your system would not have been infected. Microsoft also announced that the new “Windows 10 S” would help prevent ransomware infection as it will only run software purchased from the Microsoft Store.