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.

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.

SQL Server Information Script

dbcc_sql

As a SQL Server DBA, you must know the detailed information about your databases, like file sizes, locations, etc. This information is easily extracted from your SQL Server instance with the correct script. In this article from Tim Ford, you will get the required information.

The other thing to consider is that all this information that’s returned through these various means don’t necessarily match in terms of units of measure: some of this information is returned as megabytes (mb) some as counts of data pages (which are 8 kilobytes (kb) each.) The disparity does not do us much good.

All this leads to the need to have a single option to go to in order to return information that provides data points that are valuable to the Database Administrator:

  • Server Name
  • Database Name
  • File Name (both logical and physical)
  • File Type
  • File Size, Consumption, Free Space
  • Growth Units (percent or a fixed mb size)
  • Maximum File Size

Since there is no single option for aggregating this information together I had to build a stored procedure to collect it for me and it’s time to share it with all of you.

Parameters

The underlying code taps into some of those sources I mentioned above: sys.database_files and the FILEPROPERTY() function. The remainder of it is pure math and converting 8kb pages (in some cases) into a megabyte unit. The stored procedure accepts two parameters:

@granularity: d | NULL – ‘d’ signifies a database level granularity and aggregates all size metrics for each data file (and separately the transaction log) into a single row.

@database_name: <database name> | NULL – if a database name is specified then only the results for that database are returned. Otherwise results for all databases on the instance are returned.

Returned Results

The following columns are returned when specifying a database-level of granularity:

  • server
  • database_name
  • db_size_mb
  • db_free_mb
  • db_used_mb
  • data_size_mb
  • data_free_mb
  • data_used_mb
  • data_used_pct
  • log_size_mb
  • log_free_mb
  • log_used_mb
  • log_used_pct

The following columns are returned when specifying a file-level of granularity:

  • server
  • database_name
  • file_name
  • physical_name
  • file_type
  • db_size_mb
  • db_free_mb
  • db_used_mb
  • free_space_pct
  • growth_units
  • max_file_size_mb

Read the entire article that includes the sample scripts.

22 DBA Responsibilities You Should Know About

dba

Being a Database Administrator (DBA) is a tough job, and knowing what responsibilities are involved before you commit to that career can be very important.

In this article by Craig Mullins, we get his list of 22 DBA responsibilities:

  1. General database management.
  2. Data modeling and database design.
  3. Metadata management and repository usage.
  4. Database schema creation and management.
  5. Capacity planning.
  6. Programming and development.
  7. SQL code reviews and walk-throughs.
  8. Performance management and tuning.
  9. Ensuring availability.
  10. Data movement.
  11. Backup and recovery.
  12. Ensuring data integrity.
  13. Procedural skills.
  14. Extensible data type administration.
  15. Data security.
  16. Database auditing.
  17. General systems management and networking skills.
  18. Business knowledge.
  19. Data archiving.
  20. Enterprise resource planning (ERP).
  21. Web-specific technology expertise.
  22. Storage management techniques.

You can read the entire article, part 1 and part 2, to get all the details.

Scripts for listing all SQL Server Databases and Objects using PowerShell

PowerShell and SQL Server - SeniorDBA

This powerful script lists all objects in an instance and scripts them into a network folder, by date and instance, so you can keep a record of the objects.

This article by Angel Gomez gives you the script and some information on how to use it.

Using PowerShell and SQL Server Agent we can create a scheduled job that runs each day and produces scripts for all objects in all databases for an instance of SQL Server and that is what this tip does.

Here is the PowerShell code to generate a script for each object in the database.  The below code will script out table definitions, stored procedures, views, user defined functions and triggers.  This will generate scripts for every database in the SQL Server instance.

You need to supply the SQL Server name and the path where the objects are to be created.

$date_ = (date -f yyyyMMdd)
$ServerName = "." #If you have a named instance, you should put the name. 
$path = "c:\SQL_Server\Backup\Objects\"+"$date_"
 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup. 
$ExcludeSchemas = @("sys","Information_Schema")
$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')

 
$dbs=$serverInstance.Databases #you can change this variable for a query for filter yours databases.
foreach ($db in $dbs)
{
       $dbname = "$db".replace("[","").replace("]","")
       $dbpath = "$path"+ "\"+"$dbname" + "\"
    if ( !(Test-Path $dbpath))
           {$null=new-item -type directory -name "$dbname"-path "$path"}
 
       foreach ($Type in $IncludeTypes)
       {
              $objpath = "$dbpath" + "$Type" + "\"
         if ( !(Test-Path $objpath))
           {$null=new-item -type directory -name "$Type"-path "$dbpath"}
              foreach ($objs in $db.$Type)
              {
                     If ($ExcludeSchemas -notcontains $objs.Schema ) 
                      {
                           $ObjName = "$objs".replace("[","").replace("]","")                  
                           $OutFile = "$objpath" + "$ObjName" + ".sql"
                           $objs.Script($so)+"GO" | out-File $OutFile
                      }
              }
       }     
}

You can read the entire article here.