Archive for the ‘SQL’ category

6-Month Extension for aging versions of Microsoft end-of-support dates.

April 23rd, 2020

When Microsoft products reach their end-of-support dates (known as the end of “extended support”), they continue to function, but Microsoft stops issuing free patches. Security updates stop arriving, which could pose problems for organizations. In response to the “current public health situation,”. Microsoft announced this week that support deadlines that were due in May now fall toward year’s end.

Microsoft’s announcement on extending the Dynamics 365 Customer Engagement legacy Web client by two months also noted that Microsoft will be “simplifying the ability for Dynamics 365 Finance, Supply Chain Management, and Commerce customers to pause updates for an extended period.” However, the document just states that subscribers can pause for “up to three consecutive updates,” without further elaboration.

Microsoft announced earlier this month that it was delaying ending support for the older Transport Layer Security protocols versions 1.0 and 1.1, until the second half of 2020. The switch will have an affect on Microsoft’s browsers and Office 365 products.

Office 2010 Support Still Ends in October
Microsoft issued a reminder that Office 2010 for Windows and Office 2016 for Mac both will reach their end-of-support date on Oct. 13, 2020. They aren’t getting extensions. These Office users face an additional problem, namely that Microsoft plans to end the connections of those products with various Office 365 services on that same Oct. 13, 2020 date. Users should shift to newer productivity-suite products, Microsoft advised.

Microsoft’s announcement:

Also, as previously announced, after October 13, 2020, customers will need to have Office 365 ProPlus,[1] Office 2019 or Office 2016 to connect to Office 365 services. Microsoft will not take any active measures to block legacy versions of the Office client from connecting to Office 365 services, but these older clients may encounter performance and/or reliability issues over time. For related Office client support timelines, see the Office support dates matrix.

Microsoft is planning to rename Office 365 ProPlus as “Microsoft 365 Apps for enterprise” on April 21. This along with other Office 365 name changes, were announced last month.

Synergy Support during covid-19 lockdown

April 6th, 2020

As a precautionary measure instructed by our Government for COVID-19 to be Safe at Home to protect our staff and to protect the community Synergy, consultants are instructed to work from home for at east the next two weeks.

To help us to provide continuous support please follow these guidelines to ensure our queries are recorded and assigned to be addressed in addressed as soon as possible.:

1. Send an email with a clear description in the email Subject line”, which will be helpful to track the email chain.
2. Please mention any internal issue/ticket number assigned, the user, and as much detail as possible e.g. transaction detail such as: order number, vendor code, item code – take a screenshot – copy any error message and attach those details to the email. Better still record the steps e.g. with e.g. webex, or task recorder.
3. If you are not on your work telephone number, or email, e,g when working from home yourself, then ensure you provide contact information for us to reach you.
4. To understand the issue, we may need to connect to the user PC through a screen sharing app such as Microsoft TEAMS, or Go To Meeting. Please ask your admin take appropriate action to ensure we can dial in remotely to your systems if needed, Even when located elsewhere they will also able to join such sessions.
5. Every request is given a ticket number and is then assigned to a consultant. For follow up communication please mention the ticket number. That will make it easier for us to find and review the details and actions to date. It’s possible that more than one consultant may be involved and they will be working remote from each other. This will save time for everyone .
6. Once the support request/ issue is resolved, upon receipt of confirmation email, the request will be closed.
7. When needed to discuss pending issues we will also be available to have a conference call via Microsoft Teams. issues @ mutual agreed time.
6. All support request to be sent to Axapta.support@synergy-software.com and we suggest copy in the lead consultant and account manager with whom you normally deal’
7. Please circulate this information to respective users and department heads, so that everyone working from home is aware that we are still available to support you as best we can and that they know how to help us, to help them.

SQL Server 2016 SP2 Cumulative Update 12

April 1st, 2020

SQL Server 2016 SP2 Cumulative Update 12 contains some useful fixes:

SQL Server can shut down when you hit the max number of sessions
SQL Server can shut down due to lock conflicts during error message processing
“SQL Server crashes frequently” when you check a clustered columnstore index for corruption
AGs may have “interruption” – I guess that means the AG stops replicating, but it’s not clear from the KB article.
Stack dumps when transaction replication has a heavy workload on the publication database
Stack dumps when you query persisted computed columns
Stack dumps when you run a batch mode query with multiple joins (that’s columnstore indexes in 2016)
Scalar functions run slower than they did on SQL Server 2008 R2
Non-yielding scheduler when the primary AG replica runs low on memory
AG may think there’s a missing log block when the database isn’t very active
AG automatic seeding may fail
AGs with persistent log buffers: “all of the secondaries in the AG become unavailable”
Change tracking auto cleanup causes access violations and stack dumps
Access violations when Extended Events tries to capture query text on busy servers
Error when stored proc in database A pulls data from database B while being audited in database C
Stack dumps when you alter database-scoped configurations
Incorrect statistics histograms when they’re updated in parallel – which also means that after you apply this CU, you should probably update your statistics.
There’s also a new feature: the default system health Extended Events session can store way more data now, AND you can edit how much it holds!

SQL Server 2016 SP2 Cu12 is available

March 1st, 2020

There’s a new feature: the default system health Extended Events session can now store a lot more data and you can edit how much it should hold.

Several hotfixes
• SQL Server can shut down when you hit the max number of sessions
• SQL Server can shut down due to lock conflicts during error message processing
• “SQL Server crashes frequently” when you check a clustered columnstore index for corruption
• AGs may have “interruption”
• Stack dumps when transaction replication has a heavy workload on the publication database
• Stack dumps when you query persisted computed columns
• Stack dumps when you run a batch mode query with multiple joins (that’s columnstore indexes in 2016)
• Scalar functions run slower than they did on SQL Server 2008 R2
• Non-yielding scheduler when the primary AG replica runs low on memory
• AG may think there’s a missing log block when the database isn’t very active
• AG automatic seeding may fail
• AGs with persistent log buffers: “all of the secondaries in the AG become unavailable”
• Change tracking auto cleanup causes access violations and stack dumps
• Access violations when Extended Events tries to capture query text on busy servers
• Error when stored proc in database A pulls data from database B while being audited in database C
• Stack dumps when you alter database-scoped configurations
• Incorrect statistics histograms when they’re updated in parallel – which also means that after you apply this CU, you should probably update your statistics.

Cumulative updates (CU) are now available at the Microsoft Download Center.

Only the most recent CU that was released for SQL Server 2016 SP2 is available at the Download Center. Each new CU contains all the fixes that were included togetrher with the previous CU for the installed version or service pack of SQL Server.
•Microsoft recommends ongoing, proactive installation of CUs as they become available: •SQL Server CUs are certified to the same levels as Service Packs, and should be installed at the same level of confidence.
•Historical data shows that a significant number of support cases involve an issue that has already been addressed in a released CU.
•CUs may contain added value over and above hotfixes. This includes supportability, manageability, and reliability updates.

•Just as for SQL Server service packs, we recommend that you test CUs before you deploy them to production environments.
•We recommend that you upgrade your SQL Server installation to the latest SQL Server 2016 service pack.

End of support deadlines -Microsoft

January 15th, 2020

Businesses running Microsoft’s business software are facing all sorts of end-of-support deadlines at the start of 2020.

Major premises-installed business products are falling out of “extended support” this year, which means they’ll no longer get patches, including security updates, from Microsoft. It’s considered potentially risky to continue to use such “unsupported software” after their end-of-support milestones.

IT pros may have already reacted to address many of these milestones, but some workloads remain as problems to address.
Crashing deadlines are very near this month for organizations using Windows 7 and Windows Server 2008, but other important milestones loom, as well.
Windows 7 Client and Server Deadlines – Jan. 14, 2020 end-of-support date for Windows 7. That support deadline also applies to Windows Server 2008/R2, Dynamics CRM 2015, Dynamics GP 2015 R2, Dynamics NAV 2015,Dynamics SL 2015.
Later this year Office 2016 , and Outlook 2016 versions will also go out of support.

SQL 2016 Sp2 CU11 release

December 15th, 2019

The 11th cumulative update release for SQL Server 2016 SP2 is now available for download at the Microsoft Downloads site.
Please note that registration is no longer required to download Cumulative updates.
CU11 KB Article: https://support.microsoft.com/en-us/help/4527378
• Microsoft® SQL Server® 2016 SP2 Latest Cumulative Update: https://www.microsoft.com/download/details.aspx?id=56975
• Update Center for Microsoft SQL Server: http://technet.microsoft.com/en-US/sqlserver/ff803383.aspx

Windows Server 2008 and 2008 R2 support will end January 14, 2020- ask Synergy Software Systems about options.

November 16th, 2019

On January 14, 2020, support for Windows Server 2008 and 2008 R2 will end. Only 2 months away
That means the end of regular security updates.

Don’t let your infrastructure and applications go unprotected.

We’re here to help you migrate to current versions for greater security, performance and innovation.
009714 3365589

Azure Arc in preview manage hybrid data across cloud platforms……

November 16th, 2019

Now in preview, Azure Arc helps simplify enterprise distributed environments by managing everything via Azure services (like Azure Resource Manager). Connecting hybrid infrastructure via Azure Arc improves security for users via automated patching, and provides improved governance, with everything ‘under one roof’. Azure Arc, a tool that lets organizations manage their data on: the Microsoft Azure cloud, Amazon Web Services (AWS), Google Cloud Platform or any combination.

Microsoft says that deployments can be set up “in seconds” via Azure data services anywhere, a feature of Azure Arc.

Azure Arc also supports Kubernetes clusters and edge infrastructures, as well as on-premises Windows and Linux servers.
No final release date yet but there is a free preview of Azure Arc .


Enhanced HA and DR benefits for SQL Server Sofware Assurance from 1 November.

November 5th, 2019

The enhanced benefits to SQL licensing for high availability and disaster recovery that are listed below are now applicable to all releases of SQL Server for a customer with SQL Server licenses with Software Assurance. The updated benefits will be available in the next refresh of the Microsoft Licensing Terms.

Business continuity is a key requirement for planning, designing, and implementing any business-critical system. When you bring data into the mix, business continuity becomes mandatory. It’s an insurance policy that one hopes they never have to make a claim against in the foreseeable future. SQL Server brings intelligent performance, availability, and security to Windows, Linux, and containers and can tackle any data workload from BI to AI from online transaction processing (OLTP) to data warehousing. You get mission-critical high availability and disaster recovery features that allow you to implement various topologies to meet your business SLAs.

A customer with SQL Server licenses with Software Assurance has historically benefited from a free passive instance of SQL Server for their high availability configurations. That helps to lower the total cost of ownership (TCO) of an application using SQL Server. Today, this is enhanced for the existing Software Assurance benefits for SQL Server which further helps customers implement a holistic business continuity plan with SQL Server.

Starting Nov 1st, every Software Assurance customer of SQL Server will be able to use three enhanced benefits for any SQL Server release that is still supported by Microsoft:
• Failover servers for high availability – Allows customers to install and run passive SQL Server instances in a separate operating system environment (OSE) or server for high availability on-premises in anticipation of a failover event. Today, Software Assurance customers have one free passive instance for either high availability or DR
• Failover servers for disaster recovery NEW – Allows customers to install and run passive SQL Server instances in a separate OSE or server on-premises for disaster recovery in anticipation of a failover event
• Failover servers for disaster recovery in Azure NEW – Allows customers to install and run passive SQL Server instances in a separate OSE or server for disaster recovery in Azure in anticipation of a failover event

With these new benefits, Software Assurance customers can implement hybrid disaster recovery plans with SQL Server using features like Always On Availability Groups without incurring additional licensing costs for the passive replicas.

A setup can use SQL Server running on an Azure Virtual Machine that utilizes 12 cores as a disaster recovery replica for an on-premises SQL Server deployment using 12 cores. In the past, you would need to license 12 cores of SQL Server for the on-premises and the Azure Virtual Machine deployment. The new benefit offers passive replica benefits running on an Azure Virtual Machine. Now a customer need to only license 12 cores of SQL Server running on-premises as long as the disaster recovery criteria for the passive replica on Azure Virtual Machine is met.

If, the primary. or the active replica. uses 12 cores hosting two virtual machines and the topology has two secondary replicas: one sync replica for high availability supporting automatic failovers and one asynchronous replica for disaster recovery without automatic failover then . the number of SQL Server core licenses required to operate this topology will be only 12 cores as opposed to 24 cores in the past.

These high availability and disaster recovery benefits will be applicable to all releases of SQL Server. In addition to the high availability and disaster recovery benefits, the following operations are allowed on the passive replicas:
• Database consistency checks
• Log backups
• Full backups
• Monitoring resource usage data

SQL Server 2019 also provides a number of improvements for availability, performance, and security along with new capabilities like the integration of HDFS and Apache Spark™ with the SQL Server database engine.

SQL Server 2016 SP2 CU10 – (it fixes CU 9)

October 10th, 2019

SQL Server 2016 SP2 CU10 is essentially a fixed version of CU9.

Microsoft added a note to the KB article to holdoff on CU9, and now…that CU9 KB article page has vanished altogether.

So CU9 is dead to Microsoft, Everything we said about CU9, is replaced by CU10.

SQL Server 2016 Service Pack 2 SP2 CU9 release

October 1st, 2019

Cumulative Update package 9 (CU9) (build number: 13.0.5470.0) for Microsoft SQL Server 2016 Service Pack 2 SP2 is now aavilablefor download. (It contains fixes that were released after the initial release of SQL Server 2016 SP2.)
SQL Server CUs are certified to the same levels as Service Packs, and should be installed at the same level of confidence. Historical data shows that a significant number of support cases involve an issue that has already been addressed in a released CU.

The CU provides the following fixes and improvements (Referenced from https://support.microsoft.com/en-us/help/4100997/cumulative-update-9-for-sql-server-2016-sp1)

KB4099472 – PFS page round robin algorithm improvement in SQL Server 2016 SQL service
KB4133164 – FIX: Error when a SQL Server Agent job executes a PowerShell command to enumerate permissions of the database Management Tools
KB4086173 – FIX: Access violation occurs when executing a DAX query on a tabular model in SQL Server Analysis Services Analysis Services
KB4131193 – Performance issues occur in the form of PAGELATCH_EX and PAGELATCH_SH waits in TempDB when you use SQL Server 2016 SQL service
KB3028216 – FIX: A crash occurs when proactive caching is triggered for a dimension in SSAS Analysis Services
KB4135113 – FIX: Change tracking record is inconsistent during an update on a table which has a cluster/unique index in SQL Server SQL service
KB4293839 – FIX: TDE database goes offline during log flush operations when connectivity issues cause the EKM provider to become inaccessible in SQL Server SQL security
KB4230730 – FIX: A dead latch condition occurs when you perform an online index rebuild or execute a merge command in SQL Server SQL service
KB4163478 – FIX: An access violation occurs when incremental statistics are automatically updated on a table in SQL Server SQL performance
KB4230306 – FIX: Restore of a TDE compressed backup is unsuccessful when using the VDI client SQL service
KB4163087 – FIX: Performance is slow for an Always On AG when you process a read query in SQL Server SQL service
KB4164562 – FIX: Wrong user name appears when two users log on to MDS at different times in SQL Server Data Quality Services (DQS)
KB4094893 – FIX: Database cannot be dropped after its storage is disconnected and reconnected in SQL Server SQL service
KB4162814 – FIX: An internal exception access violation occurs and the SSAS server stops responding Analysis Services
KB4134541 – FIX: Error in the MDS Add-in for Excel when you use the German version of Excel in SQL Server Data Quality Services (DQS)
KB4132267 – FIX: Deploying a SSAS project in SSDT is frequently unsuccessful in SQL Server Analysis Services in Tabular mode Analysis Services
KB4101554 – FIX: Parallel redo in a secondary replica of an availability group that contains heap tables generates a runtime assert dump or the SQL Server crashes with an access violation error High Availability
KB4098762 – FIX: Hidden parameters are included in reports when the Browser role is used in SSRS 2016 Reporting Services
KB4134175 – FIX: Processing a cube with many partitions generates lots of concurrent data source connections in SSAS Analysis Services
KB4091245 – FIX: Access violation occurs when you query a table with an integer column in SQL Server 2017 and SQL Server 2016 SQL performance
KB4094706 – FIX: One worker thread seems to hang after another worker thread is aborted when you run a parallel query in SQL Server SQL service
KB4058175 – FIX: TDE enabled database backup and restore operations are slow when the encryption key is stored in an EKM provider in SQL Server SQL service
KB4131960 – FIX: An access violation occurs when you execute a nested select query against a columnstore index in SQL Server SQL Engine
KB4094858 – FIX: “An unexpected error occurred” when you use DAX measures in Power BI table visualizations in SQL Server Analysis Services
KB4101502 – FIX: TDE enabled database backup with compression causes database corruption in SQL Server 2016 SQL service

CUs also often include supportability, manageability, and reliability updates.

Before udpate:

- Check compaitbiltiy with your application.
- Test CUs before you deploy to production environments.

SQL Server 2016SP2 Cumulative Update 8

August 3rd, 2019

The urgent security update earlier this month is not the only patch for SQL Server 2016 in July,
Microsoft has released SQL. SP2 CU8 (build number: 13.0.5426.0)
• Restores of compressed encrypted backups fail
• Data masking doesn’t
• DAXquery needs memory 200x larger than the database size
• Peer-to-peer replication fails when your host name isn’t uppercase
• QueryStore cleanup can fill the transaction log and cause an outage
•DistributedAvailability Groups cause memory dumps when automatic seeding
• AGreplication stops working due to internal thread deadlocks
•The deadlock monitor can cause an access violation
• Query a view with a union on a linked server,
• Concurrent inserts into a clustered columnstore index can deadlock
•Infiniteloop when FileTable is used for a long time without a restart
•SSAS2016 randomly crashes ( maybe not completely random if they fixed it)
•TransparentData Encryption doesn’t encrypt if it’s restarted mid-encryption

And much more.https://support.microsoft.com/en-us/help/4505830/cumulative-update-8-for-sql-server-2016-sp2

I guess we will get a similar patch for Sp1 but by now you should be on a later patch

SQL Server 2014 Sp3 mainstream support ended on July 9 – CU4 just released

July 31st, 2019

However Cumulative Update 4 for SQL Server 2014 SP3 has just been released.

https://support.microsoft.com/en-us/help/4500181/cumulative-update-4-for-sql-server-2014-sp3

SQL Server 2008 and SQL Server 2008 R2 -OUT OF SUPPORT today

July 13th, 2019

SQL Server 2008 and 2008 R2, both of these versions of SQL server go out of extended support with Microsoft today 9th July 2019

Many companies and businesses are still SQL Server 2008 R2 and below. There can be a number of reasons for this, maybe the applications the databases support require an older version of SQL Server, maybe the applications are also coming to the end of life, but the end dates do not match up with the data platform end of support dates.

Sometimes applications are critical to the business and everything works just fine. The business doesn’t want to disrupt the application or introduce any risk by performing a migration to a new version so why change it?

In this situation your data platform is out of support completely. Out of support system attract hackers. Note the previous articles about fines for loss of privacy data to realise how serious this can be

So you should be making plans to migrate your legacy SQL Servers off the unsupported versions. It is likely if you are still on an old database that you are also on an old server and on an old version of Windows. That gives additional risk of failed hard disks, other system vulnerabilities – Meltdown, Spectre? Phishing…….
Investors and insurers are not likely to be sympathetic in such circumstances.

There are many performance and security benefits of upgrade.

If you decide to run on out support software and take the risk associated with running on out of support software. The main advantage of this approach is there is nothing immediate to do. The longer you run on the platform the greater the chances of you encountering a security vulnerability or failing a compliance test.
If anything does go wrong you’ll have no support from Microsoft.
Other software vendors support contracts may also require that you be on a currently supported database

Modernise and upgrade is one of the options that you have available.

You can upgrade your on premises SQL Server or migrate the databases to Azure either as IaaS solution where you run the VM in Azure or even the PaaS Azure SQL database offering

There are number of advantages to upgrading your data platform. You’ll be running your database workloads on an in support data platform, with a long support window. There will likely by new features in the latest and greatest version of SQL Server that you can use to add business value to your application – Availability Groups for example. Also you will likely find people with skills in the later technology, those skills will be more readily available in the jobs market.

There will likely be a different licensing model – the licensing model changed between SQL Server 2008 R2 and SQL Server 2012 – it possible you will have to pay more for you SQL Server licences.

The third option is instead of doing nothing you pay for a custom support agreement. The main advantage here is you can continue to get security updates and therefore potentially remaining compliant. The main disadvantage of this approach is the cost involved, which is typically 75% of the full license costs of the latest version of SQL Server and Windows Server.

Migrate workload to Azure. Microsoft allow SQL Server 2008 and SQL Server R2 VMs running in Azure to have the security updates for free for a further 3 years. So you can migrate your database server to azure and continue to get security updates for free until 2022.

The main advantage of this is you get to keep running the same version of the OS and Data platform, the security updates are free so the cost is minimal \. The disadvantages is you would need to move off premises, if this is not an option for you then you can’t exercise this option and there will still be work in involved in ‘lifting and shifting’ the VM to the cloud.

Whatever you do when support ends for SQL Server 2008 and SQL Server 2008 R2 have a plan

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