Monday, July 4, 2016

Why You Need to Secure Your SQL Server Instances

Database Management Systems (DBMSs) store data. They sure have added functionality and a huge set of significant features but again, in the end of the day, they store data. Your data. This makes your DBMSs one of the most valuable assets in your Organization and that's why you need to keep them as secure as possible during their entire life cycle  within your Organization.

SQL Server is a very powerful data platform and part of this power, is to allow the user to control different settings, thus making it work the best for his/her needs. However, as in all systems, if these settings are misconfigured, or the proper precautions are not taken on the user’s side, then along with the functionality the user wants to enable, he or she possibly will create security risks.

Take for example, the ‘Password Expiration’ option. In case you have a SQL login which is used as a service account, then this could be a reason for not to have the ‘Password Expiration’ enabled for that login. If however, you have an SQL login which is used by a physical person and the ‘Password Expiration’ option is not enabled, this increases the risk of having the password guessed more easily than in the case where the password expires every X days (i.e. every 90 days) and thus the user needs to enter a new one.

Figure 1: Start Page.
Other examples of security risks are: using the same password as the username for SQL Logins, having 'xp_cmdshell' enabled without really needing it and without the proper design and accesses, having the BUILTIN\Administrators local windows group on the database server as SysAdmins, etc.

There are many settings which can be incorrectly set by the user, either because of naivety, or lack of deep understanding about these settings, or any other reason.

By the time a DBMS hosts a single database, it is critical that you keep that DBMS instance as secure as possible. Misconfiguring your instance can be a source of vulnerabilities so you need to periodically check you instance about related security risks and take remediation actions when and where needed.

It is based on all the above that I have been developing during the last two years "DBA Security Advisor". DBA Security Advisor is a powerful tool i released a few days ago, which assesses SQL Server instances for potential security risks based on a proven best practices set of security checks. Furthermore it provides recommendations for the detected security risks as well as remediation scripts and methods.

DBA Security Advisor comes in two editions: (i) A Community Edition which is free but with a limited set of security checks and limited functionality, and (ii) An Enterprise Edition where all security checks and other features are available. You can compare the available features per edition on this link.

The workflow of DBA Security Advisor (Enterprise Edition) is very straightforward:

1. You connect to a single or multiple SQL Server instances.
2. You select the security checks to run against the connected SQL Server instance(s) and run the assessment.
3. You go through the generated report with the security findings.
4. You study the recommendations and remediation scripts/methods and act accordingly towards resolving the security risks.
5. You re-run the security assessment and check if the previously-reported security risks have been eliminated after you took actions.

Figure 2: Connect to Multiple Instances (Enterprise Edition).




























Figure 3: Select Security Checks (Enterprise Edition).




























Figure 4: Embedded Report Viewer: Sample Security Risks.




























Figure 5 Standalone Security Report Viewer with Recommendations and Remediation Scripts and Methods.


























I believe that you will find DBA Security Advisor extremely useful. It will help you secure your SQL Server instances, as well as become compliant with a large number of security best practice factors. Test the Community Edition today which is free, and after you are convinced that DBA Security Advisor can help you in your everyday SQL Server administration and hardening process, you can consider upgrading to the Enterprise Edition, and thus unlock all security checks and the rest of its powerful features.



Resources:



Cheers,

Artemakis Artemiou
Microsoft Data Platform MVP
https://www.aartemiou.com
https://www.dbasecadvisor.com
https://www.inmemoltpsim.com
Read more on this article...

Tuesday, June 28, 2016

DBA Security Advisor is Now Out!

Today is big day. Today, DBA Security Advisor is finally released!

It has been a long journey. Two years of research, development, testing, QA, and the list goes on!

I work with SQL Server for more than 11 years and I have seen hundreds of SQL Server installations and setups. I have seen best practices and bad practices. I have seen deep knowledge in administering SQL Server, but I have also seen naivety in administering such a powerful RDBMS and believe me, naivety in database administration is not something good.

SQL Server is a very powerful data platform and part of this power, is to allow the user to control different settings, thus making it work the best for his/her needs. However, similarly as in all systems, if these settings are misconfigured, or the proper precautions are not taken on the user’s side, then this could create possible security risks.

Take for example, the ‘Password Expiration’ option. In case you have a SQL login which is used as a service account, then this could be a reason for not to have the ‘Password Expiration’ enabled for that login. If however, you have an SQL login which is used by a physical person and the ‘Password Expiration’ option is not enabled, this increases the risk of having the password guessed more easily than in the case where the password expires every X days (i.e. every 90 days).

Another example, is using the same password as the username for SQL Logins. This is a critical security risk and you should never do this.

DBA Security Advisor, via a rich set of security checks, assesses your SQL Server instances against potential security risks and recommends solutions along with remediation scripts and methods.

Download the Community Edition today! Feel free to try DBA Security Advisor! After you are convinced that it can really help you, then you can consider upgrading to the Enterprise Edition where you can have access to all the security checks as well as to all the great features of DBA Security Advisor.





Resources:



Cheers,
Artemakis
Read more on this article...

Saturday, June 25, 2016

Announcing DBA Security Advisor - A Security Tool for SQL Server

After almost two years of heavy development and extensive testing, in three days, on June 28 2016, I will be releasing my brand new software tool for SQL Server.

The name of the tool is 'DBA Security Advisor' and as the name implies, it is a database security tool. DBA Security Advisor assesses SQL Server instances against a rich set of security checks and provides recommendations along with remediation scripts and methods, all based on proven security best practices for SQL Server.

Figure 1: Connecting to Multiple SQL Server Instances.




























DBA Security Advisor comes in two editions: (i) The Community Edition which is free but with
limited security checks and functionality, and (ii) The Enterprise Edition with requires a license and where all security checks and features are available. You can check the features of each edition here.

DBA Security Advisor provides a variety of security checks in order to enable the DBA to perform a rich assessment of the SQL Server instance or instances and get meaningful insights about their configuration from the security perspective.

Figure 2: Available Security Checks in Enterprise Edition
(Community Edition, among other, supports fewer security checks).





























After the assessment is completed, DBA Security Advisor generates a comprehensive report with the results of the assessment and other important information. Also, in the Enterprise Edition of the tool, you can get recommendations and remediation scripts and methods.

Figure 3: Embedded Report Viewer.





























Figure 4: Standalone Security Report Viewer (Enterprise Edition).

























Note: DBA Security Advisor supports SQL Server 2005 or later. 


For more information, you can download the DBA Security Advisor's datasheet below:










Also you can visit DBA Security Advisor's official website.

Feel free to register to DBA Security Advisor's newsletter and stay up to date with the latest news about this remarkable software product.

Read more on this article...

Saturday, May 28, 2016

Operating System Requirements for all SQL Server Versions and Editions


SQL Server is the world's most powerful data platform and it is constantly evolving. From release to release significant new features are added. Besides the features and functionality, one of the typical question DBAs have is which are the Operating System requirements for each version and edition of SQL Server. In this post you can find this information for all versions and editions gathered from different MSDN Library articles. In the end of each table there is a reference to the source document on MSDN in case you need to find more information (i.e. 32-bit vs. 64 bit, etc.).


SQL Server 2016 - Operating System Requirements
SQL Server 2016 Enterprise Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
SQL Server 2016 Standard Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows 8.1
Windows 8.1 Pro
Windows 8.1 Enterprise
Windows 8
Windows 8 Pro
Windows 8 Enterprise
SQL Server 2016 Web Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
SQL Server 2016 Developer Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows 8.1
Windows 8.1 Pro
Windows 8.1 Enterprise
Windows 8
Windows 8 Pro
Windows 8 Enterprise

SQL Server 2016 Express

Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
 Windows 8.1
 Windows 8.1 Pro
 Windows 8.1 Enterprise
 Windows 8
 Windows 8 Pro
 Windows 8 Enterprise

 

For more info please visit: https://msdn.microsoft.com/en-us/library/ms143506.aspx

 




SQL Server 2014 - Operating System Requirements
SQL Server 2014 Enteprise Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essential
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Web
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Web

SQL Server 2014 Business Intelligence

Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Web
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Web

SQL Server 2014 Standard

Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Foundation
Windows Server 2008 R2 SP1 Web
Windows 8.1
Windows 8.1 Pro
Windows 8.1 Enterprise
Windows 8
Windows 8 Pro
Windows 8 Enterprise
Windows 7 SP1 Ultimate
Windows 7 SP1 Enterprise
Windows 7 SP1 Professional
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Foundation
Windows Server 2008 SP2 Web

SQL Server 2014 Web

Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Web
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Web

SQL Server 2014 Developer

Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Web
Windows 8.1
Windows 8.1 Pro
Windows 8.1 Enterprise
Windows 8
Windows 8 Pro
Windows 8 Enterprise
Windows 7 SP1 Ultimate
Windows 7 SP1 Enterprise
Windows 7 SP1 Professional
Windows 7 SP1 Home Premium
Windows 7 SP1 Home Basic
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Web

SQL Server 2014 Express

Windows 10 Home
Windows 10 Professional
Windows 10 Enterprise
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2008 R2 SP1 Datacenter
Windows Server 2008 R2 SP1 Enterprise
Windows Server 2008 R2 SP1 Standard
Windows Server 2008 R2 SP1 Foundation
Windows Server 2008 R2 SP1 Web
Windows 8.1
Windows 8.1 Pro
Windows 8.1 Enterprise
Windows 8
Windows 8 Pro
Windows 8 Enterprise
Windows 7 SP1 Ultimate
Windows 7 SP1 Enterprise
Windows 7 SP1 Professional
Windows 7 SP1 Home Premium
Windows 7 SP1 Home Basic
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Foundation
Windows Server 2008 SP2 Web




SQL Server 2008 R2 - Operating System Requirements

SQL Server 2008 R2 Datacenter

Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows Server 2008 for Windows Essential Server Solutions
Windows 2008 R2 Datacenter
Windows 2008 R2 Enterprise
Windows 2008 R2 Standard
Windows 2008 R2 Web
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation

SQL Server 2008 R2 Enterprise

Windows Server 2003 SP2 Datacenter
Windows Server 2003  SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows 2008 R2 Datacenter
Windows 2008 R2 Enterprise
Windows 2008 R2 Standard
Windows 2008 R2 Web
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation

SQL Server 2008 R2 Standard

 

Windows XP Professional SP2
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Vista SP2 Ultimate
Windows Vista SP2 Enterprise
Windows Vista SP2 Business
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows Server 2008 SP2 for Windows Essential Server Solutions
Windows Server 2008 SP2 Foundation Server
Windows 7 Ultimate
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 Datacenter
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Web
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2008 R2 Foundation Server
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8
Windows 8 Professional
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 R2 Developer

 

Windows XP SP2 Professional
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Vista Ultimate
Windows Vista Home Premium
Windows Vista Home Basic
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 SP2 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows 7 Ultimate
Windows 7 Home Premium
Windows 7 Home Basic
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 DataCenter
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Web
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8
Windows 8 Professional
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 R2 Workgroup

 

Windows XP SP2 Professional
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Vista SP2 Ultimate
Windows Vista SP2 Enterprise
Windows Vista SP2 Business
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2008 SP2 Foundation Server
Windows 7 Ultimate
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 Datacenter
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Web
Windows Server 2008 R2 Foundation Server
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8
Windows 8 Professional
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 R2 Web

 

Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Web
Windows Server 2008 R2 Datacenter
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation

SQL Server 2008 R2 Express 

 

Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2003 SP2 Standard
Windows Server 2003 R2 SP2 Datacenter
Windows Server 2003 R2 SP2 Enterprise
Windows Server 2003 R2 SP2 Standard
Windows Vista SP2 Ultimate
Windows Vista SP2 Home Premium
Windows Vista SP2 Home Basic
Windows Vista SP2 Enterprise
Windows Vista SP2 Business
Windows Server 2008 SP2 Datacenter
Windows Server 2008 SP2 Datacenter without Hyper-V
Windows Server 2008 SP2 Enterprise
Windows Server 2008 SP2 Enterprise without Hyper-V
Windows Server 2008 SP2 Standard
Windows Server 2008 SP2 Standard without Hyper-V
Windows Server 2008 SP2 Web
Windows Server 2008 SP2 Foundation Server
Windows 7 Ultimate
Windows 7 Home Premium
Windows 7 Home Basic
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 Datacenter
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Web
Windows Server 2008 R2 for Windows Essential Server Solutions
Windows Server 2008 R2 Foundation Server
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8
Windows 8 Professional
Windows 8.1
Windows 8.1 Professional




SQL Server 2008 - Operating System Requirements
SQL Server 2008 Enterprise

Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Server 2008 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows Server 2008 R2 Web
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation

SQL Server 2008 Standard 

Windows XP Professional
Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Vista Ultimate
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 Web
Windows Server 2008 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows Small Business Server 2008
Windows Server 2008 for Windows Essential Server Solutions
Windows Server 2008 without Hyper-V for Windows Essential Server Solutions
Windows 7 Ultimate
Windows 7 Enterprise
Windows 7 Professional
Windows 2008 R2 Web
Windows 2008 R2 Foundation Server
Windows 2008 R2 Standard
Windows 2008 R2 Enterprise
Windows 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 8
Windows 8 Professional
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 Developer 

Windows XP Professional
Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Vista Ultimate
Windows Vista Home Premium
Windows Vista Home Basic
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows Server 2008 Web
Windows 7 Web
Windows 7 Home Basic
Windows 7 Home Premium
Windows 7 Enterprise
Windows 7 Ultimate
Windows 7 Professional
Windows 2008 R2 Web
Windows 2008 R2 Standard
Windows 2008 R2 Enterprise
Windows 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 8
Windows 8 Professional
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 Workgroup

Windows XP Professional
Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Vista Ultimate
Windows Vista Home Premium
Windows Vista Home Basic
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 Web
Windows Server 2008 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows 7 Ultimate
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 Web
Windows Server 2008 R2 Foundation Server
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 8
Windows 8 Professional
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8.1
Windows 8.1 Professional

SQL Server 2008 Web

Windows XP Professional
Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacenter
Windows Server 2003 SP2 Enterprise
Windows Vista Ultimate
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 Web
Windows Server 2008 Standard Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows Server 2008 R2 Web
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation

SQL Server 2008 Express

Windows Server 2003
Windows Server 2003 SP2 Standard
Windows Server 2003 SP2 Datacente
Windows Server 2003 SP2 Enterprise
Windows Vista Ultimate
Windows Vista Home Premium
Windows Vista Home Basic
Windows Vista Enterprise
Windows Vista Business
Windows Server 2008 Web1
Windows Server 2008 Standard
Windows Server 2008 Standard without Hyper-V
Windows Server 2008 Datacenter
Windows Server 2008 Datacenter without Hyper-V
Windows Server 2008 Enterprise
Windows Server 2008 Enterprise without Hyper-V
Windows 7 Ultimate
Windows 7 Home Basic
Windows 7 Home Premium
Windows 7 Enterprise
Windows 7 Professional
Windows Server 2008 R2 Web
Windows Server 2008 R2 Foundation Server1
Windows Server 2008 R2 Standard
Windows Server 2008 R2 Enterprise
Windows Server 2008 R2 Datacenter
Windows Server 2012 Datacenter
Windows Server 2012 Standard
Windows Server 2012 Essentials
Windows Server 2012 Foundation
Windows 8
Windows 8 Professional
Windows Server 2012 R2 Datacenter
Windows Server 2012 R2 Standard
Windows Server 2012 R2 Essentials
Windows Server 2012 R2 Foundation
Windows 8.1
Windows 8.1 Professional

Note: SQL Server 2008 support on Windows Server 2003 R2 is the same as support on Windows Server 2003 SP2.



SQL Server 2005 - Operating System Requirements
SQL Server 2005 Enterprise Edition

Windows 2000 Server SP4
Windows 2000 Advanced Server SP4
Windows 2000 Datacenter Edition SP4
Windows Server 2003 Server SP1
Windows Server 2003 Standard Edition SP1
Windows Server 2003 Enterprise Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Small Business Server 2003 Standard Edition SP1
Windows Small Business Server 2003 Premium Edition SP1
Windows Server 2008
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web Edition
Windows Server 2008 Standard
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web

SQL Server 2005 Developer Edition

Windows 2000 Professional Edition SP4
Windows 2000 Server SP4
Windows 2000 Advanced Server SP4
Windows 2000 Datacenter Edition SP4
Windows XP Home Edition SP2
Windows XP Professional Edition SP2
Windows XP Media Edition SP2
Windows XP Tablet Edition SP2
Windows Server 2003 Server SP1
Windows Server 2003 Enterprise Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Small Business Server 2003 Standard Edition SP1
Windows Small Business Server 2003 Premium Edition SP1
Windows Vista Home Basic Edition
Windows Vista Home Premium Edition
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2003 Standard Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Server 2003 Enterprise Edition SP1
Windows XP Professional 2003
Windows Vista Home Basic Edition
Windows Vista Home Premium Edition
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2008
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web Edition
Windows Server 2008 Standard
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web

SQL Server 2005 Standard Edition

Windows 2000 Professional Edition SP4
Windows 2000 Server SP4
Windows 2000 Advanced Server SP4
Windows 2000 Datacenter Edition SP4
Windows XP Professional Edition SP2
Windows XP Media Edition SP2
Windows XP Tablet Edition SP2
Windows Server 2003 Server SP1
Windows Server 2003 Enterprise Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Small Business Server 2003 Standard Edition SP1
Windows Small Business Server 2003 Premium Edition SP1
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2003 Standard Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Server 2003 Enterprise Edition SP1
Windows XP Professional 2003
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2008
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web Edition
Windows Server 2008 Standard
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web

SQL Server 2005 Workgroup Edition

Windows 2000 Professional Edition SP4
Windows 2000 Server SP4
Windows 2000 Advanced Server SP4
Windows 2000 Datacenter Edition SP4
Windows XP Professional Edition SP2
Windows XP Media Edition SP2
Windows XP Tablet Edition SP2
Windows Server 2003 Server SP1
Windows Server 2003 Enterprise Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Small Business Server 2003 Standard Edition SP1
Windows Small Business Server 2003 Premium Edition SP1
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2003 Standard Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Server 2003 Enterprise Edition SP1
Windows XP Professional 2003
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2008
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web Edition
Windows Server 2008 Standard
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web

SQL Server 2005 Express Edition

Windows 2000 Professional Edition SP4
Windows 2000 Server SP4
Windows 2000 Advanced Server SP4
Windows 2000 Datacenter Edition SP4
Windows XP Embedded SP2 Feature pack 2007
Windows Embedded for Point of Service
Windows XP Home Edition SP2
Windows XP Professional Edition SP2
Windows XP Media Edition SP2
Windows XP Tablet Edition SP2
Windows Server 2003 Server SP1
Windows Server 2003 Enterprise Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Server 2003 Web Edition SP1
Windows Small Business Server 2003 Standard Edition SP1
Windows Small Business Server 2003 Premium Edition SP1
Windows Vista Home Basic Edition
Windows Vista Home Premium Edition
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2003 Standard Edition SP1
Windows Server 2003 Datacenter Edition SP1
Windows Server 2003 Enterprise Edition SP1
Windows XP Professional 2003
Windows Vista Home Basic Edition
Windows Vista Home Premium Edition
Windows Vista Ultimate Edition
Windows Vista Business Edition
Windows Vista Enterprise Edition
Windows Server 2008
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web Edition
Windows Server 2008 Standard
Windows Server 2008 Enterprise
Windows Server 2008 Data Center
Windows Server 2008 Web

Note: SQL Server 2005 support on Windows Server 2003 R2 is the same as support on Windows Server 2003 SP1.

Read more on this article...

Monday, May 23, 2016

SQL Server 2008 R2 Service Pack Installation Fails - Element not found. (Exception from HRESULT: 0x80070490)

In case you are trying to install a SQL Server 2008 R2 service pack in a clustered SQL Server environment and the installation fails reporting something similar to the below:

Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.
Exit code (Decimal): -568706566
Exit facility code: 1562
Exit error code: 14842
Exit message: Element not found. (Exception from HRESULT: 0x80070490)
Start time: .....
End time: .....
Requested action: Patch

Then, the first thing you can do is to check if there are any disks in the cluster that are offline. If this is the case, you need to bring the disks back online prior to trying again.

As a side note, for the recommended practice of patching clustered SQL Server instances you can read this article.
Read more on this article...

Thursday, April 21, 2016

Entity Framework: Code First

A few days ago we had our second user group meeting for 2016 at Cyprus .NET User Group and it was all about data access.

I was the speaker at the event and I presented Entity Framework and more specifically the "Code First" development approach.

Entity Framework is an Object/Relational Mapping (ORM) Framework that treats data as Domain-Specific Objects. Entity Framework supports a variety of DBMSs.

The purpose of Entity framework is to simplify the data-access required when developing database applications.

In order to work with Entity Framework you need:
In order to install Entity Framework, right after you start a new project in Visual Studio, you can add it either via the GUI of NuGet Manager or via Package Manager Console with the command: Install-Package EntityFramework.

Figure 1: NuGet Package Manager.


Figure 2: Package Manager Console - Syntax for Installing Entity Framework.






Code First was first introduced in Entity Framework 4.1 and it follows the Domain Driven Design (DDD) paradigm. It is one of the three available development approaches in Entity Framework. The main idea is to create Domain Classes based on which the database can be created in the background.

In Entity Framework you can query data with any of the below methods:
  • LINQ to Entities
    • Method syntax
    • Query syntax
  • Entity SQL 
  • Native SQL


Figure 3: Component Stack when Using Entity Framework. Source: MSDN.
























For more information you can visit the below resources:

Read more on this article...

Wednesday, April 13, 2016

SQL Server 2016: Dynamic Data Masking

Dynamic Data Masking is another data protection feature in SQL Server 2016. It limits sensitive data exposure by masking it to non-privileged users. The masking rules are applied in the query results and you designate how much of the sensitive data to reveal and to who. The Dynamic Data Masking feature provides four types of masking methods:
  • Default: Full masking according to the data types of the designated fields.
  • Email: Masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. . aXXX@XXXX.com.
  • Custom String: Masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. . aXXX@XXXX.com.
  • Random: A random masking function for use on any numeric type to mask the original value with a random value within a specified range.
A simple example of Dynamic Data Masking is to display only the last 4 digits of a phone number to the support personnel of an IT Department or don't show the email address to unauthorized users, etc.

Now let's proceed with a demo in order to see some examples of using Dynamic Data Masking.

/*
Demo: Dynamic Data Masking
*/

--Create a sample database
IF DB_ID('SampleDB_DataMasking') IS NULL
BEGIN
CREATE DATABASE SampleDB_DataMasking;
END
GO

--Use sample database
USE SampleDB_DataMasking;
GO

--Create sample table
CREATE TABLE tblEmployeesMasking
    (
      empName VARCHAR(250) ,
      empPhoneNo VARCHAR(20) ,
      empEmailAddress VARCHAR(100)
    );
GO

--Populate sample table with data
INSERT  INTO dbo.tblEmployeesMasking
VALUES  ( 'John Clarck', '545-5478-1234', 'john.clark@example.com' ),
        ( 'Kostas Andreou', '878-8888-5678', 'kostas.andreou@example.com' ),
        ( 'Wei Xing', '673-5123-9101', 'wei.xing@example.com' );

--Check records (unmasked)
SELECT * FROM tblEmployeesMasking



Output










--Now let's mask the email address
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empEmailAddress VARCHAR(100) MASKED WITH (FUNCTION = 'email()');

Let's check again the records of the table, this time using a non-sysadmin user.

--Create user (non-sysadmin) with GRANT SELECT permission on the sample table
CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON dbo.tblEmployeesMasking TO TestUser;
GO

--Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.tblEmployeesMasking;
GO


Output













As you can see in the above screenshot, the column's 'empEmailAdress' values are presented as masked.
OK. So now let's switch the execution context back our original login's context in order to proceed with masking the 'empPhoneNo' column:

REVERT;
GO

--Let's mask the phone number with default
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empPhoneNo VARCHAR(100) MASKED WITH (FUNCTION = 'default()');

Let's check the records of the table using a non-sysadmin user.

--Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output:











Now you can see that the column 'EmpPhoneNo' is also masked.
The next test is to remove the 'empPhoneNo' column's masking and set a custom masking function:

REVERT;
GO

--Remove masking 
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empPhoneNo VARCHAR(100) 

--Let's mask the phone number with custom function
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empPhoneNo VARCHAR(100) MASKED WITH (FUNCTION = 'partial(3,"-XXXX-XX",2)');


Let's check the records of the table using a non-sysadmin user.

--Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output:











As you can see from the above screenshot, now the empPhoneNo column is masked based on our custom masking function.
Last, if you want to allow 'TestUser' to be able to see the masked data, you can grant her or him the UNMASK permission:

REVERT;
GO

-- Granting the UNMASK permission
GRANT UNMASK TO TestUser;
GO

Let's check the records of the table within the context of 'TestUser':

--Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output:











Because TestUser was granted the UNMASK permission, it was possible to see the unmasked data.

Similarly, if REVOKE the UNMASK permission, TestUser will only see the masked data:


















Dynamic Data Masking is a significant feature in SQL Server 2016 and Azure SQL Database. As you can see from the above examples it is very easy to use it and it can really help you when it comes to Security and Compliance regarding yours or your organization's most valuable asset: the data.


References: 
MSDN Library Article: What's New in SQL Server 2016 Release Candidate (RC2)
MSDN Library Article: Dynamic Data Masking
Microsoft Azure Document: Get started with SQL Database Dynamic Data Masking (Azure Portal)

See also...
SQL Server 2016 Top Features


--
My Latest Projects:

Read more on this article...

Friday, April 1, 2016

I'm a Microsoft Data Platform MVP for the 8th Year!

Just got the magic email from Microsoft saying:











Dear Artemakis Artemiou,

Congratulations! We are pleased to present you with the 2016 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in Data Platform technical communities during the past year.

...

The Microsoft MVP Award provides us the unique opportunity to celebrate and honor your significant contributions and say "Thank you for your technical leadership."
-------------------------------------------
This is my 8th year as a Microsoft Data Platform MVP and I feel very honored receiving this prestigious award for another year. 
Interacting with the Data Platform technical communities all over the world is something magic. The endless technical discussions, the quest for knowledge and solutions to complex problems, the excitement for new technologies, events, books, blogs, social networks, ..., everything, it is something special.

I have been working with SQL Server for over than 15 year now and hey, I'm just loving it! Working with and constantly researching the most powerful Data Platform in the world for so many years can only offer you deep knowledge about a large set of technologies, that all together converge and form this amazing Data Platform known to all of us as SQL Server and Azure SQL Database.

A huge thank you to all of you, my fellow SQL Server community members for having these great technical discussions through all online and offline channels. It is these discussions that make us better in what we do.

I would also like to thank Microsoft for supporting the technical communities all over the globe so actively and continuously. Also, a special thank you to the local Microsoft Office at Cyprus for supporting our community activities at the Cyprus .NET User Group.

I would also like to thank the SQL Server and other involved teams at Microsoft for implementing this amazing Data Platform vision and for having all those great discussions with us.

Furthermore, I would like to thank the CEE MVP Community Team for all its valuable support throughout each MVP Award year.

On top of everything, a huge thank you to my beautiful wife and daughter for their true support, love and understanding for all those endless hours I spent away from them working on all those "community stuff" :) Without your support, you know it would just not feel right. I love you and thank you from the depths of my heart loves! Once again, this award is dedicated to you!

The journey continues. So many technologies, all part of this amazing Data Platform: In-Memory OLTP, SQL Server on Linux, Advanced Analytics with SQL Server R Services, and the list goes on.

Once again, my commitment to all of you is that I will continue sharing with you my knowledge on SQL Server and related technologies via my blog, online and offline events, my books, social networks, my technical papers on different journals, and any other available channel!


Read more on this article...

Wednesday, March 30, 2016

SQL Server 2016: The STRING_SPLIT Function

A long-awaited string function added to SQL Server 2016 is STRING_SPLIT. As the name implies, this function splits the given character expression using the separator set by user.

Let's see some examples of using the STRING_SPLIT function.


--
--Example #1
--
DECLARE @string AS VARCHAR(250);
SET @string = '1-2-3-4-5-6-7-8-9-10';

SELECT  Value
FROM STRING_SPLIT(@string, ',');

--Output:


























--
--Example #2
--
CREATE TABLE #test
    (
      id INT ,
      productsPurchased VARCHAR(250)
    );

INSERT  INTO #test
VALUES  ( 1, 'product1, product2, product3' );

SELECT  id ,
        LTRIM(t2.value)
FROM    #test t
        CROSS APPLY STRING_SPLIT(productsPurchased, ',') t2;

--Output:





























--
--Example #3
--
CREATE TABLE #CSVHeaders ( headers VARCHAR(MAX) );

INSERT  INTO #CSVHeaders
VALUES  ( 'header1,header2,header3,header4,header5,header6,header7,header8,header9,header10,header11,header12,header13,header14,header15' );

SELECT  LTRIM(value) AS HeaderName
FROM    #CSVHeaders
        CROSS APPLY STRING_SPLIT(headers, ','); 

--Output:































As you can see, STRING_SPLIT is a very handy new string function in SQL Server 2016. It allows you to easily manipulate text and organize it by parsing it using separators.

For more info, please visit MSDN Books Online.

Read more on this article...

Saturday, March 5, 2016

In-Memory OLTP Simulator: The Experiment

A few days ago I published a 38-page technical paper titled "In-Memory OLTP Simulator: The Experiment".

In-Memory OLTP Simulator is a software tool that I developed, which allows the user to easily simulate virtually any workload against the powerful In-Memory OLTP Engine of Microsoft SQL Server.

This paper presents a series of simulations for a specific scenario against different workload sizes. The paper discusses the findings of these simulations by describing how the entire process was designed using In-Memory OLTP Simulator, as well as by observing and analyzing the performance trends after executing the different versions of the scenario against SQL Server’s In-Memory OLTP Engine.

The highlight of the paper is the fact that for a workload of 5M records, I achieved breakthrough performance with a speedup of 26x over disk-based tables!

You can download the paper from In-Memory OLTP Simulator's official website.


In-Memory OLTP Simulator: Statistics for the 5M Record Processing.




Read more on this article...

Wednesday, February 24, 2016

SQL Server 2016: Row Level Security

Row-Level Security (RLS) is one of the top features in SQL Server 2016. With RLS you can control access to rows in a table based on the characteristics of the user executing a query.

The access restriction logic is located in the database tier and access restrictions are always applied, thus they cannot be skipped.

Below I will showcase RLS with the use of a simple scenario. This example features a fictitious school's database and more specifically a table containing the school's student records. Each student is assigned an advisor. By using Row-Level Security it is possible for each advisor to see only the records of his students. In the same scenario, only the school principal can view all student records.

--
-- Row Level Security Example
--

USE master;
GO

--Create sample database
CREATE DATABASE RLSDemoDB;
GO

--Use the sample database
USE RLSDemoDB;
GO

--Create four database users 
CREATE USER Advisor1 WITHOUT LOGIN;
CREATE USER Advisor2 WITHOUT LOGIN;
CREATE USER Advisor3 WITHOUT LOGIN;
CREATE USER Principal WITHOUT LOGIN;

--Create the students table
CREATE TABLE tblStudents
(
      code VARCHAR(10) NOT NULL PRIMARY KEY,
      name VARCHAR(100) NOT NULL, 
      advisorCode sysname,
      classID INT,
      GPA FLOAT
);

--Insert sample data
INSERT  tblStudents
VALUES  ( 'ST001', 'Student1','Advisor1', 1,3.2),
        ( 'ST002', 'Student2','Advisor1', 3,3.3),
        ( 'ST003', 'Student3','Advisor1', 5,2.8),
        ( 'ST004', 'Student4','Advisor2', 2,3.9),
        ( 'ST005', 'Student5','Advisor2', 4,3.1),
        ( 'ST006', 'Student6','Advisor2', 5,1.8),
        ( 'ST007', 'Student7','Advisor3', 6,2.2),
        ( 'ST008', 'Student8','Advisor3', 4,3.7),
        ( 'ST009', 'Student9','Advisor3', 3,3.6),
        ( 'ST010', 'Student10','Advisor4',2,3.5)
GO

Let's check the table contents before enabling the security policy:






















Now let's grant SELECT access to all users which are the three advisors and the principal:

--Grant SELECT access to users
GRANT SELECT ON tblStudents TO Advisor1;
GRANT SELECT ON tblStudents TO Advisor2;
GRANT SELECT ON tblStudents TO Advisor3;
GRANT SELECT ON tblStudents TO Principal;
GO

Now it's time to create the Security policy. First we have to create the schema:

--Create security schema
CREATE SCHEMA Security;
GO

Then, we need to create the function that will be used for checking the executing user:

--Create function that checks the executing user
CREATE FUNCTION Security.fn_securitypredicate(@advisorCode AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result 
WHERE @advisorCode= USER_NAME() OR USER_NAME() = 'Principal';
GO

Finally we create and enable the security policy which uses the schema and the function created above:

--Create security policy
CREATE SECURITY POLICY StudentFilter
 ADD FILTER PREDICATE Security.fn_securitypredicate(advisorCode) 
 ON dbo.tblStudents
 WITH (STATE = ON);
GO

Now let's retrieve the records from the the tblStudents table, each time within the context of a different user (i.e. advisor1, advisor2, advisor3 and principal).


If we execute the above query as the user 'Advisor1' we can only see the student records that have 'Advisor1' as their advisor:

















If we execute the above query as the user 'Advisor2' we can only see the student records that have 'Advisor2' as their advisor:

















If we execute the above query as the user 'Advisor3' we can only see the student records that have 'Advisor3' as their advisor:

















If we execute the above query as the user 'Principal' we can see all student records:

























If we disable the policy, then we return to the original state where all the records are retrieved from the tblStudents table within the context of any user that was granted the SELECT permission on the table:































































































From the above example you can see that it's quite easy to control access to your data at the most granular level in the database, that is the record level, by using Row-Level Security in SQL Server 2016.
Read more on this article...