Archive for the ‘SQL’ category

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

Microsoft Ignite agenda insights to the future road map

August 14th, 2018

Microsoft recently published the session list for its annual Ignite IT Pro conference happening at the end of the September. Alook at the topcis gives a clue to its roadmap. There sessionson on the next version of SQL Server. Surface Hub 2 and Surface Go with LTE, Intune and Windows Autopilot, Windows Server 2019. New Remote Desktop services.

Last year, Microsoft used Ignite to highlight AI, intelligent edge and its futuristic quantum-computing technologies but overall the listed sessions, look more down to earth. There are two mixed-reality sessions — including “Visio Immersive,” Almost 100 listed sessions touch on AI . At Inspire Microsoft told partners the “AI Accelerate Kit”would be coming in October and include AI use cases, best practices and “Ethical AI” guidance so that seems lilley to be included.

At Ignite Microsoft will again focus on Microsoft 365,- the bundle of Windows 10, Office 365 and Intune security/management technologies.

Expect to a lot of Dynamics 365 CRM and ERP content — because October is when the next feature update will arrive for the suite of Dynamics products.

There seems to be more developer content: . ASP.NET, Visual Studio Code and Visual Studio 2017, Node.js, and sessions on linux and Docket containers, Progressive Web Apps and MSIX, the new Windows 10 application-packaging technology Microsoft is rolling out.

There are 115 sessions listed for SQL Server /Azure SQL. Mayeb we will get an insight into the successor to SQL Server 2017 — codenamed “Aris,” which is currently in private Community Technology Preview testing.

Microsoft wil lalso show the new the Surface Hub 2 and Surface Go.

Expect Windows Server 2019, Microsoft’s next major release of Windows Server, to be a hot topic -it’s due to start roll out before year end.

https://www.microsoft.com/en-us/ignite

https://www.microsoft.com/en-us/ignite/faq

September 24–28, 2018 | Orlando, Florida

SQL Server 2016 SP2 CU2, SP1 CU10

July 18th, 2018

Fixes and improvements:
• DAG improvement – automatically seed replicas – when you add a database to an existing AG, SQL Server can automatically seed it across the secondary replicas. .
• AGs – configurable session_timeouts
• AGs – slow transactions with 1 sync and 1 async secondary
• AGs – on cross-data-center AG failover, you get a non-yielding scheduler and a crash
• AGs – queries on secondary take twice as long
• AGs – VSS backups fail on secondary replicas in a Basic Availability Group (which technically you’re not supposed to do, but you can still back up the entire secondary VM, and that’s where the problem looks like it’s coming in)
• AGs – fixing error 19432 for duplicate log blocks
• Log shipping – add support for Transparent Data Encryption by configuring MAXTRANSFERSIZE.
• Dynamic data masking doesn’t
• SSAS crashes when Process Full follows Process Clear –“you will notice that the SSAS may crash.” .
• Memory dump when you merge partitioned temporal tables .
• Stats updates can get a “corrupted index” message and a disconnect
• Assertion error when you add a database
• Slow performance when Query Store is enabled
• Non-yielding schedulers require a reboot – not the most informative KB article ever. “Assume that you have a Microsoft SQL Server 2016 installed.” .

See KB articles for more information . Download SQL 2016 SP2 CU2 and/or SP1 CU10.

https://support.microsoft.com/en-us/help/4341569/cumulative-update-10-for-sql-server-2016-sp1

End of life for SQL 2008 and 2008 r2 is only a year away

July 14th, 2018

On July 9, 2019, Microsoft will end Extended Support, for SQL Server 2008 and 2008 R2hich means no more updates or support of any kind, potentially leaving you vulnerable to security and compliance issues.
Some considerations:
That is only a year away. So time to start planning and to get it into your 2019 budget.
What applications are affected? With what new SQL version are they compatible?
Will you need to rebuy licenses? The SQL license cost is now core based and it might prove lot higher than last time so take the time to consider all options.
Should any of your applications move to the cloud?
Should you also look at upgrades to Hardware? Windows, Office, Exchange, or Business finance/erp systems in conjunction with SQL?
Is now the time to review your security solutions?
Are you going to expand, or implement heavy new processes like consolidation, budgeting, BI in then next 2-3 years?
Is your mobile network growing?

There are major enhancements at QL 2016 sp1 so we recommend you should not consider any version lower than that. By next year SQL 2017 will also have settled down.

To discuss options callus o 0097143365589

SQL training in Arabic – free on line course

June 26th, 2018

A free video course presented in Arabic to help you learn about SQL Server 2012. The course covers a number of concepts important to database professionals such as installation, configuration, storage, backup, security and high availability/disaster recovery concepts. Ayman El-Ghazali narrates the course in the Arabic language and demonstrates the skills so you can follow along on your own instance. You can find the videos and more information here.

https://thesqlpro.com/ArabicSQL/