Here’s just some of what you’ll find in the ultimate guide to SQL Server 2017 (spoiler alert—get free access to these chapters below):

Database server components

Even if you have a firm foundation in SQL Server configuration and administration, you want to keep the basics at-hand so you can easily refresh your memory. SQL Server 2017 Administration Inside Out provides a solid foundation in the infrastructure that makes up a database and how it works. The guide also offers helpful tips and best practices every DBA should know, like:

• Keep in mind that In-Memory OLTP requires RAM overhead that’s two times the size of a memory-optimized object.
• Disable power saving everywhere. Power-saving settings in your operating system (OS) can result in poor query performance. Turn on High Performance at the OS and BIOS levels.
• Realize that Redundant Array of Independent Disks (RAID) shouldn’t take the place of performing backups. It doesn’t provide 100 percent protection from data loss.
• Ensure all TCP/IP traffic to and from SQL Server is encrypted. If you’re using the Shared Memory Protocol with applications that are all on the same server, this isn’t required.

Securing the server and data

Recognizing that more frequent and complex cyberattacks are targeting your environment, SQL Server 2017 Administration Inside Out covers the security capabilities of SQL Server 2017 comprehensively. The guide helps you play defense to protect your data from attack and minimize the damage should you suffer a data breach. For example, you’ll learn (or be reminded) that you can:

• Prevent common SQL injection attacks by making sure that all data is escaped, sanitized, and validated before input and that all SQL Server queries are parameterized.
• Use newer capabilities like Row-Level Security to restrict access through security policies based on group membership or execution context.
• Start SQL Server in single-user mode or with minimal configuration and remove the offending audit if that audit shuts down your SQL Server instance.

Performance tuning SQL Server

Ensuring your database performs at top speed is one of your highest priorities. You want to have an accurate and complete understanding of performance tuning concepts, the objects typically associated with tuning SQL Server database performance, and best practices that help your databases run at top speed. In SQL Server 2017 Administration Inside Out, you’ll gain this critical knowledge, including:

• The NOLOCK hint (or READ UNCOMMITTED isolation level) can return invalid data. Not only can uncommitted data be read, but committed data can be read twice or skipped. You also run the risk of returning corrupt data or finding the query has failed.
• You can use the Query Store feature to help you analyze execution plan performance by looking at live Query Store data as it happens as well as at the history of statement execution. Query Store is one of the features that first appeared in Azure SQL Database and then moved to SQL Server on-premises.

All this info—all in one place

This is only a small sample of the in-depth information you can find in SQL Server 2017 Administration Inside Out. Take the book for a test drive by downloading a free custom excerpt, including the three full chapters previewed above.

With this excerpt, you’ll also get an exclusive 50-percent discount code for the full 14-chapter e-book.

Download the free excerpt from SQL Server 2017 Administration Inside Out today.

The post DBA essentials—SQL Server 2017 security, performance tuning, and more—inside out appeared first on Microsoft SQL Server Blog.

mssql-cli is a new and interactive command line query tool for SQL Server. This open source tool works cross-platform and is part of the dbcli community.

Feature highlight: Special commands

In our initial release, mssql-cli brought auto-completion, syntax highlighting, configuration support, and history to your SQL Server command line experience. Bringing more features over from PGCLI, we introduce special commands.

Special commands are meant to make your life easier. They are shortcuts to perform common tasks and queries. All special commands start with a backslash (\), and you can use the built-in IntelliSense to see a list of special commands you can use. You can also learn more by running the following command:

$ mssql-cli>\?

To start off, let’s say you have a common query you run often, but don’t want to type it out each time or scroll through your history. You can “save” your query by running the following special command:

$ mssql-cli>\sn address select * from "Person"."AddressType"

To execute this query, simply run:

$ mssql-cli>\n address

To see this in action, see the gif below:

To learn more about special commands, please check out usage guide, which also includes information about other features that you can use with mssql-cli.

Getting started

In order to install mssql-cli, you must have Python on your machine. Please refer to the installation guide for OS-specific methods to get Python.

mssql-cli is installed via pip. If you know pip, you can install mssql-cli using command.

$ pip install mssql-cli

Once you have mssql-cli installed, connect to your database with the command:

$ mssql-cli -S <server name> -U <user name> -d <database name>

Please try out mssql-cli and submit any feature requests and issues on our Github issues page. You can also keep track of any future updates by following our Twitter @sqldatatools.

The post Announcing a new update for mssql-cli: Special Commands appeared first on Microsoft SQL Server Blog.

We are excited to announce the February release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our GitHub.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this February release is the third major update since the announcement. If you missed it, the January release announcement is available here.

The February release includes several major repo updates and feature releases, including:

• Added Auto-Update Installation feature
• Added Connection Dialog ‘Database’ Drop-down
• Added functionality for new query tabs keeping active connection
• Fixed bugs in SQL Editor and auto-completion

For complete updates, refer to the Release Notes.

Auto-Update Installation

We want to use the February Insiders builds to test the auto-update feature. The 0.26.2 build will be released as an auto-update to 0.26.1 (assuming there are no issues that require publishing a new build to successfully support auto-update).

To discover updates faster, we have added auto-update functionality to ensure you have the latest version of SQL Operations Studio. A notification will pop up on the gear icon on the bottom left as shown in the image above. In addition, you can also check for updates by clicking Help on the toolbar.

Connection Dialog Database Drop-down

The Connection Dialog Database field is now a dynamically populated drop-down list that will contain a list of databases populated from the specified server.

New query tabs keeping active connection

A top voted feature by our users was for new query tabs to keep their active connection. In the February release, users can now click on a server name and press Ctrl + N, which will create a new query tab and retain the active connection instead of having to manually set the connection. This can be seen in the gif below.

Contact us

If you have any feature requests or issues, please submit to our GitHub issues page. For any questions, feel free to comment below or tweet us @SQLOpsStudio.

The post The February release of SQL Operations Studio is now available appeared first on Microsoft SQL Server Blog.

We are excited to announce the January release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the release notes to get started. SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB, and SQL DW from Windows, macOS, and Linux. To learn more, visit our Github.

SQL Operations Studio was announced for public preview on November 15, 2017 at Connect(), and this January release is the second major update since the announcement. If you missed the December release announcement, you can learn more on the SQL Server blog.

The January release includes several major repo updates and feature releases, including:

• Enable the HotExit feature to automatically reopen unsaved files.
• Add the ability to access saved connections from Connection Dialog.
• Set the SQL editor tab color to match the Server Group color.
• Fix the broken Run Current Query command.
• Fix the broken pinned Windows Start Menu icon.

For a complete list of updates, please refer to the release notes.

HotExit feature

A highly requested feature for SQL Operations Studio is to remember unsaved changes when the program is exited, similar to VSCode and other editors. We are excited to announce that HotExit has been enabled as demonstrated in the GIF above. SQLQuery2 represents an unsaved query, and the user can quit SQL Operations Studio and simply reload it again to retain the unsaved query.

This feature is not enabled by default. To enable HotExit by default, go to Settings with Ctrl + , and  copy “files.hotExit”: “onExit” into your settings.

Saved connections available in Connection Dialog

The Connection Dialog now contains a “Saved Connections” tab next to “Recent Connections”. This contains a list of saved server connections from the server’s “Object Explorer” viewlet, as well as the Server Group.

SQL editor tab color to match Server Group color

SQL query editor and dashboard windows will have the same color as the Server Group used to open the window. This feature is not enabled by default. To enable Tab Colors by default, go to Settings with Ctrl + , and  copy “sql.tabColorMode”: “fill” into your settings.

Contact us

If you have any feature requests or issues, please submit them to our Github issues page. For any questions, feel free to comment below or tweet us @sqlopsstudio.

The post The January release of SQL Operations Studio is now available appeared first on Microsoft SQL Server Blog.

We are excited to announce the December release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our GitHub.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this December release is the first major update since the announcement.

The December release includes several major repo updates and feature releases, including:

• Migrating SQL Ops Studio Engineering to public GitHub repo
• Azure Integration with Create Firewall Rule
• Windows Setup and Linux DEB/RPM installation packages
• Manage Dashboard visual layout editor
• “Run Current Query with Actual Plan” command

For complete updates, refer to the Release Notes.

Migrating SQL Ops Studio Engineering to public GitHub repo

To provide better transparency with the SQL Operations Studio community, we have decided to migrate the Github internal branch to the public repo. This means any bug fixes, feature developments, or even test builds can be publicly viewed before an update is officially announced.

We made this move because we want to collaborate with the community to continually deliver features that our users want. This gives you the opportunity to see our changes in action to address your top voted issues. Visit our GitHub page and give us your feedback.

Azure Integration with Create Firewall Rule

Now let’s get into new features. A common issue when connecting to Azure SQL DB instances is that the connection can fail due to server firewall rules. This would require loading Azure Portal to configure firewall rules so that you can connect to your database, which can be inconvenient.

To speed up this process, we have enabled Azure Integration with Create Firewall Rule dialog. When your connection to an Azure SQL DB instance fails because of firewall settings, this dialog will appear, allowing the user to use their Azure subscription account to automatically configure the client IP address with the server. This retains the same experience as configuration on Azure Portal, except you can do it all through SQL Operations Studio.

Windows Setup installation and Linux DEB/RPM installation packages

We are always looking for new ways to improve the installation experience. With the December release, we have added Windows Setup installation to simplify installation on Windows. This wizard will allow the user to:

• Select installation location
• Select start menu folder
• Option to add to path

In addition to Windows Setup, we have also added Linux DEB/RPM installation packages. These will add new ways for Linux users to download SQL Operations Studio for their choice of installation.

Feel free to try out these new installation experiences on our download page.

Manage Dashboard visual layout editor

In the initial release, there were not many options to customize the visual layout of the dashboards. With the December release, you can now resize and move your widgets by enabling the visual layout editor mode by clicking the pencil on the top right of the Manage Dashboard screen. This gives users greater control of their dashboard in addition to building their own custom insight widgets.

Run Current Query with Actual Plan command

Another new feature we have enabled is Run Current Query with Actual Plan, which is a command that will execute the current query and return the actual execution plan with the query results. This feature area is still in-progress as we work through the best UX for integrating this command directly into the query editor. While that design work is in-progress the functionality is still available via the Command Palette and you can define a keyboard shortcut if using this feature frequently.

Contact us

If you have any feature requests or issues, please submit to our GitHub issues page. For any questions, feel free to comment below or tweet us @sqlopsstudio.

The post The December release of SQL Operations Studio is now available appeared first on Microsoft SQL Server Blog.

What’s the difference between graph and relational databases?

Relational databases, like SQL Server, use foreign keys to manage relationships between entities and tables. Foreign keys adequately query one-to-many relationships; however, as relationships between various data entities become more complex, queries also become more complex and performance may diminish as a result.

In these cases, developers may opt for graph database models to manage complex relationships and enable operational agility. In a graph database, edges are heterogeneous in nature—a single edge can be used to connect different type of nodes to each other. This is not easy to achieve using foreign keys in a relational database. For example, consider a social graph where a person (node) likes another person (edge) or organization (node) or restaurant (node). Here the same ‘likes’ edge is used to connect three different types of nodes and entities to each other—person to person; person to organization; and person to restaurant.

So, how do graph databases work?

Graph databases are comprised of a collection of nodes and edges. Nodes represent a given entity (a noun), while edges (a verb) represent the relationship between the two nodes they connect. For example, pitcher Randy Johnson (node) faces (edge) batter John Kruk (node).

When do you choose a graph database model over a relational model?

While graph and relational schema are capable of achieving the same ends, graph models make it easier to express certain queries. Since it’s easier to manage many-to-many relationships, graph models are better suited for applications where relationships between data entities are the focus. They’re also better for applications that need to traverse connected data sources to generate insights, such as applications with hierarchical data where the queries may involve traversing the hierarchies. Graph databases allow you to express traversal or navigation queries easily, using join-free pattern matching.

The T-SQL Graph extension offers the best of both worlds.

If you’ve already licensed SQL Server 2017, there’s no need to purchase a second system to manage more complex database needs. SQL Server 2017 fully integrates with Transact-SQL (T-SQL) to create and query graph objects. Maintain your existing SQL Server database and benefit from pairing graph capabilities with other SQL Server technologies like columnstore, HA, R services, and others. SQL graph database also supports all the security and compliance features available with SQL Server.

Download and try SQL Server 2017 on the platform of your choice today, and see for yourself.

The post Bring clarity to complex data with graph databases on SQL Server 2017 appeared first on Microsoft SQL Server Blog.

Read on to learn more about mssql-cli features, how to submit feature requests or issues, and our open source collaboration story to bring you this great tool.

mssql-cli auto-completion that is context aware

Features

Mssql-cli is a new and interactive command line tool that provides the following key enhancements over sqlcmd in the Terminal environment:

• T-SQL IntelliSense
• Syntax highlighting
• Pretty formatting for query results, including Vertical Format
• Multi-line edit mode
• Configuration file support

Mssql-cli aims to offer an improved interactive command line experience for T-SQL. It is fully open source under the BSD-3 license, and a contribution to the dbcli organization, an open source suite of interactive CLI tools for relational databases including SQL Server, PostgresSQL, and MySQL. The command-line UI is written in Python and the tool leverages the same microservice backend (sqltoolsservice) that powers the VS Code SQL extension, SQL Operations Studio, and the other Python CLI tool we announced earlier, mssql-scripter.

mssql-cli multi-line queries to build readable queries. Also includes syntax highlighting and “smart” auto-completion.

Try it now

In order to install mssql-cli, you must have Python on your machine.

Mssql-cli is installed via pip. If you know pip, you can install mssql-cli using command.

$ pip install mssql-cli

This command may need to run as sudo if you are installing to the system site packages. Mssql-cli can be installed using the –user option, which does not require sudo.

$ pip install --user mssql-cli

Once you have mssql-cli installed, connect to your database with the command:

$ mssql-cli -S <server name> -U <user name> -d <database name>

Contact us

We are open to any questions, feedback, or any feature suggestions for future releases, which can be submitted on our GitHub Issues.

Our Open Source Story

Over the years, customers have told us we need to modernize sqlcmd. This feedback got significantly amplified after SQL Server 2017 was released on Linux and macOS (Docker). Customers asked us for richer experiences on the command line including tab completion for T-SQL keywords and database objects and syntax highlighting.

Thus, the SQL Developer Experiences team set out to create a modern version of sqlcmd. As we worked through our scenarios, we discovered an awesome and immensely popular set of open source command line tools for databases on GitHub at https://github.com/dbcli maintained by the dbcli organization. The repo contains open source cross-platform CLI tools for databases, which support “smart” auto-completion, syntax highlighting and easy installation. We were particularly impressed with the pgcli and mycli tools, as well as how active and passionate their community was in creating better database cli tools.

We reached out to Amjith Ramanujam, the founder of the dbcli organization, did an in-depth demo, explained what we were trying to do, and explored if mssql-cli could be included in the dbcli family. Simply put, Amjith and the other maintainers of the project were delighted and welcomed us with open arms!

This is a momentous occasion for the SQL Developer Experiences team because it represents the first time our team is contributing source code to an existing open source organization with a commitment to be a good citizen in an existing open source community. It is also an exciting moment to build an awesome tool by collaborating with the OSS community, which we will continue to do in the future.

The post Try mssql-cli, a new interactive command line tool for SQL Server appeared first on Microsoft SQL Server Blog.

SQL Server continues to be a proven leader in database performance for both analytic and OLTP workloads. SQL Server 2017 is fast, built-in with capabilities and features such as Columnstore indexes to accelerate analytic performance and Automatic Tuning and Adaptive Query Processing to keep your database application at peak speed.

Recently, Hewlett Packard Enterprise (HPE) announced a new world record TPC-H 10TB benchmark result¹ using SQL Server 2017 and Windows Server 2016 on their new DL580 Gen10 Server. This new amazing result at 1,479,748 Composite Query-per-Hour (QphH) was achieved at price/performance of .95 USD per QphH continuing to show SQL Server’s leadership in price and performance.

HPE also announced the first TPC-H 3TB result² on a 2-socket system using SQL Server 2017 and Windows Server 2016 with their DL380 Gen Server. They achieved a stellar 1,014,374 QphH on only 2-sockets. These results continue to show how powerful SQL Server can be to handle your analytic query workloads including data warehouses.

SQL Server also is a proven leader for OLTP workloads. Lenovo recently announced a new world-record TPC-E benchmark result³ using SQL Server 2017 and Windows Server 2016. This is now the #1 TPC-E result in both performance at 11,357 tpsE and price/performance at 98.83 USD per tpsE for systems with 4 sockets or more. This result was achieved on Lenovo’s ThinkSystem SR950 server using 4 sockets at 112 cores which represents a 25% performance gain from the previous 4 socket result with 16% more cores.

SQL Server 2017 is the world leader in TPC-E and TPC-H performance, price, and value and continues to demonstrate it is one of the fastest databases on the planet, in your cloud or ours.

References

• ¹ 10TB TPC-H non-clustered result as of November 9th, 2017.
• ² 3TB TPC-H non-clustered result as of November 9th, 2017.
• ³ TPC-E benchmark result as of November 9th, 2017.

The post SQL Server 2017: A proven leader in database performance appeared first on Microsoft SQL Server Blog.

We are excited to announce that SQL Operations Studio is now available in preview. SQL Operations Studio is a free, light-weight tool for modern database development and operations for SQL Server on Windows, Linux and Docker, Azure SQL Database and Azure SQL Data Warehouse on Windows, Mac or Linux machines.

Download SQL Operations Studio to get started.

It’s easy to connect to Microsoft SQL Server with SQL Operations Studio and perform routine database operations—overall lowering the learning curve for non-professional database administrators who have responsibility for maintaining their organization’s SQL-based data assets.

As more organizations adopt DevOps for application lifecycle management, developers and other non-professional database administrators find themselves taking responsibility for developing and operating databases. These individuals often do not have time to learn the intricacies of their database environment, making hard to perform even the most routine tasks. Microsoft SQL Operations Studio takes a prescriptive approach to performing routine tasks, allowing users to get tasks done fast while continuing to learn on the job.

Users can leverage their favorite command line tools (e.g. Bash, PowerShell, sqlcmd, bcp and ssh) in the integrated terminal window right within the SQL Operations Studio user interface. They can easily generate and execute CREATE and INSERT scripts for SQL database objects to create copies of their database for development or testing purposes. Database developers can increase their productivity with smart T-SQL code snippets and rich graphical experiences to create new databases and database objects (such as tables, views, stored procedures, users, logins, roles, etc.) or to update existing database objects. They also have the ability to create rich customizable dashboards to monitor and quickly detect performance bottlenecks in your SQL databases on-premises or in Azure.

SQL Operations Studio comes at an opportune time for users who use clients running macOS or Linux. Many users who use or plan to deploy SQL Server 2017, which became generally available in September 2017, also use Macs as their clients. They will now be able to use a free database development and operations tool from Microsoft that runs natively on their OS of choice.

SQL Operations Studio has been forked from Visual Studio Code with the objective to make it highly extensible. It’s built on an extensible microservices architecture and includes the SQL tools service built on .NET Core. Users will be able to download it from GitHub or Microsoft.

Eric Kang has done some great overview videos. Check out the latest one on Channel 9, below, or attend one of the Connect() pre-recorded sessions going on this week.

We hope you love this new tool.  It’s received great reviews from the community testing it in private beta and, with your feedback, we can make it even better. Join us in improving SQL Operations Studio by contributing directly at the GitHub repo.

If you have questions or would like to add comments, please use the comments section below. We would love to hear from you!

The post Announcing SQL Operations Studio for preview appeared first on Microsoft SQL Server Blog.

At Microsoft, we’re dedicated to helping both enterprises and individuals realize their full potential. Our industry position in Operational DBMS is due to the unequaled capabilities of SQL Server.

The release of SQL Server 2017 brings the power of SQL Server to Windows, Linux, and Docker containers for the first time ever. Developers are able to build intelligent applications using preferred languages and environments, while enjoying in-memory performance across workloads, mission-critical high availability, and in-database advanced analytics. You can develop once and deploy anywhere in a consistent experience across your datacenter and public cloud.

SQL Server proves itself, year over year, to be the least vulnerable DBMS in the industry. Built for security from the ground up, SQL Server offers customers a layered protection approach that incorporates encryption, authentication, and monitoring and auditing at the disk, database, and application levels. Innovative security technologies like Always Encrypted, for encryption at rest and in motion, help transform global operations for the better.

Perhaps most noteworthy for organizations living in the real world of cost-benefit analyses, SQL Server 2017 remains one of the most cost-competitive DBMS offerings in the enterprise space. In fact, you can get all the robust business capabilities in SQL Server 2017 built-in to a single product SKU, without expensive add-ons — for one great, low total cost of ownership.

But don’t just take our word for it. We encourage you to take the time to read the full Gartner report.

And then take a moment to see how you can get free licenses when you migrate to SQL Server 2017. We’re confident you’ll find the industry-leading database you know and love — now across operating systems and application platforms, on-premises and in the cloud.

Gartner does not endorse any vendor, product or service depicted in its research publications, and does not advise technology users to select only those vendors with the highest ratings or other designation. Gartner research publications consist of the opinions of Gartner’s research organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose.

This graphic was published by Gartner, Inc. as part of a larger research document and should be evaluated in the context of the entire document. The Gartner complete document is available now. 

The post Three years in a row – Microsoft is a leader in the ODBMS Magic Quadrant appeared first on Microsoft SQL Server Blog.