[From Technet] Improvements to machine learning capabilities in SQL Server 2019

Many organizations seek to do more with their data than pump out dashboards and reports. Applying advanced analytical approaches such as machine learning is an essential arena of knowledge for any data professional. While database administrators (DBAs) don't necessarily have to become data scientists, they should have a deep understanding of the machine learning technologies at their disposal and how to use them in collaboration with other domain experts.

For those of us who work with SQL Server, there are many cool new capabilities to get familiar with in SQL Server 2019. At the heart of it all is a solution called Big Data Clusters, allowing you to create scalable clusters of SQL Server, Apache Spark, and HDFS containers running on Kubernetes.

That means flexibility in the ways you access the data and relational data side-by-side. Through the cluster, you can query data from external sources. You can also store big data in HDFS managed by SQL Server. At the end of the day, this makes more of your data available, faster and more easily, for machine learning, artificial intelligence, and other advanced analytical tasks.

SQL Server 2019 also provides expanded machine learning capabilities built in. It adds commonly requested features related to the use of R and Python for machine learning. For example, SQL Server 2019 enables SQL Server Machine Learning Services to be installed on Linux. Failover clusters are supported for greater reliability, and new and improved scripting capabilities open new options for generating and enhancing models.

Integration of Python with the SQL server database engine enables you to perform advanced machine learning tasks close to the data rather than moving it around. Insights generated from the Python runtime can be accessed by production applications using standard SQL Server data access methods.

With the addition of partition-based modeling, you can train many small models instead of one large model when using partitioned data. If you have data that breaks out easily using categories such as demographics or regions, partitioning enables you to get more granular with your models without having to break the dataset apart.

As the line between DBA and data scientist continues to blur, most of us will be expected to understand and manage these types of solutions. Microsoft clearly recognizes the importance of machine learning and the need to apply it more easily across different data typeswhile maintaining the performance and manageability benefits of using SQL Server.

To learn more about what you can do with Microsoft SQL 19, check out the free Packt guide Introducing Microsoft SQL 19. If you're ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.

The post Improvements to machine learning capabilities in SQL Server 2019 appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2r2Oewv

[From Technet] Tools and commands for running SQL Server 2019 on Linux

Organizations that embraced the option to run Microsoft SQL Server 2017 on Linux have been looking forward to the release of SQL Server 2019. Regardless of which operating system (OS) you choose, it's the same SQL Server database code, and includes even more of the same features and services as the Windows release. This introductory blog post about running Microsoft SQL Server 2019 on Linux provides basic information database professionals need to know before upgrading or migrating SQL Server onto Linux.

Supported Linux platforms

Microsoft SQL Server 2019 is tested and supported to run on several Linux distribution platforms:

• Red Hat Enterprise Linux (RHEL)
• SUSE Linux Enterprise Server (SLES)
• Ubuntu

Along with the above versions of Linux distributions, SQL Server 2019 is supported in a container scenario using a Docker image. Running a SQL Server database inside a Docker engine with Linux offers more flexibility, faster recovery, and quicker deployments, including deployments into the Azure cloud. For those becoming familiar with Linux, Docker for Windows or Mac gives you the option to run a Docker engine on your workstation with SQL Server 2019 on Linux.

Along with Docker technology, orchestration can be achieved, both managing and deploying SQL Server containers on Linux using Red Hat Open shift or Kubernetes. This includes SQL Server 2019 Big Data Clusters (BDC), fully scalable clusters with SQL Server, Spark, and Hadoop File System (HDFS). BDCs provide the ability to read, write, and analyze big data with T-SQL or Spark, and you can combine big data and relational data, too.

While this post has focused on RHEL, SLES, Ubuntu, and container options for Linux, you may have questions regarding other Linux distributions that may be able to run SQL Server 2019, but they're not supported by Microsoft. Always consider the database server's use before deciding to run SQL Server 2019 on an unsupported operating system and refer to Microsoft's support policy on the topic.

Tools for Microsoft SQL Server 2019 running on Linux

With the release of SQL Server 2019, there are more tools that database professionals can use with SQL Server 2019 running on Linux:

• Windows-based toolsYou can use any existing tools that run on Windows to access SQL Server on Linux. This includes SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), operational support systems, and third-party tools.
• Activity MonitorUse SSMS on Windows to connect remotely and use tools/features such as Activity Monitor commands on a Linux host.
• Azure Data StudioUse this cross-platform database tool to manage the Microsoft family of on-premises and cloud data platforms on Windows, MacOS, and Linux. That includes SQL Server 2019 running on Linux. You can also create SQL Server Notebooks in Azure Data Studio, multiple team members to collaborate on projects.
• Dynamic Management Views (DMVs)System DMVs collect different types of information about SQL Server, including Linux process information. Use server state information in conjunction with dynamic management functions (DMFs) to monitor the health of a server instance, diagnose problems, and tune performance.
• Query StoreUse Query Store for insights on query plan choice and performance. It simplifies performance troubleshooting by helping database professionals quickly find performance differences caused by query plan changes.
• Performance DashboardThe Performance Dashboard helps you determine if there's a performance bottleneck on the connected SQL Server instance and provides diagnostic data to help you resolve the problem.
• mssql-conf is a configuration script that installs with SQL Server 2019 for Linux.

Command Line Tools for Microsoft SQL Server 2019 on Linux

• sqlcmd and Bulk Copy Protocol (BCP)These command-line tools are natively available on Linux. sqlcmd is a command-line query utility, and BCP is a bulk import-export utility.
• mssql-scripterThis command-line tool on Linux generates T-SQL scripts for a SQL database running anywhere.
• mssql-cliThis new, interactive command-line tool written in Python offers several enhancements over sqlcmd in the Terminal environment.

With these tools and command line tools, you're now ready to run Microsoft SQL Server 2019 on Linux. To learn more about what you can do with Microsoft SQL Server 2019, check out the free Packt guide Introducing Microsoft SQL 19. If you're ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.

The post Tools and commands for running SQL Server 2019 on Linux appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2Px0XzE

[From Technet] Easier management of PolyBase and relational data through SQL Server 2019

The days when a database administrator (DBA) could specialize solely in a single database technology are rapidly ending. Today, we're much more likely than ever before to be asked to bring together many types of data from diverse sources. Although specialization still has its place, having the knowledge and tools at our disposal to cross those boundaries makes us much more useful.

That's one reason to get excited about the continued expansion of the PolyBase technology introduced in SQL Server 2016, which has become much more powerful in the release of SQL Server 2019.

Before PolyBase, when trying to use external data sources from SQL Server, you either had to transfer data from one source to another or query both sources and then write custom logic to joining and integrate the data at the client level. PolyBase simplifies the process of reading from external data sources. It does so by enabling your SQL Server instance to process Transact-SQL (T-SQL) queries that access both external data and relational data inside the instance.

Initially, PolyBase targeted Apache Hadoop and Azure Blob Storage. The ability to target big data inside Hadoop nodes expanded the ability to do modern analytics seamlessly from a SQL Server platform. No additional software needs to be installed in the Hadoop environment, and you don't even need to know Hadoop intimately to query it. PolyBase even pushed some computations to the Hadoop node to optimize queries.

What's awesome about PolyBase in 2019 is that Microsoft has added connectors that allow you to query a much wider range of external data. PolyBase now works with Oracle, Teradata, MongoDB, and any database that conforms to Open Database Connectivity(ODBC) standards. You can query the data where it lives without having to bring it into SQL Server. Best of all, it provides distributed, scalable query performance.

PolyBase can also simplify things when you do have to move data from one place to another. For example, you can bring data from Hadoop, Azure Blob Storage, or Azure Data Lake Store into relational tables. This allows you to take advantage of the fast analysis and columnstore capabilities of SQL Server without the need for a separate extract, transform, load (ETL) or import tool. On the other hand, it's also easy to export data from SQL Server to cost-effective online storage. Features like PolyBase make it much easier for database administrators to help organizations get maximum value from all their data, while optimizing the ratio of cost to performance.

To learn more about what you can do with Microsoft SQL Server 2019, check out the free Packt guide Introducing Microsoft SQL Server 2019. If you're ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.

The post Easier management of PolyBase and relational data through SQL Server 2019 appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2RngVyQ

[From Technet] What’s new with SQL Server 2019 Linux features

With SQL Server 2017, Microsoft entered the world of multi-OS platform support for SQL Server. For many technical professionals, the ability to run SQL Server on the same open source operating system as the rest of the application stack is not just a goal, but a dream that Microsoft made come true. With the release of SQL Server 2019, the inclusion of Linux now includes new features, support, and capabilities.

As a long-time Linux database administrator (DBA), in this post I'll share my top five focus areas for the Microsoft data professional to become knowledgeable of as they embark on the brave new world of Linux.

1. Embrace the command line

Yes, there is a graphical user interface, (GUI) for Linux, but the command line rules in Linux. We can't stress enough how important it is to learn how to navigate directories (cd), change permissions (chmod), and list contents (ls). Your best friend will become the -h argument to any command to get the help menu for whatever you're attempting.

It will be essential to know how to install and update your server and applications, (apt-get, yum, and zypper) as it may be your responsibility not only to just perform this task for the database tier, but also the server if a Linux administrator isn't already on staff in your organization.

You will also need to learn how to update your existing PowerShell scripts to work on Linux or learn how to write BASH scripts. This is commonly done from a terminal editor and one of the best is VI/VIM. Knowing how to use VI will give you credibility when working with Linux and deter too many questions when in the middle of critical work or even day-to-day tasks.

2. Understand the operating system

There are many distributions (aka flavors) of Linux, which has grown from the original release. Each distribution, although very similar, may have their own unique requirements and quirks. Understanding what distributions are best for enterprise deployments can ensure what you have deployed into the environment can withstand the demands the business puts on it. It was only two decades ago that Unix DBAs were hesitant to run relational databases on any version of Linux. In that time, every distribution has come a long way, but many distributions have a purpose. If we take a very specific flavor of Debian, called Raspbian, it was specifically designed to run the operating system for Raspberry Pis, which are single Advanced RISC Machine (ARM) processor computers. Understanding the difference in a distribution like Raspbian vs. enterprise Linux distributions like Enterprise Red Hat, Enterprise SUSE, and Ubuntu is important. Just because you can get SQL Server to run on non-supported Linux distributions such as CentOS doesn't mean you should run the business on them. Stick to the supported versions and the user experience will be more satisfying along with new features available in regular release intervals.

3. Use the tools you already have

Just because SQL Server is running on Linux doesn't mean that your previous robust tools for management, monitoring, and querying won't work any longer. As a DBA, you can still use SQL Server Management Studio (SSMS) and preferably, use Azure Data Studio to connect to your Linux SQL Server 2019 databases. You can't install Server Management Studio onto Linux, but you can create a Windows "jump box" with Server Management Studio and configure it as the primary log in point for administration tasks. Developers can still access SQL Server 2019 on Linux with Visual Studio Code with the SQL Server extension and have full use of SQL Server Data Tools (SSDT).

As this is Linux, the command line is still king. Sqlcmd is available to log in from on the Linux server to execute queries, run scripts, and perform management. Sqlcmd doesn't change in the Linux version and executing a script is as simple as the following example, which logs in as the admin user to the database and password, then runs a script and logs it all in a simple text file:

sqlcmd -U $username -S $servername -P $spassword -d $database -i $script.sql > $logfile

4. Build on existing knowledge with new tools

PowerShell is taking center stage on Linux with new releases on a regular basis, so keep those skills sharp and with a few changes, many of the scripts you use today can be updated to work on SQL Server 2019 on Linux. Although sqlcmd is available for your querying command line needs, there is a new tool in town called mssql-cli.

This installation is a simple, on-line command:

sudo pip install mssql-cli

If there are any issues with installation, Linux is commonly quite good about telling you what library or tool dependencies that must be installed before the installation needs to be run again for success.

Always remember, if you get stuck, help is always available to offer assistance:

mssql-cli h

To log into a database, the following is required:

mssql-cli -U $username -S $servername -P $password -d $database

If you notice the command is very similar to the example we used for sqlcmd rest easy, your world hasn't changed as much as you were concerned about. Once you're logged into mssql-cli, you'll be able to use this to access SQL Server 2019 databases inside Linux. Features like auto-complete with IntelliSense, multi-line editing, and a metadata repository makes it easier to work with the command line for those just getting up to speed with SQL Server 2019 on Linux. There are a few limitations in mssql-cli, like the ability to execute scripts or send an output aren't present, but the same queries that worked on-premises in sqlcmd will work here. Keep in mind that this new tool is in its infancy and still maturing on a regular release schedule.

The third new tool isn't that new but is the next powerhouse to take on the DBA since SQL Server Management Studio and is called Azure Data Studio. If you haven't embraced this cross-platform tool specifically designed for data professionals, you're missing out. No more 32-bit interface, this is a modern interface with IntelliSense, code control integration, and customizable dashboards.

The biggest benefit is that it can be installed on Linux with no local installation or jump box required. All three primary Linux distributions are supported, as well as a Mac OS installation. Database administration tasks that are less likely to be required with cloud implementations, such as on-premises physical management, backups, etc. still will require SQL Server Management Studio, but if you're part of the next generation of cloud administrators that have automated much of the mundane, then Azure Data Studio is a must.

5. Consider the power of containers

With Linux and SQL Server 2019, the addition of Big Data Clusters with Kubernetes support has been added. The reasons behind using containers are vast including simplified deployment, ease of management, and quicker recovery, but then add Big Data Clusters on top of this and it becomes quite the powerhouse.

Containers with Kubernetes offers DBAs the opportunity to manage the database without the added layer of separate OS for each node, or group of nodes, that is also called a pod. Less resources have to be allocated per database and without the additional layer, it can recover quicker.

Using Kubernetes, or similar sorted container orchestration, Big Data Clusters can be deployed, then manage a cluster of SQL Server, Apache Spark, and the Hadoop Distributed File System (HDFS). Polybase is then used to virtualize data from various data sources with SQL Server, while an HDFS data pool can be used as a data lake for all big data using Apache Spark. The SQL Server master instance simplifies the management and includes read, write, and processing of all data, no matter the source and is an exciting new frontier in the SQL Server 2019 release for Linux.

The list above includes just a few of the significant opportunities to learn and build your skills in Linux with SQL Server 2019. To help you on the way to mastering SQL Server 2019 on Linux, a number of resources have been made available through Microsoft, including this incredible new Packt e-book Introducing Microsoft SQL Server 2019. Don't miss out on the next generation of SQL Server. Learn all the things and let Microsoft help you get there. If you're ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.

The post What’s new with SQL Server 2019 Linux features appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2LmSRbo

[From Technet] How to configure persistent memory for SQL Server on Linux

With the release of SQL Server 2019 on Linux, Microsoft introduced persistent memory (PMEM) support on Linux. This is an exciting development, as previous versions of SQL Server on Linux didn't support PMEM. Let's look at how to configure the PMEM for SQL Server on Linux.

SQL Server 2016 introduced support for non-volatile DIMMs and an optimization called Tail of the Log Caching on NVDIMM.These leveraged Windows Server direct access to a persistent memory device in DAX mode to reduce the number of operations needed to harden a log buffer to persistent storage.

SQL Server 2019 extends the support for PMEM devices to Linux, providing full enlightenment of data and transaction logs placed on PMEM. Enlightenment is a way to access the storage device using efficient user-space memcpy() operations. Rather than going through the file system and storage stack, SQL Server leverages DAX support on Linux to place data directly into the device. This helps to reduce latency.

Enable enlightenment of database files

The first step to enabling enlightenment of database files in SQL Server on Linux is to configure the devices. In Linux, use the ndctl utility to configure PMEM device and create a namespace.

ndctl create-namespace -f -e namespace0.0 –mode=fsdax* –map=mem

You can verify the namespace using ndctl, as shown by this sample output:

ndctl list

[

{

“dev”:”namespace0.0″,

“mode”:”memory”,

“size”:1099511627776,

“blockdev”:”pmem0″,

“numa_node”:0

}

]

Next, create and mount PMEM device.

With XFS:

mkfs.xfs -f /dev/pmem0

mount -o dax,noatime /dev/pmem0 /mnt/dax

xfs_io -c “extsize 2m” /mnt/dax

With EXT4:

mkfs.ext4 -b 4096 -E stride=512 -F /dev/pmem0

mount -o dax,noatime /dev/pmem0 /mnt/dax

Once your device has been configured, formatted, and mounted, you can place database files in it or create a new database.

Note that PMEM devices are O_DIRECT safe. Therefore, you must enable trace flag (TF) 3979 using mssql-conf utility to disable the forced flush mechanism. This is a server-wide configuration change, so don't use this trace flag if you have any O_DIRECT non-compliant devices that require forced flush to ensure data integrity.

Now that you know how to use PMEM on SQL Server 2019 on Linux, you can reduce latency considerably. To learn more about what you can do with Microsoft SQL 19, check out the free Packt guide Introducing Microsoft SQL 19. If you're ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.

The post How to configure persistent memory for SQL Server on Linux appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2YdYcaz

[From Technet] How to deploy SQL Server 2019 Big Data Clusters

SQL Server 2019 Big Data Clusters is a scale-out, data virtualization platform built on top of the Kubernetes container platform. This ensures a predictable, fast, and elastically scalable deployment, regardless of where it's deployed. In this blog post, we'll explain how to deploy SQL Server 2019 Big Data Clusters to Kubernetes.

First, the tools

Deploying Big Data Clusters to Kubernetes requires a specific set of client tools. Before you get started, please install the following:

• azdata:Deploys and manages Big Data Clusters.
• kubectl: Creates and manages the underlying Kubernetes cluster.
• Azure Data Studio:Graphical interface for using Big Data Clusters.
• SQL Server 2019 extension:Azure Data Studio extension that enables the Big Data Clusters features.

Choose your Kubernetes

Big Data Clusters is deployed as a series of interrelated containers that are managed in Kubernetes. You have several options for hosting Kubernetes, depending on your use case, including:

• Azure Kubernetes Service (AKS): You can use the Azure portal to deploy Azure Kubernetes Service. Azure Kubernetes Service allows you to deploy a managed Kubernetes cluster in Azure, all you manage and maintain are the agent nodes. You don't even have to provision your own hardware.
• Multiple Linux machines: Kubernetes can also be deployed to multiple Linux machines, physical or virtual. This is a great option if you're looking for an opportunity to leverage existing infrastructure. You can use the kubeadm tool to create the Kubernetes cluster and a bash script (see sample script). Visit our documentation to learn how to automate the deployment.

Deploy SQL Server 2019 Big Data Clusters

After configuring Kubernetes, your next step is to deploy Big Data Clusters with the azdata bdccreate command. There are several different ways to do this as well:

• If you're deploying to a dev-test environment, consider using one of the default configurationsprovided by azdata.
• To customize a deployment, you can create and use your own deployment configuration files.
• If you need to walk away while you're deploying, an unattended installationwill allow you to pass all other settings in environment variables.

Deployment scripts

Deployment scripts can make deployment easier and faster by deploying both Kubernetes and Big Data Clusters in a single step. They also often provide default values for Big Data Clusters settings. However, you aren't locked into the values defined by the script. Deployment scripts can also be customized, so you can create your own version that configures the Big Data Clusters deployment to your liking.

Two deployment scripts are currently available. The Python script deploys a big data cluster on Azure Kubernetes Service, and the Bash script deploys Big Data Clusters to a single node kubeadm cluster.

Deployment notebooks

There's one more option for deploying Big Data Clusters, and that’s running an Azure Data Studio notebook. There will also be a UX experience in Azure Data Studio for deployment.

Because SQL Server Big Data Clusters are deployed on Kubernetes, getting up and running is fairly painless. As you can see, you have several options each step of the way, but your path is made clear based on your use case. To learn more about what you can do with Microsoft SQL Server 2019, check out the free Packt guide Introducing Microsoft SQL 2019. If you're ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.

The post How to deploy SQL Server 2019 Big Data Clusters appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2CYgmTW

[From Technet] Reflections on PASS Summit 2019 from the SQL Tools Team

Last September at Microsoft Ignite 2018, Rohan Kumar, Corporate Vice President of Azure Data, announced the general availability of Azure Data Studio. This month, a little over a year later, Azure Data Studio took PASS Summit 2019 by storm, from keynote demos to community sessions to customer conversations at the Microsoft booth, we experienced overwhelming support for the immense growth of Azure Data Studio since its general availability announcement.

The conference kicked off with an exciting keynote by Rohan, who announced several general availability and preview milestones for Azure Data Studio and SQL Server, including the general availability of SQL Server 2019, the preview of Azure SQL Database Edge, and the preview of Azure Arc. Rohan's keynote was enriched by demos from team members, with several using Azure Data Studio to demonstrate the power of the newly announced capabilities of SQL Server. My favorite example is Asad Khan's, Partner Director of Program Management for SQL Server and Azure SQL, demo of a connected factory powered by SQL. In his demo, he highlighted Notebooks in Azure Data Studio while showing how devices collecting data at the edge can stream data into a SQL Server 2019 Big Data Cluster. Then, he visualized the data using the SandDance extension that received a huge spike in applause from the audience.

In addition to the day one keynote, Azure Data Studio was demoed in 16 total sessions including four sessions hosted by community members centered entirely around Azure Data Studio. Notebooks in particular made a huge splash at PASS Summit. By 4:00 PM on the first day of the conference, Steve Clement, an attendee tweeted, "Five sessions so far this week and all 5 have used ADS Notebooks. I'm noticing a theme here. #PASSSummit."

For those looking forward to the future of SQL Server Tooling, Vicky Harp, Principal Program Manager Lead, SQL Server Tools and Udeesha Gautam, Senior Software Engineering Manager, SQL Server Tools, gave a first look at upcoming features in a highly attended session of over 350 attendees titled What's new in SQL Server Tools. Highlights from their session include:

• Vicky's internally-renowned Corgi demo, where she employs Notebooks in Azure Data Studio to name a Corgi using a SQL Server 2019 Big Data Cluster
• Udeesha's demo of Azure SQL Database Edge testing and development in Azure Data Studio following its preview announcement in Rohan's keynote
• PostgreSQL support in Azure Data Studio in preview
• MariaDB and MySQL support in Azure Data Studio in preview (not yet publicly available)
• UX mockups of database project support for Azure Data Studio
• And more

Finally, we received a tremendous amount of support and feedback from the community at the Microsoft clinic and in our three focus groups. In many cases, we had users approach us just to rave about Azure Data Studio, and others tell us that they had not heard of Azure Data Studio until this conference, but they were excited to start using it. Both the clinic and our focus groups were important opportunities for our program managers and engineers to speak directly to customers, learn about their unique use cases for our tools, gather feedback, and answer questions.

As a new member of the SQL Tools team, this was my first time attending PASS Summit and regardless of my sleep deprivation and jam-packed schedule, I found myself constantly energized by the encouragement of the PASS community for our team's hard work. We truly take all of your comments seriously as guidance for our prioritization, and we strive day in and day out to delight our users through our work.

On a closing note, I will share one of my favorite tweets about Azure Data Studio this conference season, written by attendee Richard Munn: “If it was possible to marry and spend the rest of your life with software, my choice would be @AzureDataStudio!”

The post Reflections on PASS Summit 2019 from the SQL Tools Team appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2KwwDnd

[From Technet] Five essential skills for today’s database administrators

DevOps, the cloud, and new database technologies mean our jobs as database administrators (DBAs) are changing at an ever-faster pace. If you're fascinated by data and all the things you can do with it, it's a thrilling time to be in the business. Here are five of the skills we see as essential parts of the modern DBA's toolkit.

1. Expertise with multiple technologies: The one-size-fits-all approach to databases is fading. Just as application developers are moving toward a microservices model that focuses on the right tool for the job, organizations are choosing databases according to specific workload needs. The more you know about Hadoop, NoSQL, graph, and other technologies, the better-positioned you will be to make a positive contribution to the conversation.
2. Collaboration: Speaking of conversations, DBAs will increasingly need to become contributing members of application teams rather than siloed specialists off in their own corners. DevOps tend to break down the barriers between IT functions. Understanding how applications work, and, even better, how they deliver business value, puts you in a position to be a creative problem solver and all-around data expert.
3. Data science skills: Machine learning and AI are some of the fastest growing uses of data in the enterprise today. Making these technologies work efficiently and getting superior results depends on good data, which depends on you and your specialized skills.
4. Cost optimization: In an on-premises world, most DBAs aren't deeply involved in cost conversations unless there's a big IT capital investment taking place. In the cloud, database configuration has a direct impact on the bottom line. Understanding how to manage the technology in cost-optimized ways can help your organization do more with its IT budget.
5. Cloud (and edge) everything: There will be on-premises databases around for a long time, but the cloud is going to continue growing in importance. The good news is that it can help automate a lot of the infrastructure-related drudgery that characterizes classic DBA jobs. The even better news is that it frees you to do more creative things. In many industries such as retail, manufacturing, and aerospace, edge computing is becoming even more interesting than cloud and requires further evolution of database approaches. If you're an expert here, you can write your own ticket for several years to come.

IT has always been a fast-evolving field and it's only going to accelerate for DBAs. Now's the time to skill up and take the lead. To learn more about what you can do with Microsoft SQL Server 2019, check out the free Packt guide, Introducing Microsoft SQL Server 2019. If you're ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.

The post Five essential skills for today’s database administrators appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/32F6852

[From Technet] Build an intelligent analytics platform with SQL Server 2019 Big Data Clusters

In the most recent releases, SQL Server went beyond relational data and enabled support for graph data, R, and Python machine learning, while making SQL Server available on Linux and containers in addition to Windows. At the same time, organizations are challenged with the amount of data stored in different formats, in silos, and the expertise required to extract value out of the data. Through enhancements in data virtualization and platform management, Microsoft SQL Server 2019 Big Data Clusters provides an innovative and integrated solution to overcome these difficulties. It incorporates Apache Spark and HDFS in addition to SQL Server, on a platform built exclusively using containerized applications, designed to derive new intelligent insights out of data.

Modernize your data estate with a scalable data virtualization and analytics platform

Data integration strategies are based on extract, transform, and load (ETL) results in data duplication and transformations that diminish data quality, higher maintenance, and security risks. SQL Server 2019 has a new approach to data integration called data virtualization across disparate and diverse data sources, without moving data. Out-of-the-box connectors for data sources like Oracle, Teradata or MongoDB help you keep the data in place and secure, with less maintenance and storage cost. You can now uncover unconsidered perspectives by easily combining all your data, which ultimately leads to better data-driven decisions.

Systems Imagination is using these capabilities in SQL Server Big Data Clusters, eliminating the need to shift or replicate data to gain insights.

"With SQL Server 2019 Big Data Clusters, we can analyze cancer research data coming from dozens of different data sources, mine interesting graph features, and carry out analysis at scale" – Pieter Derdeyn, Knowledge Engineer, Systems Imagination.

In addition, SQL Server 2019 Big Data Clusters provides a comprehensive machine learning and AI platform with all the tools and services required to ingest, store, prepare, and analyze data.With previous versions of SQL Server, you can execute Python and R scripts to clean and prepare data, train, evaluate, or deploy machine learning models within a database. Within Big Data Clusters, you can use the data analysis tools and frameworks of your choice on the same platform where data resides.

In Azure Data Studio, you can submit Apache Spark jobs and use the built-in compute context in your preferred language including R, Python, or Scala. Your AI and machine learning lifecycle can benefit from SQL Server's mission-critical features like performance, security, availability, and scalability. You can also operationalize these models and deploy them as containerized applications running within the platform, side by side with the data. Models are exposed as a REST API for easy integration with your business applications. This set of comprehensive analytics tools is what Dr. Foster, one of the Big Data Clusters early adopters customers, leveraged for their analytics platform:

"Our analysts need access to cutting edge data science technologies and techniques that adhere to strict industry-regulated guidelines. With SQL Server 2019 Big data clusters, we are able to analyze our relational data in the unified data platform, leveraging Apache Spark, HDFS, and enhanced machine learning capabilities, all while remaining compliant." – George Bayliffe, Head of Data, Dr. Foster

Fig 1. Intelligence over all your data with SQL Server 2019 Big Data Clusters.

Built on top of the Kubernetes containers, Big Data Clusters have a built-in management system on any infrastructure

Managing all the services that enable you to run relational and big data workloads in a secure, efficient, and scalable way is challenging. With Big Data Clusters, you can operationalize management and data engineering tasks in an integrated and consistent way with a modern, containers-based architecture built on top of Kubernetes. At the center of this platform is the SQL Server master instance that stores relational data and serves as an entry point to other data sources within or outside the cluster. With additional SQL Server instances in the data pool, you can build a scale-out data mart for ingesting and automatically distribute data resulting in enhanced query performance efficiency. Multiple parallel-processing SQL Server instances in the compute pool and elastically scalable shared storage with SQL Server and HDFS are also included by default in a big data cluster. To further expand your data lake, you can unify your HDFS stores using HDFS tiering, Microsoft's latest contribution to the Apache HDFS open source project, now available with SQL Server 2019 Big Data Clusters. Along with HDFS, we include Apache Spark, ideal for data ingestion tasks, preparation, training, and analysis of high data volumes in a scalable and performant way.

Fig 2. The combination of SQL Server database engine, Apache Spark, and HDFS in SQL Server 2019 enable diverse big data scenarios.

The choice of infrastructure is fundamental when it comes to deploying and managing all these components at scale. Kubernetes enables application portability, elastic scalability, and consistency across platforms, allowing SQL Server 2019 Big Data Clusters to ensure a predictable, self-contained, and fast deployment workflow. Balzano recognizes the value of a self-managed, autonomous and flexible platform that allows you to focus on getting valuable insights out of data.

“SQL Server 2019 Big Data Clusters allowed us to accommodate and integrate all aspects from one shared platform for our data scientists and for our software engineers who wire up workflows, security, and scalability. At runtime, our healthcare customers benefit from simple containerized deployment and maintenance while being able to move our solution between on-premises and the cloud easily.” – Ren Balzano, Founder and CEO, Balzano.

SQL Server's years long commitment is to support mission-critical applications. In Big Data Clusters, we ensure that management services embedded within the platform provide fast scale and automated upgrade operations, automatic logs and metrics collection, enterprise grade secure access, and high availability. Azure Active Directory authentication is available through innovative implementations for applications running in containers, providing an integrated security model that spans all services, including SQL Server, Apache Spark, and HDFS. Maintenance tasks like secure container deployment, certificates, and secrets storing and rotation are provided by the platform through tight integration with Azure Active Directory and Kubernetes. Applications running on top of a Kubernetes orchestrator benefit from the platform's built-in health monitoring, failure detection, and failover mechanisms. In addition, for critical components like the SQL Server master instance, you can enable flagship features like Always On Availability Groups for additional reliability and read scale out capabilities.

Cost effective big data and AI platform

You can start with the Developer Edition at no cost and try the complete set of capabilities of a full-featured deployment. The SQL Server 2019 licensing model was updated to incorporate a new subscription model for Big Data Clusters, and you have the option to use your existing SQL Server software licenses for Big Data Clusters deployments. A new Software Assurance benefit gives you eight Big Data Cluster node core licenses for each of the Enterprise Edition SQL Server master instance cores for free.

Get started

With a unified set of data integration, management, and data analysis tools, Big Data Clusters makes it not just easy, but also affordable for you to build on this platform. SQL Server 2019 Big Data Clusters provides the analytics at scale platform that you can count on for enterprise-grade performance, high availability, security, and manageability. We are very excited to see you use the broad range of scenarios that will help bridge the gap between relational data and big data deployments. You can store and analyze data from multiple sources at scale, in various data formats, with scale-out compute for data processing and machine learning, together with the industry-leading experience of SQL Server.

Start building your new analytics platform today. Here are a few pointers to help you get started:

• Read the online documentation for SQL Server 2019 Big Data Clusters
• Deploy Big Data Clusters and leverage the new analytics platform powered by Kubernetes
• Experiment and try new capabilities of SQL Server 2019 Big Data Clusters using free training workshops
• Learn more about all of the enhancements that went in SQL Server 2019 and try the new release of SQL Server today!

The post Build an intelligent analytics platform with SQL Server 2019 Big Data Clusters appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/36VoVN1

[From Technet] New in Azure Synapse Analytics: CICD for SQL Analytics using SQL Server Data Tools

At Microsoft Ignite 2019,weannouncedAzure Synapse Analytics,a major evolution of Azure SQL Data Warehouse.Thesame industry leading data warehousenow provides a whole new level of performance, scale, and analytics capabilities.One of these capabilities is SQL Analytics, which provides a rich set of enterprise data warehousing features.

Today we are announcing the general availability of the highest requestedfeature for SQL Analytics in Azure Synapse, SQL Server Data Tools (SSDT) database projects. This release includes support for SQL Server Data Tools with Visual Studio 2019 along with native platform integration with Azure DevOps providing built-in continuous integration and deployment (CI/CD) capabilities for enterprise level deployments. This announcement also comes with support for the Schema Compare extension in Azure Data Studio for SQL Analytics.You can now expect a frictionless development and deployment experience on any platform for your analytics solution.

Since announcing preview support for SQL Server Data Tools (SSDT), customers have been able to use popular SQL Server Data Tools features such as Schema Compare, build, and publish for local development of their data warehouse.Althoughthis hashelpedcustomersaccelerateprojectdevelopment, an automated build, test, and deployment infrastructure isstill critical for continuous integration and deployment (CI/CD) scenarios.Without thenativeintegration with Azure DevOps, customers werestillforced tomanuallywritePowerShell and TSQL scriptsintegrated with Azure DevOps for an automated release process.

With SQL Server Data Toolsgenerally available and native Azure DevOps support,you cannowset up stable release pipelineswithout any custom code, andchanges to your data warehouse modelcan besafelyand automaticallypromoted across development, testing, and production environments.Preview customerssuch asT-Mobilewill now beable to accelerate theirfeature developmentwithAzure Synapse.

"In our current environment, we would have needed hundreds of custom scripts to validate and promote changes across our test and production environments. We're excited to now simply use SSDT, MSBuild, and the Publish task in Azure DevOps to deploy and release features to production on a consistent and faster cadence." –Anthony Sabol, Director, Reporting & Analytics at T-Mobile.

Integrate with Microsoft Azure Repos for continuous integration

Data engineers and developers caneasily integrate their SQL Server Data Tools database projects with Microsoft Azure Repos.

Configure continuous deployment using Microsoft Azure Pipelines

Changes committed to source controlin Azure Repos can automaticallybepre-validatedusingMSBuildandpromoted totarget environments using Microsoft Azure Pipelinesand thebuilt-inSQLAnalyticsdeploymenttaskextension.

Cross platform support for SchemaComparewithAzure Data Studio

Azure Data Studiois a cross-platform database tool thatnow allows you to compare the schemabetween twodatawarehouse definitions.

Next steps

• Install Visual Studio 2019 for SQL Analytics to get started
• Set up source control integration with SQL Analytics by using Azure Repos
• Integrate a simple Azure Pipeline forcontinuous integration and deployment for SQL analytics capabilities within Azure Synapse
• Learn more about Azure Data Studio SQL Compare
• For feature requests and providing us feedback, please vote on our UserVoice

The post New in Azure Synapse Analytics: CICD for SQL Analytics using SQL Server Data Tools appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2qrEXxf

[From Technet] State of the SQL Server tools

This week we're announcing the general availability of SQL Server 2019, a significant milestone for Azure Data and for SQL Server customers. This presents a good moment to give an update on the state of tooling for SQL Server.

Since SQL Server 2016, the tools for SQL Server have been released independently "out of box" from the server product. This allows us to be more agile to the needs of our users, get both features and bug fixes shipped more quickly, stay aligned with the more continuous release cycle of Azure SQL, and in general allows the tools team to innovate in exciting ways. However, one side effect is that it can be difficult to understand what's happening across the tools landscape, as things change quickly in multiple products that are releasing as frequently as every month.

The SQL Server client tools cover the full breadth and depth of the product, and can be broadly categorized as follows:

• SQL Server drivers and connectivity APIs, such as Microsoft.Data.SqlClient, ODBC, JDBC, etc.
• SQL Server tooling APIs and scripting support, including SQL Server Management Objects (SMO), DacFX, and PowerShell
• Command line tools for SQL Server like sqlcmd, bcp, mssql-cli, and sqlpackage
• Jupyter Notebooks, a new entrant in this space that bridges the CLI-to-GUI divide
• Graphical tools for SQL Server, including SQL Server Management Studio, Visual Studio, Azure Data Studio, and Visual Studio Code
• The Azure portal experiences supporting the Azure SQL family of products

Each of these areas has had important updates since the last major release of SQL Server.

SQL Server drivers

Microsoft maintains or contributes to a number of drivers for SQL Server supporting a wide variety of languages. As new features are added to Azure and to SQL Server, some require updates at the protocol and driver level, including features like UTF-8 support, Always Encrypted, and data classification. The most significant strategic update in this area has been the change from System.Data.SqlClient to Microsoft.Data.SqlClient as the premier driver for the .NET ecosystem. You can read more about this in the .NET announcement blog. The Microsoft.Data.SqlClient driver and its associated NuGet package are now generally available, and while System.Data.SqlClient will continue to be supported, the new out-of-the-box Microsoft.Data.SqlClient is the recommended assembly for .NET developers targeting SQL Server in the future.

SQL Server APIs and scripting

There is continuous work in the important SQL Server Management Objects (SMO) and DacFX libraries, which are the application interfaces used by most of the rest of the tooling stack to interact with SQL Server. With SQL Server 2017 introducing a need for cross-platform tooling, a lot of effort has been spent in the past few years in updating these assemblies so that they can be used on MacOS, Linux, and in containers. The SQL Assessment API is a new offering that allows for best practice checks to be performed with SQL Server Management Objects and with PowerShell. And the SQL Server PowerShell module continues with very regular updates, having had 17 releases in the past two years. These capabilities are widely used, and the SQL PowerShell module has been downloaded over 2.3 million times since 2017.

Command line tools

The SQL Server command line tools were some of the first and most important tools to be taken cross-platform for users of containers, Linux, and MacOS, owing to their important role in non-graphical and automation use cases. The open source mssql-cli is a new command line tool with a more user-friendly interactive querying experience compared to sqlcmd, and important work has been done in sqlpackage.exe to allow cross platform extract and build for dacpacs. This work is not only foundational to bringing more cross-platform developer experiences into the graphical tools, it has proven to bring significant performance improvements as well.

Jupyter Notebooks

The flexible and portable format of Jupyter Notebooks has been popular in the data science and big data communities for a number of years. However, with the introduction of full fidelity T-SQL support to notebooks in Azure Data Studio, this is a technology that has become relevant to a wider community of SQL Server users including developers, IT professionals, database administrators, analysts, and more. The nature of a Jupyter notebook creates a "documented CLI" experience which can be used for development, data exploration, troubleshooting, documentation, and more. Through integration with Microsoft tools like Azure Data Studio, PowerShell, and Azure Data, in combination with open source platforms like Papermill, it's possible for notebooks to be used as both interactive and non-interactive, operationalized experiences. The SQL Server team is investing heavily into notebooks and the related Jupyter Book concept for product documentation, deployments, and more. Azure Data Studio offers a full Jupyter Notebook editor with support for SQL, Python, Apache Spark, Scala, R, and PowerShell.

Graphical tools

The graphical tools for SQL Server are the most visible and recognizable tools in the portfolio. The flagship graphical interface for SQL Server is SQL Server Management Studio (SSMS), a tool that has been used by millions for over 15 years. Since SQL Server 2016, SQL Server Management Studio has been available as a separate download from SQL Server. In the past year, the major version 18.0 shipped, followed by a number of minor releases on an approximately bimonthly cadence. Regular updates and improvements to SQL Server Management Studio will continue, but most of the net new innovations in the graphical tooling space, such as notebook support, can be expected to ship in Azure Data Studio. As a cross-platform tool designed to support multiple database systems, Azure Data Studio operates in a slightly different space from SQL Server Management Studio. Azure Data Studio is open source and ships monthly, with major features in the last year including Jupyter Notebooks, Dacpac import and export, Schema Comparison, external table creation, data visualization in SandDance, and extensions provided by the community and commercial partners. Azure Data Studio is also investing in deployment experiences for SQL Server, which is an inversion from the days when a SQL Server CD was used to install SQL tools.

For our SQL Server developers, the SQL Project functionality in Visual Studio has been supported and maintained, but without a great deal of visible change in the last couple of years, as work has been going into the API and CLI levels. This lower level work is coming toward completion, and new experiences around development for Azure SQL Database Edge are imminent. Additionally, we have a vibrant userbase of the SQL Server (mssql) extension for Visual Studio Code, that's very actively maintained and has recently shipped a major update.

Azure portal

In the Azure portal, a great deal of work is being done to consolidate, standardize, and simplify the process of working with the Azure SQL family of products including SQL Server on Azure Virtual Machines, Azure SQL Database, and Azure SQL Managed Instance. The past few months have brought a new unified deployment and management experience and moving forward, we intend to bring more features pertaining to management at scale and beyond.

Looking forward

The SQL Server tooling stack has never been deeper or stronger. The agile nature of tooling development combined with the active involvement of the SQL Server community creates an environment where it's possible to iterate more quickly than ever. As SQL Server spreads across an ever widening combination of deployment options and platforms, from ground to cloud, expect to see changes in the SQL Server tools as well to support the new workflows and experiences that users need to do their best work.

The post State of the SQL Server tools appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2NK79na

[From Technet] The November 2019 release of Azure Data Studio is now available

Today we are announcing the November release ofAzure Data Studio is now available.

Download Azure Data Studioand review therelease notesto get started.

Please note: After downloading Azure Data Studio, say Yes to enabling the preview features so that you can use the extensions.

Azure Data Studio is a multi-database, cross-platform desktop environment for data professionals using the family of on-premises and cloud data platforms on Windows, MacOS, and Linux. To learn more,visit our GitHub.

The key highlights to cover this month include:

• Announcing SQL Server 2019 support
• New notebook features
  • Announcing PowerShell notebooks
  • Announcing collapsible code cells
  • Performance improvements in notebooks
• Announcing Jupyter Books
• General availability of Schema Compare and SQL Server Dacpac extensions
• Announcing Visual Studio IntelliCode extension
• Bug fixes

For a complete list of updates, refer to therelease notes.

Announcing SQL Server 2019 support

With the November release of Azure Data Studio, we officially support experiences for SQL Server 2019 Big Data Clusters features. New features include:

• Deploy SQL Server 2019 Big Data Clusters with the Big Data Clusters deploy wizard
• Manage SQL Server 2019 Big Data Clusters health with the controller dashboard
• Manage HDFS access control lists using security access control lists (ACLs) dialog
• Add mounts using HDFS tiering dialog
• Troubleshoot using built-in Jupyter Book, SQL Server 2019 guide
• Added Teradata and Mongo support in the External Table Wizard
• Renamed the SQL vNext extension to data virtualization extension

To learn more about SQL Server 2019 Big Data Clusters, check out our documentation.

You can also learn more through our SQL Server 2019 video learning series, where you can learn about SQL Server 2019 and see the humans behind creating this technology.

New notebook features

Our users love Azure Data Studio notebooks. Whether for education, demos, or for running daily tasks, our community has been very active in letting us know the top features that they want to see in notebooks.

With the November release of Azure Data Studio, we have added three key features:

• PowerShell kernel support
• Collapsible code cells
• Output toolbar for save output actions

PowerShell kernel support

The number one most asked for feature by far was support for PowerShell notebooks. We have made it even easier recently to use PowerShell in Azure Data Studio with the improvements added through the PowerShell extension, and now we continue this story by adding the PowerShell kernel to our notebooks.

Simply open a new notebook and change the kernel drop-down to install the PowerShell kernel. Do you have a great PowerShell script that you want to share with the community? Add your scripts to the code cells of a PowerShell notebook and add rich markdown instructions to help guide the user when to run each command.

Try it out today!

Collapsible code cells

Sometimes with really long T-SQL scripts, this affects the readability of the notebook. What if you could make it easier to read a notebook while keeping the option to execute or modify the notebook? Now you can with collapsible code cells.

Collapsible code cells only apply to code cells, but this allows you to hide very long code cells by default. You can also collapse all code cells or expand all code cells through the notebook toolbar.

In addition, if you only want to collapse certain cells, you have the option for each code cell and can save the state of the notebook to reflect this.

No more walls of code text that affect readability. You can start using it now.

Output toolbar

For the SQL kernel, it’s common that users want to save the results of a code cell to another file format like csv, json, or .xls. We made this easier by adding an output toolbar so that you can easily save the output to another file. More will be coming to this toolbar, so let us know today what you would like to see.

If you have any feedback for additional notebook features,let us know on our GitHub issues.

Announcing Jupyter Book support

As more people use Azure Data Studio notebooks, we have received many requests for a way to create a collection of related notebooks to share with others. Introducing Jupyter Book support. Jupyter Books are a collection of markdown files and notebooks organized with a table of contents. This support allows users to interact with existing Jupyter Books so that you can have a single location to access all the notebooks you need for your daily use cases.

In the November release, we ship a book with Azure Data Studio. You can access this book through the command palette by typing "Jupyter Books: SQL Server 2019 Guide." This will launch the book in the books view-let. This book was built to help you troubleshoot SQL Server 2019 Big Data Clusters and is a highly interactive experience.

This book gives a sneak peek into the vision we have for books going forward in having richer troubleshooting experiences as part of the product. You can execute a troubleshooting notebook within the book, and if it runs into an error, the error message will point to another notebook to fix that error. You can see this for yourself as you try out SQL Server 2019 Big Data Clusters.

We will continue to invest in books and add ways to make books experience easier for your daily use.

General availability of Schema Compare and SQL ServerDacpacExtensions

It's been a little over a year since the general availability of Azure Data Studio, and since then we have amassed a growing library of over 45 extensions, both first-party and community-contributed. To date, all our first-party extensions have remained in preview since their release. Today, we’re announcing the first two of our extensions to transition from preview to general availability: The SQL Server Dacpac extension and the Schema Compare extension.

At its release, the SQL ServerDacpacextension wasone of our top ten most requested features.It offerssupport for the Data-tier Application Wizard, whichsimplifiesthe development and deployment of data-tier elements by making it easier for users to manage .dacpacand .bacpacfiles. Ina unifiedwizard experience, users can:

• Deploya.dacpacto a SQL Server instance
• Extract a SQL Server instance toa.dacpac
• Create a database froma.bacpac
• Export schema and data to a .bacpac

The Schema Compare extension provides a cohesive experience for users to compare database definitions and apply the schema differences from the source to the target. The extension also offers a wide range of options to customize comparisons depending on each user's individual needs. For example, a user could decide to exclude stored procedures from the Schema Compare or opt into backing up the target database before deploying any changes. Check out the gif below for a demonstration of our customizable options.

What's new in the generally available extensions?

As part of our committed work for general availability, we delivered several highly requested features and enhancements, along with a few fundamentals like accessibility compliance and localization. Here's a high-level overview of the new features encompassed by our general availability efforts:

• Support forAzureSQL Data Warehouse
• SQLCMDmodefordirect modification and execution of Schema Compare andDacpacscripts
• Support forAzure Active Directory (AAD) authentication
• Full accessibility via screen readers and keyboards
• Localizationin ten languages
• Bug fixes and improvements

Announcing Visual Studio IntelliCode extension

In addition to being supported in the recent update to the mssql extension for Visual Studio Code, we’re announcing that Azure Data Studio now supports the Visual Studio IntelliCode extension. If you haven't heard of IntelliCode, we highly encourage you to read the general availability announcement from Microsoft Build 2019.IntelliCode is a set of AI-assisted capabilities that aims to improve developer productivity with features like contextual IntelliSense, code formatting, and style rule inference.

SQL Server developers can now get smarter IntelliSense for T-SQLreserved keywords like SELECT, FROM, WHERE, and many more. The IntelliCode suggestions are starred items that appear at the top of the IntelliSense list. This improves productivity as you write your T-SQL queries by having reserved keywords appear at the top of IntelliSense for quick completion. You can also see the suggestions through the light bulb icon.

This improves productivity as you write your T-SQL queries as reserved keywords that you are looking for appear at the top.

In order to try out IntelliCode, you will first need todownload the IntelliCode extension.After downloading the extension, you can start seeing IntelliCode working instantly in your SQL Server query files and also in SQL Server notebooks.

We will continue to work with the IntelliCode team to unlock even more IntelliCode features for our SQL Server users. If this feature triggers a light bulb for you and you have ideas for how this feature can help you, pleasesubmit a feature request on our GitHub issues page.

To learn more about IntelliCode, check out theIntelliCode product page.

Bug fixes

If you would like to continue helping to make Azure Data Studio a great product, please share any feedback or report issues through our Issues page. Our engineering team is regularly going through the untriaged issues and assigning issues into different monthly milestones so that you know we're working on it. Your votes on issues help us prioritize.

Please review the full list of bug fixes for the November release to learn more.

Contact us

If you have any feature requests or issues, please submit toour GitHub issues page.For any questions, feel free to comment below ortweet us.

The post The November 2019 release of Azure Data Studio is now available appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2NMHHgR

[From Technet] SQL Server Management Studio 18.4 and sqlpackage v18.4 generally available

Today we're announcing the release of SQL Server Management Studio (SSMS) 18.4. This update has full support for SQL Server 2019 capabilities as well as some bug fixes.

You can download SQL Server Management Studio 18.4 today and review SQL Server Management Studio release notes for details.

In addition, we’re also announcing sqlpackage v18.4 is now generally available. This means cross-platform support for sqlpackage is officially generally available.Download the latest release today.

In this blog, you will learn about the new features in these two exciting releases.

SQL Server Management Studio 18.4 features

General SQL Server Management Studio

SQL Management Objects and scripting

• Supports the script of a materialized view in SQL Data Warehouse

Data classification

• Added support for a custom information protection policy for data classification

Query store

• Added the “Max plan per query” value in the dialog properties
• Added support for the new custom capture policies

Key bug fixes include:

General SQL Server Management Studio

• Object Explorer
  • Fixed an issue where dropping an Azure SQL Database in Object Explorer was showing an inaccurate error message.

• SQL Agent
  • Fixed an issue where the agent operator UI would not update the operator name when it was changed in the UI and it wouldn’t be scripted. See the feedback forum to learn more.

• General SQL Server Management Studio
  • Fixed a long outstanding issue where SQL Server Management Studio wasn’t honoring the connection information passed at the command line when SQL files were also specified.
  • Mitigated the removal of the -P command line option by having SQL Server Management Studio look at its cache of credentials. If the required credential was found, the connection would be established using it.
  • Fixed a crash in SQL Server Management Studio while trying to display securables on “Replication Filter” objects.

• Query result
  • Fixed an issue introduced in SQL Server Management Studio 18.3.1, where grids would be drawn slightly too narrow and show “…” at the end of the longest string in every column.

• SQL Management Objects and scripting
  • Fixed an issue when “Script table as” for SQL Data Warehouse whose collation was Japanese_BIN2 wasn’t working.
  • Fixed an issue where ScriptAlter() ended up executing the statements on the server.

• Import flat file
  • Fixed an issue where “Import flat file” functionality wasn’t handling text qualifiers correctly.

• Replication tools
  • Fixed an issue that was causing the application to throw an error (“Could not load file or assembly”) when trying to edit SQL Agent jobs

Please see the SQL Server Management Studio release notes for a comprehensive list of bug fixes.

Sqlpackage v18.4 features

New features include:

• Added support to deploy to Azure SQL Data Warehouse
• Added support for new Azure database editions including GeneralPurpose, BusinessCritical, and Hyperscale
• Added Azure SQL Database Managed Instance support for Azure Active Directory users and groups
• Supports the /AccessToken parameter for sqlpackage on .NET Core
• Removed SHA1code signing

View the complete set of release notes to learn more.

To learn more about sqlpackage, read our documentation.

As always, we’re here to listen and would love to hear from you with any questions, comments, or feature suggestions.

The post SQL Server Management Studio 18.4 and sqlpackage v18.4 generally available appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2NkNJWX

[From Technet] Welcome to the 21st annual PASS Summit

PASS Summit has become an annual pilgrimage for thousands of SQL Server professionals from around the world to join together. My very first demo at PASS Summit was in 2010, and today marks the third time I've had the honor of being invited to deliver the day one keynote.

It has been incredible to reflect on how this event continues to support our community, providing unique opportunities for us to learn, connect, and share. The friends we make along the way, the networking and business opportunities that arise out of hallway meetings, the product announcements, and the technical deep dives are all focused on building and supporting the community. Because we are in this with you, PASS Summit provides us with the privileged opportunity to be able to listen to the community. To sit and understand your own firsthand experiences with the products and together embark on a journey of driving the industry forward to meet the expectations of an ever-changing tech landscape.

So to start off, a simple thank you to each one of you for spending the time, money, and energy to be a part of this amazing, open community of data friends and fans. It's going to be a great four days.

Microsoft Azure the PASS community

There are over 300,000 members of the PASS community, and you’re our closest peers and best advisors. Whether we see you on-site, online, or in one of the thousands of events being hosted by local and virtual chapters, each one of you has helped shape the way the world uses data.

Over the last twenty years of PASS Summits, we've worked side-by-side to drive innovation and provide solutions to companies around the globe. This is a great opportunity to hear directly from the people behind the products you love, as well as for you to share your own experiences and insights to shape future product roadmaps. This open exchange drives us to come back each year to deliver the latest product news, the best technical sessions and demos, to host open door Data Clinics, and connect with each of you at the social events.

Interesting facts about Microsoft at PASS Summit:

• Over 1,000 attendees visited the Microsoft booth in 2018
• Over100 Microsoft staffers attend across the engineering, support, and marketing organizations
• Over 15 years ago, Bob Ward and a few members of our CSS team started the Data Clinic with just their laptops and few whiteboards in a spare room
• 10 years ago, the Data Clinic became a force of its own when Mark Souza and the SQLCAT team joined forces with CSS to provide free one-on-one sessions with Microsoft experts, something few other events in the world provide.

And of course, there are the amazing SQL Saturday events. I've had first-hand experience with these over the years and the amount of learning that occurs during a one-day session is phenomenal. We encourage everyone to sign up for these events and leverage the knowledge and insights from our industry experts. Thank you to the community organizers and local PASS chapters for creating this invaluable avenue of learning for both new and seasoned data professionals.

Get prepared for the biggest and best experience yet

2019 marks a big year for data at Microsoft. We have a fantastic booth experience with five solution areas and combined Data Clinic that provides coverage across the entire Azure Data landscape. Come check out the great sessions by our own seasoned SQL pros Bob Ward, Connor Cunningham, Sanjay Mishra, and Slava Oks.

This year the team has also put together an unplugged session with key engineering leaders joined by some of the MVPs. You will hear us talk about the inner workings of the engineering team and how we collaborate with MVP's and our SQL Community.

Additionally, Gayle Sheppard, Corporate Vice President of Azure Data, will be involved in the Women in Technology events across the Summit, attending the day one keynote luncheon, and hosting a Women in Technology (WIT) focus group where she shares her professional perspective on the industry and imparts valuable insights into the data business.

Microsoft certification boot camps

We've partnered with PASS to bring you exclusive training, free practice tests, and discounted certifications. All attendees of each boot camp will receive a free Microsoft Official Practice Test.

Join me LIVE at the keynote!

I can't wait to share some monumental announcements in my action-packed keynote tomorrow where I will take you along the journey of SQL Edge to Cloud.

Join me at the Microsoft Azure Keynote tomorrow at 8:00 AM PST or live stream.

Learn about Microsoft's presence at PASS Summit today.

The post Welcome to the 21st annual PASS Summit appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/2Ci6Pq8

[From Technet] Gain intelligence over data with SQL Server 2019, now generally available

Today we're announcing that SQL Server 2019 is generally available. It's been an amazing journey with a tremendous response from customers. Ever since we announced preview of SQL Server 2019 at Ignite 2018, it has seen 50 percent more adoption than SQL Server 2017, our highest performing SQL Server release to date.

SQL Server 2019 delivers performance, security, and intelligence over all your data

SQL Server 2019 enables enterprises to gain intelligence over all data types, both structured and unstructured, by combining the power of new big data clusters with enhanced data virtualization. These powerful additions to SQL Server enable enterprises to not only store and query big data at scale but also combine it with structured data in whatever database it may reside such as SQL Server, Oracle, MongoDB, PostgreSQL, and others.SQL Server also includes built-in AI capabilities to enable comprehensive analytics and AI solutions for all your data needs.

Customers like Dr Foster, a Telstra and healthcare analytics company, are delighted to have this capability built into SQL Server 2019.

"Dr Foster required a solution that would help limit the amount of data movement, reduce duplication of data sets across the estate, and surface all of our business-critical relational data via a single access point – a unified data platform. With SQL Server 2019 Big Data Clusters, we are able to analyze our relational data in the unified data platform, leveraging Apache Spark, HDFS, and enhanced machine learning capabilities, all while remaining compliant." - George Bayliffe, Head of Data, Dr. Foster

SQL Server is an industry leader when it comes to performance. SQL Server 2019 builds on the mission of providing one of the most performant databases in the market by investing in key capabilities like intelligent query processing, which greatly improves query performance with no application changes, as well as accelerated database recovery which enables faster recovery times irrespective of the number or size of active transactions in the system.

Customers like Ita-Unibanco, one of Latin America's leading banks, have seen tremendous performance improvements by upgrading to SQL Server 2019:

"We are really pleased with the performance and results we achieved from using the new SQL Server 2019 features. The intelligent query processing feature has increased the speed and processing power of our business applications. With accelerated database recovery, long-running application transactions can roll back and be recovered in a matter of seconds which is a huge relief for our database administrators, saving us significant time and resources."- Edilson Andrade de Albuquerque, Manager of the Database Team, Ita-Unibanco

When it comes to security, SQL Server 2019 continues to be one of the most secure databases in the world according to the National Institute of Standards and Technology vulnerability assessment report*. But today, SQL Server 2019 takes security to a new level through Always Encrypted with secure enclaves, which provides the ability to protect your most sensitive data while still allowing robust operations to be executed without losing encryption.

* Data from theNational Institute of Standards and Technology vulnerability assessment report.

 

Hiscox, a business insurance provider, trusts SQL Server 2019 to protect their sensitive customer data:

"With secure enclaves in SQL Server 2019, we can now enforce highly restricted client application-based access to our customers' most sensitive data without inhibiting our real-world data handling and analysis requirements." – Victoria Silversmith, Technical Design Authority Data, Hiscox

Best of all, customers also gain access to all of the new features with no change to the license cost for SQL Server, and customers with Software Assurance will see even greater benefits including a free disaster recovery replica in Azure for business continuity. Learn more about the new Software Assurance benefitsand find more details on SQL Server 2019 features in the SQL Server 2019 technical blog.

Get started with SQL Server 2019 on Azure

More companies are finding that SQL Server is best on Azure, and it's easy to see why. Customers running SQL Server in Azure gain access to innovation for their SQL workloads including harnessing the power of AI to tune and secure their databases, evergreen SQL that never needs to be patched or upgraded with Azure SQL Database, and limitless database storage from Azure SQL Database Hyperscale. Analyst firm GigaOm recently published a report comparing SQL Server performance on AWS RDS to Azure SQL Database. They found SQL Database to be the price-performance leader for mission-critical workloads while costing up to 86 percent less than AWS RDS.¹

Please find more information on performance and price-performance comparisons in the GigaOm report.

Try SQL Server 2019

• On Azure Virtual Machines
• On-premises

---

¹Price-performance claim based on data from a study commissioned by Microsoft and conducted by GigaOm in August 2019. The study compared price performance between a single, 80 vCore, Gen 5 Azure SQL Database on the business-critical service tier and the db.r4.16xlarge offering for SQL Server on AWS RDS. Benchmark data is taken from a GigaOm Analytic Field Test derived from a recognized industry standard, TPC Benchmark E (TPC-E), and is based on a mixture of read-only and update intensive transactions that simulate activities found in complex OLTP application environments. Price-performance is calculated by GigaOm as the cost of running the cloud platform continuously for three years divided by transactions per second throughput. Prices are based on publicly available US pricing in East US for Azure SQL Database and US East (Ohio) for AWS RDS as of August 2019. Price-performance results are based upon the configurations detailed in the GigaOm Analytic Field Test. Actual results and prices may vary based on configuration and region.

The post Gain intelligence over data with SQL Server 2019, now generally available appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/34ve9uN

[From Technet] SQL Server 2019 is now generally available

As you saw from our launch announcement earlier today, over a year ago at Microsoft Ignite we announced our first public preview of SQL Server 2019 and today our latest release is now generally available.

You have told us that in today's demanding world of massive data, wide variety of data sources, and expectations of near real-time application and query performance you need more than just a database engine. You need a modern data platform.

SQL Server 2019 encompasses all of this in one product, SQL Server 2019 brings enhancements in the core SQL engine, offers a scale-up and scale-out system with built in support for Big Data (Apache Spark, Data Lake), state of the art data virtualization technology, and with built-in machine learning capabilities.

SQL Server 2019 is designed to solve challenges of the modern data professional including:

• Store enterprise data in a data lake and offer SQL and Spark query capability overall data
• Reduce the need for Extract, Transform, and Load (ETL) applications by eliminating data movement
• Integrate and secure machine learning applications with scalable performance
• Reduce the need for application and query changes to gain a boost in performance
• Increase confidential computing of data through hardware enclaves
• Increase application and database uptime and availability through features like ADR (Advanced Database Recovery)
• Extend the power of the T-SQL language in a secure and robust fashion
• Run applications and deploy databases across multiple operating systems and platforms with compatibility
• Reduce the risk of upgrades while using new SQL capabilities when you are ready though inbuilt database compatibility levels

Unified data platform

Enterprises today use multiple data platforms to meet their business needs. This ranges from operational databases, to data-marts, and to Big Data platforms. These platforms have different security models and tools ecosystems, which often require different skill sets and domain expertise. SQL Server 2019 offers all these capabilities as a unified data platform.

Customers can choose to deploy SQL Server in a traditional pattern. SQL Server 2019 also supports big data cluster deployment that comes with additional capabilities of big data, data virtualization, data-mart and enterprise data lake.

Big Data Clusters come with an easy to use deployment and management experience through theazdatacommand line tool, or the GUI toolAzure Data Studio. Azure Data Studio is a cross platform, modern tool built on top of VS Code. ADS provides experiences to access Big Data Clusters through interactive dashboards, and also includes rich SQL and Jupyter Notebooks experiences.

Azure Data Studio offers a unified view across all enterprise data both on-premises and in the cloud. You can view all your SQL Servers, data marts, data lakes and any external data sources that you wish to virtualize.

In addition to supporting connections to many data sources, Azure Data Studio has a built-in Jupyter notebook experience. You can use notebooks to access, explore, manipulate, visualize, and model against the built-in Apache Spark experience for SQL Server Big Data Clusters, but what I am most excited about is how we also light up all these capabilities on top of the native SQL Server engine. Using the SQL kernel, you can create rich interactive experiences using T-SQL against any version or edition of SQL Server, on-premises on in the cloud. You can program in Python, SQL, Powershell, Scala, and R. It is such a powerful concept that we have started to convert our documentation, deployment experiences, and troubleshooting manuals into interactive notebooks. We will continue to invest in innovative ways to incorporate this new paradigm into our data experience.

Azure Data Studio comes with many other rich features that make it the most powerful single tool to work across relational databases, data virtualization, big data, and built-in virtualization. Through custom dashboards, intelligent visualizations, and a highly extensible platform, we can continue to bring innovative and modern data experiences into Azure Data Studio such as the SandDance visualizer seen below.

Azure Data Studio is on GitHub so that you can see the innovative progress being made in this evolving tool. To learn more about the capabilities of Azure Data Studio, read our documentation.

Intelligence over all of your data

Enterprises today have data scattered across a variety of platforms and data sources but need to access this data in a consistent manner. This has led to the need to build complex and expensive Extract, Transform, and Load (ETL) applications often moving the data into a central database platform like SQL Server.

SQL Server 2019 has solutions for these challenges by providing data virtualization. Applications and developers can use the familiar and consistent language of T-SQL through external tables to access structured and unstructured data from sources like Oracle, MongoDB, Azure SQL, Teradata, and HDFS. SQL Server 2019 eases the burden of establishing these data sources with built-in driver support. Polybase extends this functionality allowing you to access a host of other data sources using the ODBC driver of your choice. Because external tables act and look like tables you can join data across these sources with local SQL Server tables to provide a seamless experience. In addition, external tables provide some of the same capabilities as local SQL Server tables including using familiar security and object management techniques.

SQL Server 2019 extends the functionality of Polybase by providing a complete analytics platform powered by Big Data Clusters. In addition, a Big Data Cluster deployment includes a Data Pool which can be used to build a data mart of cached results from queries from external tables across or outside the cluster or data directly ingested from sources such as IoT data.

As mentioned earlier, Big Data Clusters come with Apache Spark built-in providing an end-to-end, secure machine learning platform using technologies such as SparkML and SQL Server Machine Learning Services. Data Scientists now have a complete system to train and prepare machine learning models using external table data sources in and outside the cluster. These models can then be deployed and consumed as applications using a RESTful Web Service compliant with Swagger applications.

“Building and deploying our vertical AI-solution for clinical radiology combines very diverse implementation paradigms, data formats and regulatory requirements. SQL Server 2019 big data clusters allowed us to accommodate and integrate all aspects from one shared platform – for our data scientists with their deep learning as well as for our software engineers who wire up workflows, security and scalability. At runtime, our healthcare customers benefit from simple containerized deployment and maintenance while being able to move our solution between on-prem and the cloud easily.” Read more in our Balzano customer story.

Ren Balzano, Founder and CEO, Balzano

Industry leading intelligent performance

SQL Server provides one of the most powerful query engines proven with industry leading benchmarks. Today new TPC benchmarks have been announced continuing to prove SQL Server has unparalleled performance in the industry.

Developers and data professionals need more. They need a database engine that can adapt to their query workloads and reduce time for expensing performance tuning. Users expect to migrate to the latest release of SQL Server and gain performance without having to make major application changes. When they have to analyze query performance they need deep insights anytime and anywhere.

We recently performed a performance test on a SQL Server 2019 instance running on a Windows Server 2016 running on an 8-Socket (224 cores, 12TB RAM and 200TB+ SSD storage) Lenovo Server (ThinkSystem SR950) using Intel Cascade Lake processors. We generated data and loaded a single table (LINEITEM, TPCH database schema with 145TB+ raw data). This table had 54TB compressed data with 1 Trillion+ rows. Q1 query (as defined by TPCH) which scans the whole table and selects nearly all the rows for computation was run in both cold (all data read from storage) and warm (data in memory) scenario. SQL Server 2019 was able to provide unparalleled performance processing over a trillion rows with the query completing in under 2 mins (107 secs) in warm cache and under 4 mins (238 secs) with all data read from storage. For the warm cache, this translates to processing over 6 billion rows/sec and read throughput of around 50GB/sec. You can find the Azure Data Studio Notebooks that showcase both scenarios on GitHub.

SQL Server 2019 includes built-in query processing capabilities called Intelligent Query Processing. By updating your database compatibility level to 150 (the default level for SQL Server 2019), the query processor in the SQL Server engine can enhance performance through capabilities like batch-mode on row store, scalar UDF inlining,or table variable deferred compilation. It canautomatically correct memory-related query execution issues through memory grant feedback. No query or application changes are required for a boost in performance.

SQL Server also provides optimized in-memory capabilities without changing your application through the support of persistent memory and optimized tempdb metadata access. Tempdb now just runs faster.

Because in some situations you need immediate access to query performance insights, SQL Server 2019 enables lightweight query profiling by default including the ability to access the last actual query execution plan. For deeper insights over time, enable the query store and enable historical query plan performance analysis including the ability to have SQL Server automatically correct query plan regressions.

A list of all the performance improvements in SQL Server 2019 is available.

Mission critical security and availability

A modern data platform must provide confidential computing through software that does not expose vulnerabilities and features to secure your data. SQL Server 2019 for the last 9 years has been the least vulnerable database product in the industry according to NIST (National Institute of Standards and Technology Comprehensive Vulnerability Database).

Built on the foundation of security capabilities like row-level security and dynamic data masking, SQL Server 2019 provides features that meet the security needs of modern applications through always encrypted with secure enclaves. Enclaves allow for data to be only available in an unencrypted state in the enclave memory space on the server. This provides control over the unencrypted data while enabling rich query computing and indexing. SQL Server 2019 also provides a new T-SQL interface to classify your data assisting you to meet compliance standards such as GDPR. Because classification is now built into the engine, SQL Server audit can be used to track users who access classified data.

Keeping your data available at all times is critical to any data application. Data professionals for years have struggled with managing transaction log growth and application availability due to long running or long open active transactions. SQL Server 2019 now provides accelerated database recovery to overcome these challenges with no application changes required. Using a database option, SQL Server will use a Persisted Version Store to track changes allowing for rollback and undo recovery to execute faster than it takes to observe changes. Transaction log truncation is no longer dependent on active transactions. Accelerated database recovery is based on the work done for Azure SQL Database (read the whitepaper on the topic recently presented at the VLDB 2019 conference) and is one of the key technologies behind the amazing performance of Hyperscale in Azure.

Platform and language of choice with compatibility

SQL Server rocked the industry by bringing SQL Server to Linux with SQL Server 2017. Using the innovative technology of the SQLPAL, SQL Server on Linux has application and database compatibility with Windows. Backup a database on Windows and restore it on Linux with no application changes. SQL Server 2019 continues the Linux journey by offering new capabilities such as Replication, Distributed Transactions, Polybase, and Machine Learning Services.

Because SQL Server has embraced Linux, the community has already seen the benefits of using SQL Server containers including simplified software patching, consistency, and easy integration into Continuous Integration/Continuous Deployment (CI/CD) pipelines. Customers now see the benefit of using SQL Server with containers in production. SQL Server 2019 embraces this need by offering containers based on Red Hat Enterprise Linux images and running by default as non-root. This enables SQL Server containers to be officially supported on the popular Kubernetes platform RedHat OpenShift.

Developers also need to use the language of their choice with a compatible data platform. SQL Server supports a variety of popular languages and providers such as C#, Java, node.js, PHP, Ruby, and Go. In addition, SQL Server 2019 also allows developers to extend the T-SQL language through Java classes with SQL Server Language Extensions using the extensibility framework, the same architecture that powers SQL Server Machine Learning Services.

The voice of the customer

Everything poured into SQL Server is based on customer experiences. Listening to our customers is the only way to provide solutions that meet real-world challenges. SQL Server 2019 addresses key customer feedback areas such as:

• Improved context for errors such as string truncation
• New diagnostics for supportability
• Query store enhancements
• Deep engine performance improvements.

Read about all the SQL Server 2019 improvements in our documentation.

The post SQL Server 2019 is now generally available appeared first on SQL Server Blog.

from SQL Server Blog https://ift.tt/32cIAEr