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.

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.

 

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

Best Hacking Tools Of 2017: Nmap

Nmap, a simple Network Mapper, is a powerful port scanner tool. This free and open source hacking tool is the most popular port scanning tool around that allows you to easily perform network discovery and security auditing. Used for a wide range of services, Nmap uses raw IP packets to determine the hosts available on a network, their services along with details, operating systems used by hosts, the type of firewall used, and other information.

Nmap is available for all major platforms including Windows, Linux, and OS X.

We have written about how you can use this simple tool to find SQL Server instances on your network.

SQL Server Version Information

SQL Server - SeniorDBA

Microsoft’s SQL Server database engine has gone through various versions over the many years it has been one of the most popular solutions for database design. Some of the versions also support databases created under the older versions of the engine. This table helps you understand what support is available from those various SQL Server versions.

Product Engine Version Compatibility Level Supported Compatibility Levels
SQL Server 2016 13 130 130, 120, 110, 100
SQL Database 12 120 130, 120, 110, 100
SQL Server 2014 12 120 120, 110, 100
SQL Server 2012 11 110 110, 100, 90
SQL Server 2008 R2 10.5 105 100, 90, 80
SQL Server 2008 10 100 100, 90, 80
SQL Server 2005 9 90 90, 80
SQL Server 2000 8 80 80

You can get your current version information with this simple query:

SELECT databases.name, databases.compatibility_level from sys.databases 
GO

You can also get end-of-life information here.

Top 9 Features of SQL Server 2016

SQL Server - SeniorDBA

Technology is always improving. Microsoft SQL Server 2016 includes many new and improved features that will provide users with greater availability, better performance, and more security. The Microsoft IT Enterprise Services BI team has identified their top eight features and enhancements:

  • One programming surface across all editions – With November’s SQL Server 2016 Service Pack 1 (SP1), you can switch from Express to Standard, or Standard to Enterprise, and you don’t have to rework code to take advantage of additional features.
  • In-Memory OLTP helps ESBI meet their users’ business requirements for increased agility.
  • Columnstore Indexes reduce the amount of time it takes to run and render SRSS reporting data.
  • Temporal data reduces the amount of support tickets received from the field due to inaccurate data.
  • Row-Level Security provides a more reliable and standardized method to easily control which users can access data.
  • Dynamic Data Masking helps limit exposure of sensitive data, preventing users who should not have access to the data from viewing it.
  • Query Store provides better insight into the performance differences caused by changes in query plans.
  • Active Query Statistics allows a view of active query execution plans and helps identify and fix blocking issues while queries are running.
  • SQL Stretch Database helps improve performance to frequently used data while preserving access to archived data.

You can read additional details of these features here.

TIOBE Index for April 2017

Have you seen the latest TIOBE rankings report?

The TIOBE Programming Community index is an indicator of the popularity of programming languages. The index is updated once a month. The ratings are based on the number of skilled engineers world-wide, courses and third party vendors. Popular search engines such as Google, Bing, Yahoo!, Wikipedia, Amazon, YouTube and Baidu are used to calculate the ratings. Observe that the TIOBE index is not about the best programming language or the language in which most lines of code have been written.

This month Visual Basic.Net has moved up sightly, but the big news is the PHP replacement language called Hack.

The TIOBE Top 10 for this month:

Apr 2017 Apr 2016 Change Programming Language Ratings Change
1 1 Java 15.568% -5.28%
2 2 C 6.966% -6.94%
3 3 C++ 4.554% -1.36%
4 4 C# 3.579% -0.22%
5 5 Python 3.457% +0.13%
6 6 PHP 3.376% +0.38%
7 10 change Visual Basic .NET 3.251% +0.98%
8 7 change JavaScript 2.851% +0.28%
9 11 change Delphi/Object Pascal 2.816% +0.60%
10 8 change Perl 2.413% -0.11%

Read the entire results at this site.