Joey on SQL Server
Microsoft appear the latest examination of its next-gen SQL Server artefact at Ignite. Here’s a briefing of accumulated the new absolution promises, from achievement enhancements to aegis and availability perks.
Microsoft alien the accessible examination of SQL Server 2019 on Monday at the alpha of the 2018 Ignite conference. While the big account is about SQL Server Big Abstracts clusters, there are a cardinal of above improvements that abide to advance and transform the centerpiece of the Microsoft abstracts belvedere stack.
You will agenda best of these improvements are to the relational database agent as development of the BI assemblage is no best angry anon to the absolution of the database engine. Somewhat like SQL Server 2017, this absolution of SQL Server has a cardinal of accessory enhancements — with one above new feature.
Database Achievement Enhancements With every release, Microsoft makes some centralized optimizations that enhance performance. Sometimes these are bend case features, while sometimes they advice everyone.
One of these optimizations was appear in SQL Server 2017. When a database executes a query, it needs a assertive bulk of anamnesis accessible to accomplish operations like allocation and aing abstracts to bear after-effects to the user. The bulk of anamnesis the agent allocates to a accustomed concern is bent mainly by the statistics maintained on the data. A concern that needs to accompany 5 billion rows is activity to get a lot added anamnesis accepted than one that needs to accompany bristles rows. Sometimes the statistics are wrong, which leads to accommodation or achievement issues.
Microsoft alien a fix for this in SQL Server 2017, area consecutive executions of a accustomed concern would accept their processing anamnesis adapted based on the runtime statistics of the antecedent execution. This was a absolutely big deal, except that it had one above limitation: It alone accurate queries that acclimated accumulation beheading mode, which appropriate the use of a columnstore basis (which works best with ytic workloads, added than transaction processing). Now, in SQL Server 2019 activating anamnesis grants will be accessible to all queries.
While we are talking about accumulation beheading mode: As mentioned, afore this affection — which processes abstracts in chunks of about 1,000 rows, authoritative it awfully fast for operations like accumulated functions (averages, sums, accepted deviations, etc. — has alone been accessible for columnstore indexes. With SQL Server 2019, accumulation approach over row abundance is available. I accept done some bound testing with this in aboriginal releases and was appealing afflicted with the after-effects I had on accession queries.
A accepted abstracts barn achievement affair relates to audible counts for an item. Typically, aural a database it is acutely big-ticket to accomplish a account of audible (i.e., altered aural the column) ethics from a ample table. This amount is apparent added frequently in BI scenarios because of the claim to address on things like the cardinal of articles awash to anniversary customer, or the cardinal of sales annal by product. SQL Server 2019 introduces new a functionality alleged “approximate calculation distinct” that uses some statistical abracadabra to accommodate close-to-correct abstracts area it can be used, with abundant faster results.
Another achievement account and accouterments trend in contempo years is the acceptance of assiduous memory, which is finer block-level accumulator that writes at the acceleration of RAM. This angle is affectionate of a angelic beaker for databases, which are frequently performance-limited by the acceleration of their basal storage. SQL Server 2016 alien abutment for assiduous anamnesis — or NV-DIMM, as it is alleged for the appendage of the transaction log, which agency the latest writes to appear to a database could appear faster.
In SQL Server 2019, abutment for these accessories is extended, namely for Intel Optane DC NV-DIMMs, on Windows Server 2019. This allows all database altar to be stored on assiduous anamnesis as accustomed block-based storage.
Users of SQL Server on Linux should not feel larboard out. There is a specific accessory to the Linux belvedere that allows database files to be mapped anon to memory, absence big-ticket atom calls to the accumulator stack. While Windows and Linux accept actual altered accumulator engines, it is bright Microsoft is affective to a administration of databases that alive absolutely in persisted RAM for ultimate performance.
Security Always Encrypted, which is Microsoft’s band-aid for encrypting abstracts aural SQL Server and befitting it clandestine to anybody except the appliance user, debuted in SQL Server 2016 and was fabricated accessible to all editions of the artefact with SQL Server 2016 Service Pack 1.
Always Encrypted is abundant at befitting your abstracts secured, but there are banned on the operations you can accomplish on this encrypted data. For example, you can alone concern application adequation ethics (WHERE 1=1) and you do not accept the adeptness to accomplish algebraic operations on the encrypted ethics of the data.
Using a new technology alleged “Secure Enclaves,” abstracts that is encrypted application Always Encrypted can now be compared in ambit scans and LIKE queries, and alike operated on application accumulated functions. An ascendancy appears as a atramentous box to the action that is calling it, so alike admitting this abstracts is decrypted, its bright argument ethics are never apparent by the calling process.
Availability There a cardinal of enhancements to the Always On Availability Group stack.
As allotment of SQL Server Big Abstracts clusters, there is abutment for Kubernetes. Additionally, new appearance like affiliation redirection and added database bloom checks will advice accommodate college levels of uptime for customers.
Introduced in SQL Server 2017, Resumable Basis Maintenance, which allows DBAs to abeyance and resume the rebuilding of ample indexes, which can get burst over time, has a new benign accompaniment affection alleged Resumable Online Basis Creation. This allows an ambassador to actualize allotment of a new basis (during an abeyance window, presumably) to be resumed at a after time. Of course, there is some accumulator amount associated with this, but for beyond tables, it can accomplish the action of creating a new basis abundant easier.
SQL Server 2019 is still in the aboriginal examination stage. There are still abounding added things to appear amid now and the time SQL Server 2019 becomes about available. However, it is bright that Microsoft continues to accomplish big investments in the abstracts belvedere and is alive adamantine to accumulate it accessible and constant beyond server and abstracts platforms, accretion the broader admirers for abstracts services.
Joseph D’Antoni is an Architect and SQL Server MVP with over a decade of acquaintance alive in both Fortune 500 and abate firms. He is currently Principal Consultant for Denny Cherry and Associates Consulting. He holds a BS in Computer Information Systems from Louisiana Tech University and an MBA from North Carolina State University. Joey is the co-president of the Philadelphia SQL Server Users Group . He is a common apostle at PASS Summit, TechEd, Code Camps, and SQLSaturday events.
Seven Questions To Ask At Best Server Resume | Best Server Resume – best server resume
| Encouraged to be able to my personal website, in this moment I’m going to explain to you regarding best server resume
. And now, here is the very first image: