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.

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.

Importance of Performing DBCC CHECKDB

DBCC CHECKDB - SeniorDBA

You absolutely need to be performing a DBCC CHECKDB on all of your databases on a regular schedule. This includes the system databases. One example is Model database corruption leading to a TempDB issue, because the TempDB is created using the Model Database.

In this article by Simon Liew we get a better understanding of the potential issues by example.

The scenario starts with a perfectly healthy SQL Server 2016 Developer Edition RTM.

Page id 164 belongs to system table [sys].[sysidxstats] (equivalent to system view sysindexes) in the model database. This page is corrupted and then a full backup is taken. You can download the corrupted full model database backup at the end of this tip if you wish to simulate the steps in this tip.

Unless the BACKUP command is specified with the option CHECKSUM, generation of backup checksums and the validation of page checksums is disabled. Hence, the BACKUP command seems to execute successfully without error even though this model database contains a corrupted page.

BACKUP DATABASE [model] TO DISK = 'F:\SQLDATA\model_corrupt_sql2016.bak' WITH COMPRESSION

Backup the SQL Server Model Database
Step 1 – Setting up a corrupt model database
To simulate the scenario in this tip, you can replace the model database in your SQL Server 2016 development/test instance using the corrupt model database backup provided at the bottom of this tip. Restoring the model database works the same as restoring a user database.