Archive
SQL Server 2022 preview announced, some awesome new features !!!
SQL Server 2022 is coming !!!
On 2nd November 2021 in MS Ignite 2021 event Microsoft announced the preview of new version of SQL Server i.e. SQL Server 2022.
SQL Server 2022 will be more focused on Azure-enabled cloud and big data space, here are some excerpts from the announcements:
1. Azure Synapse link integration with SQL for ETL free and real time reporting & analytics: Here you can setup a Synapse Link relationship between SQL Pool in Azure Synapse and SQL Server as a data source and link required tables in Synapse Workspace. You can create PowerBI reports on top of these tables available in Synapse Workspace with real time data, without requiring you to write/setup an ETL from SQL Server to Synapse SQL Pool. This will also let you combine/JOIN datasets from different sources, data lake, files, etc. Similarly Data Scientists can create ML Models by using Synapse Spark Pools with PySpark language.
2. Azure Purview integration: with also work with SQL Server, for data discovery and data governance. Here you can use Purview to scan and capture SQL Server metadata for data catalog, classify the data (PII, non-PII, etc), and even control access rights on SQL Server.
3. Bi-directional HA/DR between SQL Server and Azure SQL MI: Now you can use Azure SQL Managed Instance as a Disaster Recovery site for your SQL Server workloads. You can setup Disaster Recovery as a Managed Service with Azure SQL Managed Instance (SQL MI). A Distributed Availability Group is created automatically with a new Azure SQL MI as READ_ONLY replica. This way you you can do failover from SQL Server to Azure SQL MI, and also back from Azure SQL MI to SQL Server.
4. QueryStore and IQP enhancements: Parameter Sensitive Plan optimization for handling Parameter Sniffing issues. Now the SQL optimizer will cache multiple plans for a Stored Procedure which uses different parameter values having big gap in cardinality providing consistent query performance. Enhancements on Cardinality-Estimation & Max-DOP feedback, and support for read replicas from Availability Groups.
5. SQL Server Ledger: Built on Blockchain technology, this feature will allow you to create and setup smart contracts on SQL Server itself. With ledger based immutable record of data you can make sure the data modified over time is not tampered with, and will be helpful in Banking, Retail, e-commerce, Supply Chain and many more industries.
6. Scalable SQL Engine and new features coming:
– Buffer Pool parallel scan
– Improvements on tempDB latch contention (System page GAM/SGAM concurrency)
– In-Memory (Hekaton) OLTP enhancements
– Multi-write replication with last writer wins
– Redesigned and improved SPANSHOT backups
– Polybase (REST API) for connecting to various files formats (parquet, JSON, csv) in Data lake, Delta tables, ADLS, S3, etc.
– Backup/Restore to S3 storage
– JSON enhancements and new functions
– New Time series T-SQL functions
For more details on SQL Server 2022 please check here” Microsoft official SQL Server Blog.
Database backup to Azure blob storage with SQL Server 2016+
Backup to Azure was made available with SQL Server 2012 SP1 CU2. It provided significant cost savings versus on-premises costs of onsite/offsite storage, and device maintenance and better scalability than logical drives connected to Azure machines.
But backup to Azure was comparatively slow, and the maximum backup size was 1 TB, till SQL Server 2014.
Now with SQL Server 2016+ backup to block blobs offers more cost-effective storage, performance increases with backup striping and a faster restore process, support for larger backups, up to 12 TB, as well as granular access and a unified credential story.
Backup to block blobs also supports all of the existing backup and restore features, with the exception that appends are not supported.
For detailed information please check the Quickstart guide to SQL backup and restore to Azure Blob storage service
SQL Server 2019 released, awesome new features – download now !!!
Today on 4th November 2019 Microsoft in MSIgnite2019 event announced the release of new version of SQL Server i.e. SQL Server 2019.
New stuff in SQL Server 2019 is all about Big Data Clusters for SQL Server, which will allow you to:
– Deploy scalable clusters of SQL Server, Spark, HDFS on Kubernetes
– Read, write, and process big data from Transact-SQL or Spark
– With Polybase query data from external SQL Server, Oracle, Teradata, MongoDB, and ODBC data sources with external tables
– and many more, we will check below…
–> Download SQL Server (evaluation version):
To download SQL Server 2019 you can Register and Download the Full or Evaluation version (180 days) here.
Or you can directly download the installer SQL2019-SSEI-Eval.exe
–> Free Developer Version:
Back in March 2016 Microsoft announced that going forward the Developer version of SQL Server any release will be free for Developers and Learning purpose. Register and Download the Developer version.
This Developer version is meant for development and testing only, and not for production environments or for use with production data. For more info please check my previous blog post.
–> Download SSMS (separate install):
Microsoft starting with SQL Server 2016 decoupled SSMS from SQL Server setup and is available as a separate installer. This is basically to support the move to make a universal version of SSMS for both SQL Server on-Prem & Azure SQL Database, that will ship every month or so. The SSMS setup is available separately as free download.
–> Download SSRS (Reporting Services):
Just like SSMS, now SSRS is also separately available for install from Microsoft Download Center, link.
–> Check new features in SQL Server 2019:
1. Big data clusters with scalable compute and storage composed of SQL Server, Spark, and HDFS. It provides key elements of a data lake – Hadoop Distributed File System (HDFS), Spark, and analytics tools deeply integrated with SQL Server. [more info]
2. A complete Data Science & AI platform to train and operationalize models in SQL Server ML Services or Spark ML using Azure Data Studio notebooks.
3. Data virtualization allows queries across relational and non-relational data without movement or replication. PolyBase enabled you to run a T-SQL query inside SQL Server to pull data from Hadoop and return it in a structured format—all without moving or copying the data.
4. Intelligent Query Processing improves scaling of queries and Automatic Plan Correction resolves performance problems. [more info]
– Table variable deferred compilation
– Batch mode on row store
– T-SQL Scalar UDF inlining
– Approximate QP (Approximate COUNT DISTINCT)
– Memory grant feedback, row mode
5. In-memory improvements for analytics on operational data using HTAP. Higher concurrency and scale through persistent memory (PMEM). [more info]
– Hybrid buffer pool
– Memory-optimized TempDB
– In-Memory OLTP support for Database Snapshots
6. Greater uptime with more online indexing operations. [more info]
7. Data Discovery & Classification labeling for GDPR and Vulnerability Assessment tool to track compliance.
8. Support for your choice of Windows, Linux, and containers. SQL Server on Linux for Docker Engine [link].
9. High Availability with, five synchronous replica pairs, Secondary to primary replica connection redirection, Run Always On availability groups on containers using Kubernetes. [more info]
10. Accelerated Database Recovery [more info]
11. SQL Graph enhancements, support of MATCH predicates in a MERGE statement, SHORTEST_PATH inside MATCH, and support for derived tables or view aliases in graph match query.
Check all the above and many more new features of SQL Server 2019 in MSDN Blogs.
SQL Server 2019 initial “Community Technology Preview” is out – download and check the CTP 2.0 release now !
Today on 24th September 2018 Microsoft announced the released of first CTP version of SQL Server vNext i.e. SQL Server 2019 CTP 2.0.
SQL Server 2016 & 2017 brought Machine Learning feature by adding R and Python support for Data Analytics and Scientists.
And now SQL Server 2019 has added Big Data capabilities under a unified platform by adding support for Apache Spark and HDFS, providing support for managing and querying unstructured data with structured/relational data.
Thus giving you an out-of-box end-to-end hybrid data platform with different types of workloads like OLTP, OLAP, DW/BI, Advance Analytics, Big Data and ML & AI on your data.
And just like the previous SQL Server 2017 the new SQL Server 2019 version will also run both on Windows & Linux.
Download SQL Server 2019 CTP bits:
To download the SQL Server 2019 you can Register and Download the Free evaluation version (180 days).
Or, directly download the web installer EXE (~3.7 MB): SQLServerVNext-SSEI-Eval.exe
Check version and SQL build:
select @@version
Microsoft SQL Server vNext (CTP2.0) – 15.0.1000.34 (X64)
Sep 18 2018 02:32:04
Copyright (C) 2018 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 16299: ) (Hypervisor)
New Features & Enhancements:
1. Deploy Big Data clusters with SQL Server & Spark by leveraging Polybase [more info]
2. Build and rebuild CCI online (Clustered ColumnStore Indexes)
3. Resumable online index create operation to pause and resume later from where the operation was paused or failed, instead of restarting from the beginning.
4. Row mode memory grant feedback
5. Approximate COUNT DISTINCT (for big data scenarios)
6. Batch mode on RowStore without ColumnStore index
7. Table variable deferred compilation (for better cardinality estimates)
8. New DMV sys.dm_db_page_info system function returns page information
9. Database scoped default setting for online and resumable DDL operations (ELEVATE_ONLINE or ELEVATE_RESUMABLE)
10. Always On Availability Groups – upto 5 synchronous replicas (3 till now)
11. New Polybase connectors or SQL Server, Oracle, Teradata, and MongoDB
12. Support for MERGE DML and Edge Constraints in SQL Graph
13. Expanded support for persistent memory devices (by bypassing the storage stack of the operating system using efficient memcpy operations)
14. SQL Data Discovery and Classification [more info]
… and many more, I’ll discuss about these features in my coming posts.
Feature Selection Page:
References:
>> SQL Server 2019 official Page
Migrate your SQL Server database to Azure by using SysTools “SQL to Azure Migration” Tool
Microsoft SQL Server is the most popular database management system, which provides flexibility to the database administrator to manage their database. It is a full-featured database and designed for use in corporate applications. But, many times, many users need to migrate their SQL Server database to the Azure SQL Database. Because Azure is an intelligent and fully managed relational cloud database that offers the broadest SQL Server engine compatibility.
However, at the time of SQL database to Azure migration, there are chances that we can miss some important steps or information such as trustworthy property, dependent Jobs, Linked Server, Logins etc. The main headache of any conversion is figuring out how to get the information from database and import into the server. Therefore, users need to use a professional tool to transfer SQL Server Database to Azure SQL Database.
One of the best third-party solutions I came up is SysTools SQL to Azure Migration Tool, which allows users to move all the SQL database objects like Tables, Triggers, Stored Procedures etc. to Azure SQL Database. Apart from this, there are many other features of this tool that are discussed below:
Key Features of SQL Server to Azure Migration Tool
1. Easily Transfer SQL Database to Azure
With the help of this tool, users can perform direct migration from SQL to Azure SQL database. For this, you need to provide all the credentials like server name, database name, username and password etc. After that, one can easily transfer SQL Server database to Azure.
2. Option to Add MDF/NDF Files
The tool provides an option to add MDF & associated NDF files to the software. You can also migrate all the database objects like Tables, Triggers, and Stored Procedures etc. from SQL to Azure.
3. Migrate Corrupted Database
The best part of this utility is that it can convert the corrupted database as well as the healthy file from local SQL server to Microsoft Azure SQL Database. Before migration, it scans the corrupted database files and repair them using either quick or advanced scan options.
4. Quick and Advance Scan Option
While working with this tool, the user can choose the required scan mode depending on the level of corruption out of the following modes:
– Quick Scan: This mode scans minor corrupted SQL files
– Advance Scan: This mode scan major corrupted SQL files
5. Auto Detect SQL Server File Versions
The SQL to Azure migration tool has an option to detect the versions of added MDF or NDF files automatically. For this, you just need to click on the checkbox next to this option.
6. Export Schema Option
The tool provides a feature to save the SQL database schema into Azure in any of the following ways:
– With the Only Schema: It migrates only schema of tables, views etc.
– With Schema & Date: It allows users to transfer both schema and data of all database objects.
Free and Licensed versions of SysTools SQL Server to Azure Migration Tool
The tool can be availed in the following two versions:
1. Demo Version: Users can freely download the trial version of software from the SysTools official website. It is available to understand the working of the software in a much better way.
2. Licensed Version: The licensed version of the tool can migrate SQL server database to Azure from MDF or NDF files. Also, it allows you to transfer the schema and schema & data.
System Requirements
The licensed version of SQL to Azure migration tool has been tested by the SQL experts. It will evaluate the performance of the software in terms of quality, reliability, security etc.
However, the testing has been performed in below-mentioned environment:
– Operating System: compatible with Windows 10 and all below versions
– Processor: Intel Pentium 1 GHz processor or any equivalent processor
– RAM: Around 2 GB of RAM is necessary
– Hard Disk: At least 100 MB space is required for installation
Advantages:
– It can also migrate corrupted or inaccessible SQL database to the Azure database.
– The software can preview all database components before migrating to Azure.
– The SQL Server to Azure migration tool has a simple and user-friendly interface.
Disadvantages:
– It is required to have database created on Azure SQL Server Database before migration.
– The Demo version only exports 25 records of each table.
Observational Verdict
After considering all the features of SQL Server to Azure Migration tool, I can say that it is a reliable and effective tool for DBAs and Developers to transfer SQL data to Azure without causing any data loss.
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 