Archive for the ‘SQL’ category

SQL 2016 Servcies packs May 2018

May 31st, 2018

SQL Server 2016 Service Pack 2 came out last month, but Microsoft also just released Service Pack 1 Cumulative Update 9, which has fixes that aren’t in Service Pack 2:
•PFS page round robin algorithm improvement
•Fixed PAGELATCH_EX and PAGELATCH_SH waits in TempDB
•Change tracking is inconsistent during an update on a table with a clustered index
•TDE database goes offline during a log flush

However, they also just released 2016 SP2 CU1! https://support.microsoft.com/en-us/help/4135048/cumulative-update-1-for-sql-server-2016-sp2

SQL Server 2016 Service Pack 2

April 25th, 2018

SQL Server 2016 Service Pack 2 is released. This SP2 release includes the hotfixes from all released 2016 cumulative updates: SQL Server 2016 CU1 through SP1 CU8.

SQL Server 2016 Updates
Each update is linked to its Microsoft knowledge base article with the download and the list of hotfixes included. The dates show the end of support date

SP2 2018/04/24 13.0.5026.0 2026/07/14
CU8 2018/03/19 13.0.4474.0 2019/04/24
CU7 2018/01/04 13.0.4466.4 2019/04/24
CU6 2017/11/22 13.0.4457.0 2019/04/24
CU5 2017/09/18 13.0.4451.0 2019/04/24
CU4 (w/MDS bug) 2017/08/08 13.0.4446.0 2019/04/24
CU3 2017/05/15 13.0.4435.0 2019/04/24
CU2 2017/03/20 13.0.4422.0 2019/04/24
CU1 2017/01/18 13.0.4411.0 2019/04/24
SP1 2016/11/16 13.0.4001.0 2019/04/24
CU9 2017/11/22 13.0.2216.0 2018/01/09 – out of support
CU8 2017/09/18 13.0.2213.0 2018/01/09 – out of support
CU7 2017/08/08 13.0.2210.0 2018/01/09 – out of support
CU6 2017/05/15 13.0.2204.0 2018/01/09 – out of support
CU5 2017/03/28 13.0.2197.0 2018/01/09 – out of support
CU4 2017/01/18 13.0.2193.0 2018/01/09 – out of support
CU3 2016/11/17 13.0.2186.6 2018/01/09 – out of support
CU2 (see note 1 and note 2) 2016/09/22 13.0.2164.0 2018/01/09 – out of support
CU1 2016/07/25 13.0.2149.0 2018/01/09 – out of support
None (RTM) 2016/06/01 13.0.1601.5 2018/01/09 – out of support

Note 1: CU2 has a known issue with Filestream not working when SecureBoot is enabled. If you’re on Windows Server 2016 or Windows 10, and using SecureBoot (which is enabled by default with Hyper-V Gen2 VMs), and your database has Filestream, then either need disable SecureBoot, or skip CU2 for now.

Note 2: columnstore index users should consider the on-demand hotfix update 13.0.2170.0, which includes serious performance and reliability fixes.

Warnin read the bottom note about “Uninstalling SQL Server 2016 SP2 (Not recommended): there some new features which once installed may give issues if you then try to uninstall.

https://www.microsoft.com/en-us/download/details.aspx?id=56836

Microsoft public preview of Azure SQL Database Managed Instances – running SQL Server workloads in the cloud

March 10th, 2018

Microsoft released the public preview of Azure SQL Database Managed Instances – a new option for running SQL Server workloads in the cloud.

Managed Instances (or Azure SQL Database Managed Instances, are a new PaaS database offer that joins the Azure SQL Database and Elastic Pool services. Within this PaaS family, Managed Instances take care of operational aspects like ensuring: high availability, backups, and applying patches, making these simpler and less time-consuming to administer.

While many organizations have benefited from using Azure SQL Database for new applications, it has been a significant challenge to migrate existing workloads because of key functionality gaps versus on-premises SQL Server.

Managed Instances address that problem, by providing vastly improved compatibility. Organizations can therefore more easily migrate existing on-premises SQL Server workloads to the cloud while retaining many of the manageability benefits of a PaaS offering.

Managed Instances require less operational oversight compared to traditional on-premises SQL Server,. Use of the service however, doesn’t free you from the responsibility for checking availability or ensuring that security is configured appropriately. It also remains the DBA’s responsibility to optimize performance, and to handle other operational concerns like making sure jobs complete successfully, or general troubleshooting – its platform as a service. High availability, automated backups, point-in-time restore, automatic plan correction, threat detection, vulnerability assessment, and other intelligent features are built-in into service without any additional charge. OS patching and database upgrades are handled automatically and do not require any action.

In addition to built-in monitoring and maintenance features, you can use any 3rd-party tool to monitor and manage your instance, because most of the system views are exposed.

Connectivity

Azure SQL Managed Instance is not a service on public endpoint. Azure SQL Managed Instance is placed on private IP address in your VNET. It is just hosted and managed by Azure cloud.

Currently, Azure SQL Database PaaS has two main offers for the customers who use SQL Server database and want to migrate to PaaS:
1.Managed Database – isolated and self-contained database service that has database scoped functionalities.
2.Elastic pool – a group of Azure SQL databases that share the same resource.

However, current Azure SQL Database offers don’t provide entire SQL Server “Instance as a Service” as PaaS model. As a result, some of the instance-level features in Azure SQL Database PaaS such as SQL Agent or linked servers are not supported because they are not applicable on the database level.

Currently, the only way to get the full SQL Server instance in Azure is to use Azure SQL VM that handles underlying infrastructure (e.g. disks), but still not have some SQL PaaS features as Azure SQL Database.

Managed Instance is a SQL Server Instance in Azure cloud that shares the same code with the latest version of SQL Server Database Engine and has the latest features, performance improvements, and security patches. It has most of the SQL Server 2017 features (excluding some on-premise Windows features such as Windows logins or potentially harmful features such as extended stored procedures) and enables you to put almost any database that you have in on-premises SQL Server instance. Every instance is fully isolated from the other customer instance and placed in your dedicated subnet with assigned private ip addresses.

Security/Isolation. Managed Instance is a resource in your network hosted by Azure cloud. You have to create Azure VNET and a dedicated subnet where the instance should be deployed. There are networking constraints for the VNET/subnet that you should review before you create a managed instance.

There is no public IP address dedicated to the Managed Instance. Only applications in customer network can access Managed Instance. Network administrators have the full control and can configure access to Managed Instance using standard methods such as Network security Groups and firewalls.

Choose how many CPU cores to use and how much storage you need. You can create a Managed Instance with 16 cores and 500GB storage, and then increase or decrease these numbers depending on your needs. Changing CPU or storage in instance can be done via Azure portal using simple slider.

Managed Instance has split compute and storage components. There are compute nodes with 8, 16, or 24 cores, that work with database files stored on Azure Premium disk storage. Every database file is placed on separate Azure premium disk, that guarantees that database files cannot be lost. Although Managed Instance relies on Azure Premium disks, it also has separate backup mechanism that ensures that even if something would happen with the files, platform would still be able to restore files from backups.

SQL 2008 Extended support ends July 2019

March 4th, 2018

A remdnder that Mainstream Support for SQL Server 2008 and SQL Server 2008 R2 ended on July 8, 2014. – Support Lifecycle policy, found in http://support.microsoft.com/lifecycle.

Customers are encouraged to prepare and execute on their upgrade and/or sustained engineering plans as early as possible for these SQL versions. Remaining current on your SQL Server version ensures that your product remains supported per the Support Lifecycle policy. Additionally, your software benefits from the many enhancements, fixes, and security updates provided through the latest releases.

For both SQL Server 2008 and SQL Server 2008 R2, Microsoft will continue to provide technical support which also includes security updates during the duration of extended support. See the table below for extended support end date. Non-security hotfixes for these versions will be offered only to customers who have an Extended Hotfix Support agreement.

SQL Server 2017 Cumulative Update 4

March 4th, 2018

Last month Microsoft released SQL Server 2017 Cumulative Update 4, which is Build 14.0.3022.28.

There are 55 hotfixes in the public fix list. Run the special T-SQL script in the release notes if you are using Query Store and previously ever had SQL Server 2017 CU2 installed (and you were using Query Store on any of your databases at that time). The script will look for any plans that were forced while you were running SQL Server 2017 CU2, and if it finds any, it will unforce those and then clear those from Query Store.

There are several updates both for Columnstore indexes and for Availability Groups.

There will not be any Service Packs for SQL Server 2017, so test and deploy SQL Server 2017 Cumulative Updates as they become available.

SQL Server 2017 and later versions will no longer receive SPs
The Modern Servicing Model (MSM)

Starting from SQL Server 2017:
• SPs will no longer be available. Only Cumulative Updates (CUs) and critical updates (GDRs) will be provided.
• CUs will contain localized content if it’s necessary as what SPs have done.
• CUs will be delivered more frequently at first and then less frequently: every month for the first 12 months, and then every quarter for the final four years of the five-year mainstream lifecycle.

Note The MSM only applies to SQL Server 2017 and later versions.

Earlier versions of SQL Server are not affected by this SP policy change. Service Packs (SPs) will continue to be provided for the reminder of mainstream support for SQL Server 2014 and SQL Server 2016.

“Meltdown” and “Spectre and azure.”

February 10th, 2018

Last month as reported on this blog, Intel revealed two critical vulnerabilities they found in Intel chips. These vulnerabilities allow cyber-attackers to steal data from the memory of running apps. This data can include passwords, emails, photos, or documents. Intel dubbed these as: “Meltdown” and “Spectre.”

Microsoft released a patch for Azure the very next day. Just as well because Microsoft Azure is a shared-computing environment by default. One server hosts applications and development of applications, and various Virtual Machines tap into the server to allow employees to and others to access these applications. As such, the Meltdown vulnerability allows an attacker to compromise the host and read all the data from every operating system tapping into it. Around 3-10 million physical servers host Azure, and these servers in turn host tens of millions of Virtual Machines. So impressively Microsoft developed deployed a patch for these vulnerabilities in less than a week’s time. Azure is a cloud-based application and so Microsoft could focus their security team to work on the cloud servers and only the cloud servers. This way, these millions of servers and users had a patch and all applications hosted on the Azure cloud-platform were immediately protected.

A good business case example for business to move to Azure cloud services.

Malware developers are still out there. German antivirus testing firm AV-Test reported 139 samples of malware trying to attack the Meltdown vulnerability in January to exploit those who have not patched.

Microsoft patched their cloud servers, but non-Azure users (as well as all Windows users, period) still need to apply their operating system patches to ensure complete protection. This is one vulnerability you definitely don’t want cyber-attackers to exploit, whether it’s your personal computer or your business’s server.

Meltdown and Spectre – why do these matter?

January 6th, 2018

One of the most basic premises of computer security is isolation: When you run somebody else’s code as an untrusted process on your machine, then you restrict it to its own tightly sealed test environment. Otherwise, it might peer into other processes, or snoop around the computer as a whole. A security flaw in computers’ most deep-seated hardware puts a crack in those walls, as one newly discovered vulnerability in millions of processors has done, it breaks some of the most fundamental protections computers promise—and sends practically the entire industry scrambling.

A bug in Intel chips allows low-privilege processes to access memory in the computer’s kernel, the machine’s most privileged inner sanctum. Theoretical attacks that exploit that bug, based on quirks in features Intel has implemented for faster processing, could allow malicious software to spy deeply into other processes and data on the target computer or smartphone. On multi-

Meltdown affects Intel processors, and works by breaking through the barrier that prevents applications from accessing arbitrary locations in kernel memory. Segregating and protecting memory spaces prevents applications from accidentally interfering with one another’s data, or malicious software from being able to see and modify it at will. Meltdown makes this fundamental process fundamentally unreliable.

Spectre affects Intel, AMD, and ARM processors, broadening its reach to include mobile phones, embedded devices, and pretty much anything with a chip in it. Which, of course, is everything from thermostats to baby monitors now.

It works differently from Meltdown; Spectre essentially tricks applications into accidentally disclosing information that would normally be inaccessible, safe inside their protected memory area. This is a trickier one to pull off, but because it’s based on an established practice in multiple chip architectures, it’s going to be even trickier to fix.
user machines, like the servers run by Google Cloud Services or Amazon Web Services, they could allow hackers to break out of one user’s process, and instead snoop on other processes running on the same shared server.

It’s not a physical problem with the CPUs themselves, or a plain software bug you might find in an application like Word or Chrome. It’s in between, at the level of the processors’ “architectures,” the way all the millions of transistors and logic units work together to carry out instructions.

In modern architectures, there are inviolable spaces where data passes through in raw, unencrypted form, such as inside the kernel, the most central software unit in the architecture, or in system memory carefully set aside from other applications. This data has powerful protections to prevent it from being interfered with or even observed by other processes and applications.

Because Meltdown and Spectre are flaws at the architecture level, it doesn’t matter whether a computer or device is running Windows, OS X, Android, or something else — all software platforms are equally vulnerable. A huge variety of devices, from laptops to smartphones to servers, are therefore theoretically affected. The assumption going forward should be that any untested device should be considered vulnerable.

Not only that, but Meltdown in particular could conceivably be applied to and across cloud platforms, where huge numbers of networked computers routinely share and transfer data among thousands or millions of users and instances.

The one crumb of comfort is that the attack is easiest to perform by code being run by the machine itself — it’s not easy to pull this off remotely. So there’s that, at least.

On Wednesday evening, a large team of researchers at Google’s Project Zero, universities including the Graz University of Technology, the University of Pennsylvania, the University of Adelaide in Australia, and security companies including Cyberus and Rambus together released the full details of two attacks based on that flaw, which they call Meltdown and Spectre.

“These hardware bugs allow programs to steal data which [is] currently processed on the computer,” reads a description of the attacks on a website the researchers created. “While programs are typically not permitted to read data from other programs, a malicious program can exploit Meltdown and Spectre to get hold of secrets stored in the memory of other running programs.”

Both attacks are based on the same general principle, Meltdown allows malicious programs to gain access to higher-privileged parts of a computer’s memory, while Spectre steals data from the memory of other applications running on a machine. And while the researchers say that Meltdown is limited to Intel chips, they say that they’ve verified Spectre attacks on AMD and ARM processors, as well. With these glitches, if there’s any way an attacker can execute code on a machine, then it can’t be contained.

Meltdown and Spectre

https://twitter.com/brainsmoke/status/948561799875502080

When processors perform speculative execution, they don’t fully segregate processes that are meant to be low-privilege and untrusted from the highest-privilege memory in the computer’s kernel. That means a hacker can trick the processor into allowing unprivileged code to peek into the kernel’s memory with speculative execution.

he processor basically runs too far ahead, executing instructions that it should not execute. .

Retrieving any data from that privileged peeking isn’t simple, since once the processor stops its speculative execution and jumps back to the fork in its instructions, it throws out the results. But before it does, it stores those in its cache, a collection of temporary memory allotted to the processor to give it quick access to recent data. By carefully crafting requests to the processor and seeing how fast it responds, a hacker’s code could figure out whether the requested data is in the cache or not. And with a series of speculative execution and cache probes, he or she can start to assemble parts of the computer’s high privilege memory, including even sensitive personal information or passwords.

Many security researchers who spotted signs of developers working to fix that bug had speculated that the Intel flaw merely allowed hackers to defeat a security protection known as Kernel Address Space Layout Randomization, which makes it far more difficult for hackers to find the location of the kernel in memory before they use other tricks to attack it, but the bug is more serious: It allows malicious code to not only locate the kernel in memory, but steal that memory’s contents, too.

Tough Fix

In a statement responding to the Meltdown and Spectre research, Intel noted that “these exploits do not have the potential to corrupt, modify, or delete data,” though they do have the ability to spy on privileged data. The statement also argued that “many types of computing devices—with many different vendors’ processors and operating systems—are susceptible to these exploits,” mentioning ARM and AMD processors as well.

Microsoft, which relies heavily on Intel processors in its computers, says that it has updates forthcoming to address the problem. “We’re aware of this industry-wide issue and have been working closely with chip manufacturers to develop and test mitigations to protect our customers,” the company said in a statement. “We are in the process of deploying mitigations to cloud services and are releasing security updates today to protect Windows customers against vulnerabilities affecting supported hardware chips from AMD, ARM, and Intel. We have not received any information to indicate that these vulnerabilities had been used to attack our customers.”

Linux developers have already released a fix, apparently based on a paper recommending deep changes to operating systems known as KAISER, released earlier this year by researchers at the Graz University of Technology.

Apple released a statement Thursday confirming that “all Mac systems and iOS devices are affected,” though the Apple Watch is not. “Apple has already released mitigations in iOS 11.2, macOS 10.13.2, and tvOS 11.2 to help defend against Meltdown,” the company said. “In the coming days we plan to release mitigations in Safari to help defend against Spectre. We continue to develop and test further mitigations for these issues and will release them in upcoming updates of iOS, macOS, tvOS, and watchOS.”

Amazon, which offers cloud services on shared server setups, says that it will take steps to resolve the issue soon as well. “This is a vulnerability that has existed for more than 20 years in modern processor architectures like Intel, AMD, and ARM across servers, desktops, and mobile devices,” the company said in a statement. “All but a small single-digit percentage of instances across the Amazon EC2 fleet are already protected. The remaining ones will be completed in the next several hours.”

Google, which offers similar cloud services, pointed WIRED to a chart of Meltdown and Spectre’s effects on its services, which states that the security issue has been resolved in all of the company’s infrastructure.

Those operating system patches that fix the Intel flaw may come at a performance cost: Better isolating the kernel memory from unprivileged memory could create a significant slowdowns for certain processes.

According to an analysis by the Register, which was also the first to report on the Intel flaw, those delays could be as much as 30 percent in some cases, although some processes and newer processors are likely to experience less significant slowdowns. Intel, for its part, wrote in its statement that “performance impacts are workload-dependent, and, for the average computer user, should not be significant and will be mitigated over time.”

Until the patches for Meltdown and Spectre roll out more widely, it’s not clear just what the speed cost of neutering those attacks may turn out to be. But even if the updates result in a performance hit, it is a worthwhile safeguard: Better to put the brakes on your processor, perhaps, than allow it to spill your computer’s most sensitive secrets.

Spectre, is not likely to be fully fixed any time soon. The fact is that the practice that leads to this attack being possible is so hard-wired into processors that the researchers couldn’t find any way to totally avoid it. They list a few suggestions, but conclude:

While the stop-gap countermeasures may help limit practical exploits in the short term, there is currently no way to know whether a particular code construction is, or is not, safe across today’s processors – much less future designs.

Critical Server Patches for Meltdown and Spectre – processor bugs

January 5th, 2018

There is a set of critical bugs in our processors. There are two issues, known as Meltdown and Spectre.

If you haven’t been paying attention, a serious security flaw in nearly every processor made in the last ten years was recently discovered. Initially it was thought to be just Intel, but it appears it’s everyone. The severe design flaw in microprocessors allows sensitive data, such as passwords and crypto-keys, to be stolen from memory is real – and its details have been revealed.
On a shared system, such as a public cloud server, it is possible, depending on the configuration, for software in a guest virtual machine to drill down into the host machine’s physical memory and steal data from other customers’ virtual machines.

This is so serious CERT recommends throwing away your CPU and buying a non-vulnerable one to truly fix the issue.

https://www.kb.cert.org/vuls/id/584653

There are two bugs which are known as Meltdown and Spectre. The Register has a great summarized writeup here – no need for me to regurgitate. This is a hardware issue – nothing short of new chips will eradicate it. That said, pretty much everyone who has written an OS, hypervisor, or software has (or will have) patches to hopefully eliminate this flaw. This blog post covers physical, virtualized, and cloud-based deployments of Windows, Linux, and SQL Server.

The fact every vendor is dealing with this swiftly is a good thing. The problem? Performance will most likely be impacted. No one knows the extent, especially with SQL Server workloads. You’re going to have to test and reset any expectations/performance SLAs. You’ll need new baselines and benchmarks. There is some irony here that it seems virtualized workloads will most likely take the biggest hit versus ones on physical deployments. Time will tell – no one knows yet.

What do you need to do? Don’t dawdle or bury your head in the sand thinking you don’t need to do anything and you are safe. If you have deployed anything in the past 10 – 15 years, it probably needs to be patched. Period. PATCH ALL THE THINGS! However, keep in mind that besides this massive scope, there’s pretty much a guarantee – even on Linux – you will have downtime associated with patching.
Information that you might want to review and decide how to patch your systems.

SQL Server Versions Affected

This is a hardware issue, so every system is affected SQL Server running on x86 and x64 .for these versions:

SQL Server 2008
SQL Server 2008R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
Azure SQL Database

It is likely that SQL Server 2005, SQL Server 2000, SQL Server 7, SQL Server 6.5 are all affected. No SQL Server patches are coming.

Note: according to Microsoft, IA64 systems are not believed to be affected.

SQL Server Patches

There is a KB that discusses the attacks. Here are the patches as of this time:

SQL Server 2017 CU3
SQL Server 2017 GDR
SQL Server 2016 SP1 CU7
SQL Server 2016 SP1 GDR
.
OS Patches

The Window KB for guidance is 4072698. Here are the OS patches that I’ve been able to find.

Windows Server (Server Core) v 1709 – KB4056892
Windows Server 2016 – KB4056890
Windwos Server 2012 R2 – KB4056898
Windows Server 2012 – N/A
Windows Server 2008 R2 – KB4056897
Windows Server 2008 – N/A
Red Hat v.7.3 – Kernel Side-Channel Attacks CVE-2017-5754, 5753, 5715
SUSE Linux – 7022512
Ubuntu – N/A

VMWare has a security advisory (VMSA-2018-0002) and patches. They have released:

ESXi 6.5
ESXi 6.0
ESXi 5.5 (partial patch)
Workstation 12.x – Upgrade to 12.5.8
Fusion 8.x – Updated to 8.5.9

When to PATCH – Immediately

If you have SQL Server 2017 or SQL Server 2016 running, then patches are available.

SQL Server (Windows) VM in your data center – Patch host OS or isolate SQL Server back on physical hardware. Check Windows OS for microcode changes.

SQL Server (Windows) on bare metal or VM, not isolated from application code on the same machine, or using untrusted code – Apply OS patches, SQL Server patches, enable microcode changes.

SQL Server Linux – Apply Linux OS patches, Linux SQL Server patches, check with Linux vendor

Note that when untrusted SQL Server extensibility mechanisms are mentioned, they mean:

SQL CLR
R and Python packages running through sp_external_script, or standalone R/ML Learning Studio on a machine
SQL Agent running ActiveX scripts
Non-MS OLEDB providers in linked servers
Non-MS XPs

There are mitigations in the SQL Server KB.

When You Can Patch Later

If you have SQL Server 2008, 2008 R2, 2012, 2014 you’ll have to wait on SQL Server patches. They aren’t out yet. However, there are other situations that remove an immediate need for patching.

When You Don’t Need to Patch
If you are on AWS, they’ve patched their systems, except for EC2 VMS. Those need patches from you. AWS Statement

Azure is patched according to KB4073235. Guidance in ADV180002 says .This does not include VMs that don’t get automatic updates. You need to patch those manually.

Apple – If you’re running High Sierra, Sierra, or El Capitan, it looks like Apple took care of this back in December of 2017.

Browsers

Chrome – It looks like Google is going to release a patch for Chrome later in January. See this link for more information.
Firefox – Version 57 or later has the proper fixes. See this blog for more information, so patch away!
Edge and Internet Explorer – Microsoft has a blog post . It looks like the January security update (KB4056890) takes care of that. So if you’re using either of these browsers, please update your OSes as soon as possible.

Details On the Exploits

Descriptions of the exploit, if you want to dig down and understand.

https://meltdownattack.com/

The Register
Ars Technia
cyber.wtf researcher blog

SQL Server 2014 SP2 CU9

January 2nd, 2018

On December 18, 2017, Microsoft released SQL Server 2014 SP2 CU9, which is Build 12.05563.0.
This CU has seven public hotfixes, most of which are for the SQL Engine of SQL performance -critical for taks like mrp. inventory close, consolidation etc.

Since SQL Server 2014 SP1 and earlier are no longer “supported service packs”, there is no corresponding CU for the SP1 or RTM branches of SQL Server 2014.

As always, make an effort to stay current on cumulative updates

SQL version – when should you upgrade – ask your Dynamics U.A.E. Partner, Synergy Software Systems

December 23rd, 2017

SQL Server for many years on a two-year release cycle. SQL Server 2017 arrived less than 18 months after SQL Server 2016 became available.

Since 2005 each release of SQL Server brings exciting new features and improvements to existing capabilities. Many organizations are running instances that are several versions of SQL Server behind.

To keep up with the latest SQL Server versions is a challenge, but risks losing mainstream support and missing out on beneficial features. Often database administrators must support multiple versions at once, and consultants face an even greater range of versions across their customers.

Microsoft has not committed to any specific release cadence for ersions of SQL Server. Many clients it seems are still running SQL Server 2008 R2. One reason why companies are hesitant to make the move off 2008 R2 is because of the change to per core licensing. The effort to test and to upgrade is discouraging, but it is best to do this on a planned basis than a reaction to a crisis..

It was a painful experience to upgrade from SQL Server 2000, but the compatibility gap between versions is much narrower once past 2005. To make upgrading easier, provides a tool called The Upgrade Advisor for each new version that will spot issues and provide a chance to resolve them before starting the upgrade process. Virtualization also makes setting up testing environments much simpler and quicker.

With each new version there are enhancements to T-SQL, improved availability and disaster recovery functionality, more security options, and additional ways to get better performance. 2016 service pack 1, was a game change – many previously Enterprise only features were ported down to more affordable editions.

Another consideration is support. It doesn’t take long to reach the end of mainstream support. SQL Server 2008 R2, for example, has been out of mainstream support since 2014. While it’s still in extended support, which will ensure security hotfixes, other support features are available only on a paid basis.

When you look at erp upgrades it makes sense to also review your SQL upgrade plans.

SQL Server 2017

November 22nd, 2017

SQL Server 2017 went on general release a couple of weeks ago. This brings a whole host of benefits

Microsoft SQL Server 2017 features the much-anticipated SQL Graph, which provides new graph database capabilities for representing complex many-to-many relationships. Social media platforms like Facebook and LinkedIn use graph databases extensively, and in the era of big data, use cases are emerging across many industries.

Businesses can explore these relationships to reveal valuable information – from changes in the types of structures to the query abilities being requested of you and your teams. Whether it’s identifying similarities in customers behind trends in purchasing behavior, or mapping patterns in credit card usage to determine credit limits or risk indicators of defaulting on repayments, the introduction of graph capabilities to SQL Server makes the processes more streamlined.

Perhaps the most touted feature of the new version is that it will be available to be installed on Linux; an entirely inconceivable premise 10 years ago, which just goes to show how far Microsoft have changed in their approach to supporting non-Windows platforms as standard.

The announcement earlier this year that Power BI would be included as part of SSRS in was welcome. Previously, each tool was well suited for a specific reporting purpose – SSRS was great for designing reports that require a lot of visual tailoring and widely common formats for exporting, whereas Power BI is more geared towards real-time, dashboard views that marry together disparate data sources in a straightforward way. By being able to leverage SSRS to fully utilise Power BI reports, the application suddenly becomes a lot more versatile and the potential for combining together functionality becomes a lot more recognisable. So, for example, having the ability to drill down to an SSRS report from a Power BI report would be an excellent way of providing reporting capabilities that satisfy end-user consumption in 2 different, but wildly applicable, scenarios

The updated SSMS client for SQL Server 2017 has been given refreshed icons that bring the application more in line with how Visual Studio and other Microsoft products are looking these days

Inside a Microsoft cloud data centre with Synergy Software Systems

November 22nd, 2017

Get the reach and local presence you need with Microsoft’s global datacenters – https://azure.microsoft.com/en-us/regions/ Azure is generally available in 36 regions around the world, with plans announced for 6 additional regions.

Go beyond the limits of your on-premises datacenter using the scalable, reliable infrastructure that powers the Microsoft Cloud.

Transform your business and reduce maintenance costs with an energy-efficient infrastructure spanning more than 100 highly secure facilities worldwide, linked by one of the largest networks on earth.

The engine that powers Microsoft’s cloud services, the is designed to support smart growth, high reliability, operational excellence, cost-effectiveness, environmental sustainability, and a trustworthy online experience for customers and partners worldwide.

Microsoft deliver the core infrastructure and foundational technologies for Microsoft’s over numerous online businesses including: Dynamics 365, Power Bi, Cortana analytics, IoT, Bing, MSN, Office 365, Xbox Live, Skype, OneDrive and the Windows Azure platform.

The infrastructure is comprised of a large global portfolio of more than 100 datacenters and 1 million servers, content distribution networks, edge computing nodes, and fiber optic networks.

The portfolio is built and managed by a team of subject matter experts working 24x7x365 to support services for more than 1 billion customers and 20 million businesses in over 90 countries worldwide

Those are 2014 figures and the Microsoft cloud has expanded greatly since then for example the acquisition of Linked in and the launch of Dynamics 365.

To help you comply with national, regional, and industry-specific requirements governing the collection and use of individuals’ data, Microsoft offers the most comprehensive set of compliance offerings of any cloud service provider. Microsoft business cloud services operate with a cloud control framework, which aligns controls with multiple regulatory standards (https://www.microsoft.com/en-us/trustcenter/guidance/risk-assessment#Audit-reports)

Argentina PDPA – Microsoft has implemented the security measures in the Argentina Personal Data Protection Act.

BIR 2012 – Agencies operating in the Netherlands government sector must comply with the Baseline Informatiebeveiliging Rijksdienst standard.

Canadian Privacy Laws – Microsoft contractually commits to implementing security that helps protect individuals’ privacy.

CCSL (IRAP) – Microsoft is accredited for the Australian Certified Cloud Services List based on an IRAP assessment.

CDSA – Azure is certified to the Content Delivery and Security Assoc. Content Protection and Security standard.

China DJCP – Azure and Office 365 operated by 21Vianet are rated at Level 3 for information security protection.

China GB 18030 – Azure and Office 365 operated by 21Vianet are certified as compliant with the Chinese character standard.

China TRUCS – Azure and Office 365 operated by 21Vianet obtained Trusted Cloud Service certification.

CJIS – Microsoft government cloud services adhere to the US Criminal Justice Information Services Security Policy.

CS Mark (Gold) – Microsoft received the CS Gold Mark in Japan for Azure (IaaS and PaaS) and Office 365 (SaaS).

CSA STAR Attestation -Azure and Intune were awarded Cloud Security Alliance STAR Attestation based on an independent audit.

CSA STAR Certification – Azure, Intune, and Power BI were awarded Cloud Security Alliance STAR Certification at the Gold level.

CSA STAR Self-Assessment – Microsoft STAR Self-Assessment details how cloud services fulfill Cloud Security Alliance requirements.

DFARS – Microsoft Azure Government supports Defense Federal Acquisition Regulation (DFARS) requirements.

DoD – Microsoft received Department of Defense (DoD) Provisional Authorizations at Impact Levels 5, 4, and 2.

EN 301 549 – Microsoft meets EU accessibility requirements for public procurement of ICT products and services.

ENISA IAF – Azure aligns with the ENISA framework requirements through the CSA CCM version 3.0.1.

EU Model Clauses – Microsoft offers EU Standard Contractual Clauses, guarantees for transfers of personal data.

EU-U.S. Privacy Shield – Microsoft complies with this framework for protecting personal data transferred from the EU to the US.

FACT – Microsoft Azure achieved certification from the Federation Against Copyright Theft in the UK.

FDA CFR Title 21 Part 11 – Microsoft helps customers comply with these US Food and Drug Administration regulations.

FedRAMP – Microsoft was granted US Federal Risk and Authorization Management Program P-ATOs and ATOs.

FERPA – Microsoft aligns with the requirements of the US Family Educational Rights and Privacy Act.

FIPS 140-2 – Microsoft certifies that its cryptographic modules comply with the US Federal Info Processing Standard.

FISC – Microsoft meets the requirements of the Financial Industry Information Systems v8 standard in Japan.

GxP – Microsoft cloud services adhere to Good Clinical, Laboratory, and Manufacturing Practices (GxP).

HIPAA/HITECH – Microsoft offers Health Insurance Portability & Accountability Act Business Associate Agreements (BAAs).

HITRUST – Azure is certified to the Health Information Trust Alliance Common Security Framework.

IRS 1075 – Microsoft has controls that meet the requirements of US Internal Revenue Service Publication 1075.

ISO 9001 – Microsoft is certified for its implementation of these quality management standards.

ISO 20000-1:2011 – Microsoft is certified for its implementation of these service management standards.

ISO 22301 – Microsoft is certified for its implementation of these business continuity management standards.

ISO 27001 – Microsoft is certified for its implementation of these information security management standards.

ISO 27017 – Microsoft cloud services have implemented this Code of Practice for Information Security Controls.

ISO 27018 – Microsoft was the first cloud provider to adhere to this code of practice for cloud privacy.

IT Grundschutz Compliance Workbook – Azure Germany published this Workbook to help our clients achieve IT Grundschutz certification.

ITAR – Azure Government supports customers building US International Traffic in Arms Regs-capable systems.

MARS-E – Microsoft complies with the US Minimum Acceptable Risk Standards for Exchanges (MARS-E).

MeitY – The Ministry of Electronics and Info Technology in India awarded Microsoft a Provisional Accreditation.

MPAA – Azure successfully completed a formal assessment by the Motion Picture Association of America.

MTCS – Microsoft received certification for the Multi-Tier Cloud Security Standard for Singapore.

My Number (Japan) – Microsoft does not have standing access to My Number data, a number unique to each resident of Japan.

NEN 7510:2011 – Organizations in the Netherlands must demonstrate control over patient health data in accordance with the NEN 7510 standard.

NHS IG Toolkit – Azure is certified to the Health Information Trust Alliance Common Security Framework.

NIST 800-171 – Microsoft DoD certifications address and exceed US NIST 800-171 security requirements.

NIST CSF – Microsoft Cloud Services meet the National Institute of Standards and Technology (NIST) Cybersecurity Framework (CSF)

NZ CC Framework – Microsoft NZ addresses the questions published in the New Zealand cloud computing framework.

PCI DSS – Azure complies with Payment Card Industry Data Security Standards Level 1 version 3.1.

Section 508 – Microsoft cloud services offer Voluntary Product Accessibility Templates.

Shared Assessments – Microsoft demonstrates alignment of Azure with this program through the CSA CCM version 3.0.1.

SOC 1- Microsoft cloud services comply with Service Organization Controls standards for operational security.

SOC 2 – Microsoft cloud services comply with Service Organization Controls standards for operational security.

SOC 3 – Microsoft cloud services comply with Service Organization Controls standards for operational security.

Spain ENS – Microsoft received Spain’s Esquema Nacional de Seguridad (National Security Framework) certification.

UK Cyber Essentials PLUS – Cyber Essentials PLUS is a UK government-defined scheme to help organizations protect against common cyber-security threats.

UK G-Cloud – The Crown Commercial Service renewed the Microsoft cloud services classification to Government Cloud v6.

WCAG 2.0 – Microsoft cloud services comply with the Web Content Accessibility Guidelines 2.0.

SQL Server 2012 Service Pack 4 (SP4) is available

October 16th, 2017

SQL Server 2012 Service Packs, Service Pack 4 (SP4). This release of SQL 2012 Service Pack has 20+ improvements centered around performance, scalability and diagnostics to enable SQL Server 2012 to perform faster and scale out of the box on modern hardware design.

SQL Server 2012 SP4 includes all the fixes up to and including SQL Server 2012 SP3 CU10

SQL 2016 new cumulative update releases

September 26th, 2017

The 5th cumulative update release for SQL Server 2016 SP1 is available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.

To learn more please visit:
•CU#5 KB Article: https://support.microsoft.com/en-us/help/4040714/cumulative-update-5-for-sql-server-2016
•Understanding Incremental Servicing Model for SQL Server
•Microsoft ® SQL Server ® 2016 SP1 Latest Cumulative Update: https://www.microsoft.com/en-us/download/details.aspx?id=54613
•Update Center for Microsoft SQL Server: http://technet.microsoft.com/en-US/sqlserver/ff803383.aspx
The 8th cumulative update release for SQL Server 2016 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.

To learn more visit:
•CU#8 KB Article: https://support.microsoft.com/en-us/help/4040713/cumulative-update-8-for-sql-server-2016
•Understanding Incremental Servicing Model for SQL Server
•Microsoft ® SQL Server ® 2016 RTM Latest Cumulative Update: https://www.microsoft.com/en-us/download/details.aspx?id=53338
•Update Center for Microsoft SQL Server: http://technet.microsoft.com/en-US/sqlserver/ff803383.aspx

Backup Compression and Transparent Data Encryption (TDE)

September 26th, 2017

Backup Compression and Transparent Data Encryption (TDE) have been immensely valuable and popular features in SQL Server.

In SQL Server 2016, backup compression was enabled for TDE databases. When you backup a TDE enabled database with compression and MAXTRANSFERSIZE > 64K, backup compression will kick in to reduce backup size and to improve overall backup performance and time.

Recently, Microsoft found some edge scenarios related to backup compression for TDE databases caused backups or restores to fail.

Hence our recommendations have been
• Avoid using striped backups with TDE and backup compression.

• If your database has virtual log files (VLFs) larger than 4GB, then do not use backup compression with TDE for your log backups.

• Avoid using WITH INIT when working with TDE and backup compression. Instead, use WITH FORMAT.

• Avoid using backup checksum with TDE and backup compression

Note: The default native backup uses MAXTRANSFERSIZE = 64K when the database has a single database file, so compression doesn’t kick in automatically for TDE enabled databases and the above issues aren’t encountered.

However, there are c scenarios (listed below) where the SQL server engine chooses to use MAXTRANSFERSIZE > 64K dynamically to optimize for performance. That means compression for TDE databases can kick in automatically even when maxtransfersize is not explicitly set.

• When the database has multiple data files created, it uses MAXTRANSFERSIZE > 64K for which the backup compression kicks in automatically if compression is specified and database has TDE enabled.
• When performing backup to url, the default MAXTRANSFERSIZE = 1MB for which the backup compression kicks in automatically if compression is specified and database has TDE enabled.

Starting SQL 2016 RTM CU7, SQL 2016 SP1 CU4 and above, improvements and updates to the SQL Server engine avoid these edge cases . If you plan to leverage native backup compression for TDE databases or are already using it, we strongly recommend you apply the latest CUs on SQL 2016 to ensure you are not hitting any of the known issues .

If you are already using backup compression for TDE databases in your environment on SQL 2016 builds below RTMCU7/SP1CU4, then we strongly recommend you validate your backups by a test restore and also apply the latest CUs proactively to ensure your RPO/RTO requirements are met.

If the restore of the backup from SQL Server build below RTMCU7/SP1CU4 is failing, then applying the latest CUs won’t allow restore of older backups.

Only backups created from the latest CU build will avoid the above issues and can be restored without any errors.

VDI support for backup compression on TDE enabled databases is not added yet and Microsoft plans to add it soon in an upcoming service releases of SQL Server.