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.

11 Hidden Windows 10 Tips and Tweaks

Windows 10 - SeniorDBA

All the features of Windows 10 aren’t as obvious as they should be, and that includes the tips and tweaks that make the powerful operating system easier to use. In this article by Howard Wen, we learn the details of 11 lesser-known ways to make Windows 10 better.

1. Delete your previous Windows version installation

2. Know how to sign out of Windows 10 

3. Pick whatever accent color you want

4. Use the new delay timer in the Snipping Tool 

5. Change Edge’s default search engine from Bing to another one 

6. Delay automatic updates over Wi-Fi 

7. Record video clips using the Xbox app

8. Remove the OneDrive folder from File Explorer 

9. Pin Windows apps to the desktop

10. Access all Windows 10 settings under one user interface

11. Uninstall default Windows apps

 

You can read the entire article to see all the details.

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.

Highest paid jobs in programming for 2017

Communication

Stack Overflow has asked developers each year since 2011 about their favorite technologies, coding habits, and work preferences. This includes how they learn, share, and earn more money. This year over 64,000 developers took their annual survey.

They learn something new every time they perform the survey, and this year is no exception:

  • A common misconception about developers is that they’ve all been programming since childhood. In fact, we see a wide range of experience levels. Among professional developers, 11.3% got their first coding jobs within a year of first learning how to program. A further 36.9% learned to program between one and four years before beginning their careers as developers.
  • Only 13.1% of developers are actively looking for a job. But 75.2% of developers are interested in hearing about new job opportunities.
  • When we asked respondents what they valued most when considering a new job, 53.3% said remote options were a top priority. A majority of developers, 63.9%, reported working remotely at least one day a month, and 11.1% say they’re full-time remote or almost all the time.
  • A majority of developers said they were underpaid. Developers who work in government and non-profits feel the most underpaid, while those who work in finance feel the most overpaid.

So what can you learn from the answers collected during this survey? How about what languages pay the most, or are in the highest demand?

Most Popular:

graph2-stack-overflow.png
Image: Stack Overflow

 

Highest Salary:

graph3-stack-overflow.png
Image: Stack Overflow

 

You can get much more detail directly from the Stack Overflow survey results.

PCI: Listed vs. Non-Listed P2PE Solutions

credit-cards

With credit card processing, it is important to understand the different parts and pieces that make-up the required security components. The PCI Security Standards Council (PCI-SSC) has released an assessment methodology for merchants using Point-to-Point Encryption (P2PE) solutions. The addition of the Non-Listed Encryption Solution Assessment (NESA) and the accompanying audit process provides merchants an expanded pool of encryption solutions beyond the current list of validated providers, allowing for a wider range of security offerings of the merchant is willing to accept the added liability and cost of a self-certified solution. It is very important to understand the assessment requirements of each before deciding between a listed or a non-listed solution.

The process for becoming a listed solution with the PCI-SSC begins with an audit performed by a third party independent Qualified Security Assessor (QSA) who has been certified specifically for P2PE assessments. During this technical assessment, the P2PE QSA will evaluate the solution against the relevant controls outlined in the following six P2PE Domains from the PCI-SSC:

  • Domain 1: Encryption Device and Application Management
  • Domain 2: Application Security
  • Domain 3: P2PE Solution Management
  • Domain 4: Merchant Managed Solutions (not applicable to 3rd party solution providers)
  • Domain 5: Decryption Environment
  • Domain 6: P2PE Cryptographic Key Operations and Device Management

For each applicable control, the P2PE QSA will collect evidence from the solution and observe all required procedures to ensure compliance with the standard. The results of the assessment are then documented using the P2PE Report on Validation (P-ROV) template which will be submitted by the QSA directly to the PCI-SSC for final review. Once a representative of the PCI-SSC has reviewed, approved, and signed the submitted P-ROV, the solution will receive an official listing on the PCI website.

Transparency_Report

The process of implementing and validating a new or existing solution can be quite lengthy and the NESA process gives solution providers the ability to provide a degree of security assurance to customers, along with scope reduction, while they work towards a validated listing. Much like the process for becoming a listed solution, non-listed solution providers need to engage their own P2PE QSA to perform an assessment of their solution. The requirements for this type of assessment, however, have been relaxed in that a non-listed solution assessment can be completed without meeting the requirements for P2PE Domains 1, 2, or 3, but must meet all applicable requirements of Domains 5 and 6. Though the QSA will still complete a P-ROV for informational purposes, the end result of this assessment will also include a set of documents (referred to as the NESA documentation) which will include:

  • A description of the solution
  • A summary of the application’s full compliance, partial compliance, or non-compliance with Domains 1,2, and 3
  • A statement of compliance confirming the applicable requirements of Domains 5 and 6 are met
  • The assessing P2PE QSA’s recommendation as to how the solution impacts the merchants PCI scope

This set of documents serves the same purpose as a listed solution’s P-ROV or Attestation of Validation (AOV), without being submitted to the PCI Council or the Payment Brands, and will be used by PCI QSA’s when assessing the PCI compliance of a merchant utilizing the non-listed solution. As with standard PCI certification documentation, this NESA documentation should be distributed to clients on an annual basis, and whenever there are significant changes to the system.

At the merchant level, the difference between implementing a listed versus a non-listed solution becomes apparent during the annual PCI-DSS re-certification. A merchant using a listed solution in accordance with the solution providers P2PE Instruction Manual (PIM) and the pre-requisites of the SAQ P2PE automatically qualifies for a drastic reduction in PCI scope when assessing their environment. This is because the security and isolation of credit card data has been verified by a representative of the PCI-SSC. This same level of scope reduction is not guaranteed with a non-listed solution and will really depend on what is permitted by the merchant’s acquirer as well as the payment brands. In some cases, the acquirer or payment brands may require the aid of a PCI QSA to review the solution provider’s NESA documentation and the merchant’s implementation of the solution to determine what PCI-DSS requirements are covered, and to what degree. The results of this secondary solution assessment will determine which areas of the merchant environment are in scope of PCI, but will not qualify the merchant to utilize the SAQ P2PE.

You can get more information from the official PCI Security Standards Council website or your QSA.

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.