Archive for the ‘SQL’ category

SQL Server 2008 and SQL Server 2008 R2 – end of life July 9, 2019 -ask Synergy Software Systems

June 23rd, 2019

Microsoft has previously announced that SQL Server 2008 and SQL Server 2008 R2 will reach end of life on July 9, 2019.

This means that in less than a month, Microsoft will no longer release regular security updates for the product.

There are several reasons this is important to you.
• Attacks against software products of all types are common and ongoing. With Microsoft SQL being such a prevalent platform, attacks against it are ubiquitous, and it’s important to keep your database platform up-to-date with the latest Microsoft security patches.
• Many compliance requirements dictate that you must be running currently supported software.
• As Microsoft drops support for a product, many third-party applications may also discontinue support for their products running on those platforms.

So, if you are still running SQL Server 2008/2008 R2, then what are your options?

1.Upgrade to a newer version of SQL.
SQL 2019 is in preview release as of this writing, so the current production version of SQL Server is 2017. Its end of life will be October 12, 2027.
Evaluate your applications and databases to make sure they are compatible e.g. Dynamic Ax 2012 is not supported beyond SQL 2016

Plan a migration for either on-premises or cloud. A move to an Azure SQL Database Managed Instance, will not require you to upgrade in the future. By choosing this option, you will also gain access to new features which have appeared in the latest SQL Server versions. However, it only offers subset of SQL features so you need to be sure it will support your application and use.

2.Migrate to Azure to receive three more years of Extended Security Updates for SQL Server 2008/2008 R2. If you need to stay on the same SQL code base for a bit longer, Microsoft will allow you to rehost your SQL 2008 environment in Azure and still provide you with security updates for an extended period. There is no extra cost for the extended updates beyond the standard Azure VM rates.

3.Purchase extended support. Microsoft allows customers with an active Enterprise Agreement and Software Assurance subscription to purchase and receive three years of Extended Security Updates for SQL Server 2008/2008 R2. The annual outlay for the updates is 75% of the full license cost.

4.The least desirable option is to stay where you are and pray. If circumstances prevent you from moving forward now, then at minimum you should:
• Recognize and account for the risk;
•Plan and budget for a transition as soon as possible;
•Re-evaluate your security and tighten it as much as possible.

Microsoft provides guidance for handling the end of support of SQL Server 2008/2008 R2 at https://www.microsoft.com/2008-eos.

Of course, Synergy is ready to help you to evaluate and to progress to the next level. 0097143365589

If you are running newer versions of SQL Server, then here are their End-of-Life dates.
•SQL Server 2012 – July 12, 2022
•SQL Server 2014 – July 9, 2024
•SQL Server 2016 – July 14, 2026
•SQL Server 2017 – October 12, 2027

Windows Server 2008 and 2008 R2, support is coming to an end.

June 23rd, 2019

Sometimes lifecycles end because of age or workload and other times they expire due to vendor support.
In the case of Windows Server 2008 and 2008 R2, Microsoft announced that Extended Support will end on January 14, 2020.

Microsoft provides: Mainstream Support, Extended Support, and Beyond End of Support.

Mainstream Support

Mainstream Support is Microsoft’s first phase of support and lasts five years. It includes the following benefits:
• Incident support (no-charge incident support, paid incident support, support charged on an hourly basis, support for warranty claims)
• Security update support
• Ability to request non-security updates

Extended Support

The Extended Support phase follows Mainstream Support, and also lasts five years. The key features of Extended Support are:
• Paid support
• Security updates at no additional cost
• Ability to request non-security updates (available only via Unified Support, a new model of support that offers comprehensive support that covers your entire organization)
• Microsoft will not accept requests for warranty support, design changes, or new features during the Extended Support phase.

Beyond End of Support

The Beyond End of Support phase is the final phase of the product lifecycle and lasts for three years. Here are the key things to remember.
• Request to change product design and features are not available
• Security updates are available only with the purchase of the Extended Security Update Program for up to three years. This typically costs 75% of the on-premises license cost annually.
• Technical support is provided when you purchase Extended Security Updates and have an active support plan in place on the product that has moved beyond the Extended Support date.

Server 2008 and 2008R2 are moving out of the Extended Support phase on January 14, 2020. From that date on,
non-security updates will no longer be available,
security updates will be available only if you pay for the Extended Security Update Program,
and other vendors will diminish their support of this operating system version.
If you are not prepared, then this will leave your environment open to security holes, application instability, and support restrictions.
If you have not already planned for this then now is the time to get it into your budget for first thing next year.

Summer discount on SQL protection tools

June 18th, 2019

Security, privacy, performance, and up time, all depend on a well maintained database with timely reports and alerts.
for an Enterprise system this is mission critical.
Few DBAs have the time and training to write and maintain a comprehensive set of scripts. Yet they also often lack the tools to do the job.
We offer fantastic suite of tools to help you to mange your SQL databases and with discounted bundled prices.
With privacy laws and compliance adding to the ever rising security threats, a well managed system is now often key to whether a customer will share data it you, and directly impacts whether you can secure and retain business.

Talk yo us about System health checks and administrator training, and how you can reduce administrative cost, and system risks with SQL management tools.
00971 43365589

Major SQL updates don’t skip – SQL Server 2016 SP2 CU7 and SQL Server 2017 CU 15

May 26th, 2019

This week, Microsoft released two major updates.

SQL Server 2016 SP2 CU7 has multiple fixes including:

• Filtered index corruption
• Access violations in sys.dm_exec_query_statistics_xml, sys.dm_hadr_availability_replica_states, sys.availability_replicas, sys.dm_db_xtp_hash_index_stats, sys.fn_dump_dblog, sys.dm_db_xtp_checkpoint_files
(I.e. if you monitor your servers, which you should, then you should apply this CU to avoid problems caused by the monitoring tool’s queries)
• AG failover fails
• Incorrect query results on columnstore indexes, and also this

SQL Server 2017 CU 15 has even MORE fixes, read the full list. https://support.microsoft.com/en-us/help/4498951/cumulative-update-15-for-sql-server-2017

Note also, that from SQL Server 2017, the Analysis Services build version number and SQL Server Database Engine build version number do not match

There are some CUs you might be tempted to skip because they don’t affect you. These releases will affect a wide range of features and you should plan to apply these sooner than later.

SQL 2014 updates April 2019

April 18th, 2019

SQL Server 2014 seems to have become a lot more stable i.e this shot list is the entire list of fixes in Service Pack 3, Cumulative Update 3:
• Query plans are different on cloned databases
• CDC source preview fails with an error
• Incorrect query results for columnstore filter pushdowns
• Log reader agent fails after AG failover when trace flag 1448 is enabled

And for the entire list of fixes in Service Pack 2, Cumulative Update 17:
• SQL jobs fail due to blocking in SSISDB
• Fail to join the secondary replica if the database has a defunct filegroup
• Incorrect query results for columnstore filter pushdowns

Power BI update March – April 2019

March 21st, 2019

Microsoft launched the public preview of new Power BI workspace experiences in August 2018 to enable Power BI workspace admins:
• to use security groups to manage access to workspaces,
• to enable BI teams to create workspaces without needing to create an Office 365 Group,
• to provide granular workspace roles to make giving access to workspaces easier.

At the beginning of April 2019, the new workspace experiences. will reach General Availability (GA)

Usage metrics for new workspaces are rolling out this week
This capability is much requested by customers and works the same as it did for classic workspaces based on Office 365 Groups. It may take until late this week or next week to reach all commercial cloud customers.

https://powerbi.microsoft.com/en-us/blog/update-on-the-new-workspace-experiences-preview-including-ga-timeline/

The March update for the On-premises data gateway (version 3000.2.47) includes an updated version of the Mashup Engine, which matches the one released as part of the Power BI Desktop March update.

This will ensure that the reports that you publish to the Power BI Service and refresh via the Gateway will go through the same query execution logic/runtime as in the latest Power BI Desktop version.

Happiness Day, Loneliness and Power BI

March 21st, 2019

At the annual Gartner BI Bake Off session at the Gartner Data and Analytics Summit in Orlando, Florida the Power BI team featured this report which you can explore here:

Here are some insights and highlights from the report:
• The employment groups with the most happiness are employed and retired people followed closely by stay at home parents and students.
• The highest ratio of lonely to non-lonely people by age group is between 35 and 44 years old.
• For the countries in the dataset, the UK and the US have higher loneliness ratios (0.30 and 0.29 respectively) than Japan (0.10).

if you think Power BI might provide insights into your business, and need training or assistance with report modelling, or need to understand the different licence types, then contact us – 009714 3365589

SQL 2016 SP2 CU6 is released also SQL 2016 SP1 CU 14

March 21st, 2019

The good news is that Microsoft has fixed the “String or binary data would be truncated” error!
With KB#4468101 if you enable trace flag 460, the SQL Server will now tell you just what l is going to be truncated.
SP2 CU6 also includes other fixes like:
• Users are incorrectly permitted to create incremental stats on nonclustered indexes that aren’t aligned to the base table
• Assertion for parallel deletes from filestream tables
• Filestream IO can’t be enabled on cluster shared volumes
• Assertion for linked server queries that point to themselves during a cross-database transaction (as bad as it sounds)
• MDS database upgrade fails
• Filtered nonclustered columnstore index over a clustered columnstore index may not be maintained ( in layman’s term we call it corruption)
• Stack dump during change tracking cleanup
• Data masking (doesn’t)

Microsoft also released an 2016 SP1 CU14 but that doesn’t have the key#4468101 fix. .

Cloud back ups or on-premise?

February 16th, 2019

Pretty scary.
We have suffered catastrophic destruction at the hands of a hacker, last seen as aktv@94.155.49.9 This person has destroyed all data in the US, both primary and backup systems. We are working to recover what data we can.

Though they’re back up and running, who knows if customers will stick by them, or will sue them.
What impact that had on infrastructure mail servers, backup servers, and SQL Servers for customers is hard to judge.
A large number of people might have lost their mailboxes and previously stored mail that was in IMAP storage.
This is likely an annoyance for individuals, but potentially catastrophic for businesses. Imagine your small business hosted with them and all your mailboxes were lost with customer communications and who knows what else.

Could this happen with a cloud provider like Azure O365, Google Apps or AWS?
Maybe but they will have DR backups,
But what if you store back ups on the cloud but run on premise- how long would it take to mass restore multiple, customers? Do you still have ad3qute on premise test systems to restore on and the staff and the time to do it?

Do you assume that you will always have either a primary server and an online backup server/share/bucket/container and can download data.
The problem is that online systems that connect to the primary can be accessed.
If an attacker were to access one, they potentially could access the second.
The world seems to be moving towards more online storage, or in the case of cloud vendors, a reliance on snapshots. That might be good enough for cloud vendors, but is it good enough for your on-premise system.
It’s likely that an attacker, possibly even with insider help, would wipe out backups first, then primary systems.
Some sort of disconnected offline backup of data, especially database servers gives you a third line of defence.
don’t forget that back up- need to be tested- if the back up software compatible with old versions, does your back up use the same version as the current erp software installed on your primary, or the same SQL version (i.e when you upgrade do you also upgrade your back ups, or maintain an older environment?)

Microsoft and other large vendors have had downtime whether self induced by releasing code too early, or due to hardware failure, or malicious attach . What is important to realise is just how infrequent are just issues given the number of clients they have across a range of solutions, and how little was the downtime and how fast they are at in addressing issues that arise. The think about how you would have been able to deal with the same issues in your own server room?

There are increasing risks, and increasing issues of statutory compliance with regard to data protection e.g, GDPR. The cloud generally offers cheap storage nd robust systems, yet it needs to be part of a holistic approach to reduce overall risk and cost, and not the only line of defence.

SQL Server 2016 SP2 CU5, SP1 CU13 – many fixes

January 25th, 2019

Many fixes inside SP2 CU5 and SP1 CU13, e.g.:
• Access violation when you compile a query
• Access violations and unhandled exceptions with Always On Availability Groups automatic seeding
• Dynamic Data Masking doesn’t when there’s a cursor involved
• Access violations for XML data types
• Query Store blocks transactions and log truncation
• Out of memory errors
• Non-yielding schedulers with heavy use of prepared statements
• Can’t restore compressed backups of encrypted databases
• High CPU usage when there are many batch requests (which we would expect?)
• SQL Server service crashes when you cancel CHECKDB (on a “large database” – doesn’t that apply to all? )
…. lots more

SQL 2008 and Windows Server 2008 end of support dates.

January 15th, 2019

Reminder:

End of Support for SQL Server 2008 and 2008 R2 is on July 9, 2019.
Windows Server 2008 and 2008 R2 is on January 14, 2020.

https://www.microsoft.com/en-us/cloud-platform/windows-sql-server-2008

https://info.microsoft.com/ww-ondemand-windows-server-2008-sql-server-2008-end-of-support.html

This means:
No further security updates
Compliance implications around standards and GDPR
Reduced performance
Less opportunity to innovate
May restrict ability to upgrade application software

Extended support is available at 20% of license cost.

This is a critical project to undertake, which is made more difficult by the tight timeframes and the need to build a plan that encompasses both Server and SQL upgrades. Each organisation has different, individual system architectures, strategy, budget , IT resources, and risk appetite.

Failure to update your platforms could result in compliance gaps or security attacks. The risks are too important to ignore. One in five organizations loses customers due to a cyberattack and nearly 30% lose revenue.1 4.2 billion records were stolen in 20162 and hackers aren’t going anywhere—the expected cost of cybercrime to the global economy by 2022 is $8 trillion.

The modern work landscape is changing. There is a need for massive data growth, powerful insights into unstructured data, and analytics that drive digital transformation. End of support provides a chance for you to not only revisit your IT strategy and to ensure your systems are modernized, and compliant.

Three options to consider:
1 – Upgrade to the latest versions of Windows Server and/or SQL now, either on-premises or deployed in private or public Cloud, either DIY or managed.
2 – Go ‘as a service’ and shift the responsibility, either moving workloads onto a VM within a managed Azure environment (IaaS), or transforming the workloads to platform-based services (PaaS) and going ‘serverless’.
3 – Move everything as is into the Azure environment where Microsoft will continue support for Server and SQL 2008 for a further three years at no cost which gives you more time to plan upgrade.

Consider whether now is an opportune time to migrate to azure and to take advantage of this special migration offer.

Extended security updates will be available for free in Azure for three more years after the End of support deadline for 2008 and 2008 R2 versions of SQL Server and Windows Server to help secure your workloads .

This means that Azure is an ideal place for older SQL and Windows servers.

Contact Synergy Software Systems on 009714 3365589 to understand the migration services we offer to help you manage the upgrade. (Microsoft partners for over 20 years in the U.A.E.).

Sql 2014 Sp2 Update 15

December 15th, 2018

The 15th cumulative update release for SQL Server 2014 SP2 is available for download at the Microsoft Downloads site.
Registration is no longer required to download Cumulative updates.

CU15 KB Article: https://support.microsoft.com/en-us/help/4469137

Microsoft® SQL Server® 2014 SP2 Latest Cumulative Update: https://www.microsoft.com/download/details.aspx?id=53592

Update Center for Microsoft SQL Server: http://technet.microsoft.com/en-US/sqlserver/ff803383.aspx

SQL 2014 SP3 now released

November 1st, 2018

Fixes include:

• Less TempDB contention
• Improved memory grant diagnostics using Extended Events
• Trace flags show up in query plans
• Wait stats, CPU time, elapsed time show up in query plans
• Query_hash and query_plan_hash data types now match between XE and DMVs
• Scalar UDF stats show up in query plans
• Row goals show up in…oh, you get the point
• Unified showplan schema for all supported versions
• MAXDOP hint for creating and updating statistics
• Faster restores on disks with 4K sectors

Why you should plan now to upgrade you SQL server

September 16th, 2018

Developments in software, hardware, and storage technology make the next twelve to eighteen months an ideal time to migrate from a legacy version of SQL Server to a modern version of SQL Server.

Consider that any version of SQL Server prior to SQL Server 2016 is already a legacy version of SQL Server.
- SQL Server 2014 will fall out of mainstream support on July 9, 2019
- (the same date that SQL Server 2008 and SQL Server 2008 R2 will fall out of extended support).
- SQL Server 2012 fell out of mainstream support on July 11, 2017.

Customers on those platforms, should be budgeting for what to do before the support expires.
Many new features were introduced at SQL 2016 and 2017. SQL Server 2017 is a clearly better upgrade choice than SQL Server 2016 right now. (SQL 2014 is best forgotten!)

Options:
Keep using the software and accept that it won’t receive any more security updates. This leaves you unprotected and is not a recommended course of action in the current era of malware, phasing attacks and GDPR compliance. Potentially the most expensive option ..and you will in any case need to upgrade sooner than later.
• Upgrade to newer software versions that are still supported . Update your on-prem SQL Server 2017 and Windows Server 2016 to get the necessary security, innovation, performance and efficiency. Resource intensive and maybe an expensive option.
Pay Microsoft for a custom support contract - If you have Software Assurance or Subscription licenses under an Enterprise Agreement, then get extended security updates for 3 years by paying 75% of the full license fee for SQL Server or Windows Server. Most expensive option.
Migrate your SQL 2008 workloads onto the Azure platform. Pay nothing for 3 years for support –maybe the least risky option • This choice gets you the necessary critical patches and allows you to keep data safe for >3 years. This give you the time and flexibility to prepare for a next move with SQL. Worth considering. Eligible customers can use Azure Hybrid Benefit and take advantage of existing on-premises licenses for Windows Server and SQL Server to save on Azure Virtual Machines (IaaS) or Azure SQL Database Managed Instance (PaaS) charges. Azure SQL Database reserved capacity is also available and enables you to save up to 33 percent when pre-paid SQL database vCores are taken for a one or three-year term.

Moving to the Cloud is a challenging project for many organisations. Consider booking our Cloud Migration workshop half day session to investigate and define a path for moving workloads, including SQL databases, into Azure. The workshop includes:
• Review of Azure Services.
• Identity the infrastructure required to get started.
• Review of existing workloads and migration paths.
• Administration, Maintenance and Controls.
• Security and Privacy.
• Developing a Cloud Adoption Roadmap.
• Planning a proof-of-concept to begin the journey.

SQL Server Developments

The modern versions of SQL Server are SQL Server 2016, SQL Server 2017, and the upcoming release of SQL Server

When the next version of SQL Server is released (perhaps in Q4 this year ) it is likely to have useful new features and enhancements that will make it a superior upgrade choice to SQL Server 2017. Regardless of new features, the next version of SQL Server will be in mainstream support for a longer period than SQL Server 2016 or SQL Server 2017.

Operating System Developments
Microsoft will release Windows Server 2019 sometime later in 2018. There are a number of improvements in Storage Spaces Direct (S2D), including deduplication and compression in ReFS:
- Another improvement is True Two-Node quorum for two-node S2D clusters using a USB thumb drive as a file share in a router.
- Windows Server 2019 S2D will let you have up to 4PB of raw storage capacity per S2D cluster, which is a 4X improvement over Windows Server 2016.
- There is a new PoSH cmdlet called Get-PhysicalDiskIoReport that lets you view much more granular performance information for individual physical disks, that allows you to manually monitor drive latency, and can be used to automatically detect drive latency outliers.
- Windows Server 2019 fully supports existing NV-DIMM persistent memory, along with Intel Optane 3D XPoint memory and storage. There are also improvements in the free Windows Admin Center management utility that is a great dashboard for hyper-converged infrastructure (HCI) deployments.
- VMware vSphere 6.7 supports vSphere Persistent Memory, which will let you take advantage of persistent memory in a VMware virtualized environment.

Hardware Developments
Both Intel and AMD are scheduled to release new generations of server processors during the next three to six months, both of which will work in existing model servers.
The Intel, 14nm Intel Xeon Scalable Processor “Cascade Lake-SP” is rumored to be due in Q4 of 2018. These processors will support “Apache Pass” DIMMs (meaning Intel Optane 3D XPoint persistent memory) and they are socket compatible with current Intel Xeon Scalable Processor “Skylake-SP” processors, which means that they will work in existing server models. More important for many customers is the fact that Cascade Lake-SP will have hardware-level protection for most side-channel attacks (such as Spectre/Meltdown), which will have less performance impact than software-based mitigation techniques. The Cascade Lake-SP processors will be followed by 14nm “Cooper Lake-SP” in 2019, and then 10nm “Ice Lake-SP” server processors in 2020.

Intel’s continued struggles with 10nm manufacturing are definitely going to hurt their competitive position compared to AMD in 2019/2020 so AMD stands to gain significant market share from Intel in the server space during this period.
AMD will have the 2nd generation, 7nm “Zen2” EPYC “Rome” family processors in early-mid 2019. These processors are supposed to be socket compatible with existing server models and have up to 48 cores/ 96 threads per processor. These processors are also rumored to have PCIe 4.0 support, which will give them double the bandwidth per lane compared to PCIe 3.0.

Looking further out, AMD is planning a3rd generation 7nm+ “Zen3” EPYC “Milan” family of processors sometime in 2020.

Storage Developments
Intel released its first 3D XPoint storage product, the 375GB Intel Optane DC P4800X SSD in Q1 2017. These use a PCIe 3.0 x4 link along with the NVMe protocol, and they have roughly 10X lower latency and 5-8X better throughput at low queue depths compared to the fastest PCIe NVMe NAND-based SSDs.
They also have 2-4X better write endurance than enterprise NAND-based SSDs.
These are relatively inexpensive and offer the fastest currently available type of traditional block mode storage. These are transparent to SQL Server and will work in any system that supports PCIe 3.0 x4 slots as HHHL add-in cards or U.2 connected drives.

Expected in the very near future the Intel 3D XPoint-based DIMMs (“Apache Pass”) that use a traditional low-latency DDR4 memory interface and form factor. These DIMMs will be available in 128GB, 256GB, and 512GB capacities, and they will fit in DDR4 memory slots. They will be addressable in a lower performance block mode that uses the entire storage stack, or a much higher performance direct access (DAX) mode that is byte addressable and bypasses the storage stack.

Both Windows 10 and Windows Server 2016 already have DAX support, and SQL Server 2016 SP1 has the persistent log buffer feature that lets you leverage a DAX storage volume that is built on persistent memory to create a small additional 20MB transaction log file that is used to greatly reduce latency writing to the transaction log. It seems probable that the next release of SQL Server will improve this feature.

Windows Server 2019 will have even better support for persistent memory. New two-socket servers with Intel Xeon “Cascade Lake-SP” processors will support up to 6TB of 3D XPoint DIMMs, which can be combined with traditional DDR4 memory in other memory slots.

SQL updates August 2018

August 16th, 2018

Microsoft has released a series of updates to SQL Server 2016 and 2017 to fix CVE-2018-8273:

– Executing a specially crafted query involving calculating difference between values of different date types and aggregation of the results, could lead to stack corruption, if the query runs in batch mode. Depending on particular values processed by such query, this could lead to terminating the SQL Server process, or a possibility of remote code execution.

- A buffer overflow vulnerability exists in the Microsoft SQL Server that could allow remote code execution on an affected system. An attacker who successfully exploited this vulnerability could execute code in the context of the SQL Server Database Engine service account…. The security update addresses the vulnerability by modifying how the Microsoft SQL Server Database Engine handles objects in memory.

The updates include:
•2017 CU9 GDR – 14.0.3035.2 – install this if you’re on the latest 2017, CU9
•2017 RTM GDR – 14.0.2000.63 – install this if you’re still on RTM
•2017 on Linux – 14.0.3035.2-1 and 14.0.2002.14 depending on your branch
•2016 SP2 CU2 GDR – 13.0.5161.0 – install this if you’re on the latest 2016, SP2 CU2
•2016 SP2 GDR – 13.0.5081.1 – install this if you’re still on SP2
•2016 SP1 CU10 GDR – 13.0.4522.0 – install this if you’re still on SP1 CU10
•2016 SP1 GDR – 13.0.4223.10 – install this if you’re still on SP1 with no CUs