quinta-feira, 18 de janeiro de 2018

[From Technet] Webinar: Modernize your applications with cloud and on-premises data solutions from Microsoft

Customers today demand the latest innovations in every solution you deliver. How can you make sure your data infrastructure not only keeps up, but drives innovation?

Data is the core of modern applications. Two key trends that help organizations extract the most from their data are the adoption of cloud technologies and the ability to drive new customer experiences with artificial intelligence. Organizations that modernize and harness data, cloud, and AI outperform their competition and are becoming leaders in their field. The most digitally transformed enterprises earn an additional $100 million in operating income!

Join our speakers Claudia Backus, Prem Prakash, and Frederico Rezende for a webinar on how you can transform your applications and enable new customer experiences using the Microsoft data platform.

In this webinar, you’ll learn:

  • How to leverage the performance, security and flexibility of the entire Microsoft database portfolio from SQL Server 2017 and Azure SQL Database to open-source databases like Azure Database for MySQL and Azure Database for PostgreSQL.
  • How to accelerate your move towards a cloud-based application with the new Azure Database Migration Service.
  • How the Microsoft Data Accelerator program can help you modernize your apps across on-premise and cloud.

Register now for this webinar to find out how Microsoft can help you successfully transform your existing applications and ultimately become a SaaS provider with cloud technologies.



from SQL Server Blog http://ift.tt/2DLBGeT

quarta-feira, 17 de janeiro de 2018

[From Technet] The January release of SQL Operations Studio is now available

This blog post was authored by Alan Yu, Program Manager, Microsoft SQL Server.

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-main

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

hotexit

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

saved-connections

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-editor-tab-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.enableTabColors”: true 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.



from SQL Server Blog http://ift.tt/2EO3QFl

segunda-feira, 15 de janeiro de 2018

T-SQL Query to list all indexes and their filegroups

I was fishing for a query to list all indexes and their FileGroups, so I could locate the ones that were created incorrectly and re-create them in their right place.

Ended up finding the query bellow at Technet by a Mr. Olaf  Helper. Mr. "Helper" was a great help indeed (ba dum tsss).


SELECT DS.NAME              AS DataSpaceName, 

       AU.type_desc         AS AllocationDesc, 
       AU.total_pages / 128 AS TotalSizeMB, 
       AU.used_pages / 128  AS UsedSizeMB, 
       AU.data_pages / 128  AS DataSizeMB, 
       SCH.NAME             AS SchemaName, 
       OBJ.type_desc        AS ObjectType, 
       OBJ.NAME             AS ObjectName, 
       IDX.type_desc        AS IndexType, 
       IDX.NAME             AS IndexName 
FROM   sys.data_spaces AS DS 
       INNER JOIN sys.allocation_units AS AU 
               ON DS.data_space_id = AU.data_space_id 
       INNER JOIN sys.partitions AS PA 
               ON ( AU.type IN ( 1, 3 ) 
                    AND AU.container_id = PA.hobt_id ) 
                   OR ( AU.type = 2 
                        AND AU.container_id = PA.partition_id ) 
       INNER JOIN sys.objects AS OBJ 
               ON PA.object_id = OBJ.object_id 
       INNER JOIN sys.schemas AS SCH 
               ON OBJ.schema_id = SCH.schema_id 
       LEFT JOIN sys.indexes AS IDX 
              ON PA.object_id = IDX.object_id 
                 AND PA.index_id = IDX.index_id 
ORDER  BY DS.NAME, 
          SCH.NAME, 
          OBJ.NAME, 
          IDX.NAME 



Using this query I was able to locate the indexes (clustered and nonclustered) that were incorrect and placed them (actually drop and re-create them into their righteous location). 

Although creating multiple files "per se" is not a performance improvement garantee, you need to test it in your case: http://community.idera.com/blog/b/community_blog/posts/increase-sql-server-performance-using-multiple-files

Messy work, but indeed necessary if your boss has OCD. You can create another FileGroup and add files to it using this:


ALTER DATABASE dbx ADD filegroup dbx_file_group; 


ALTER DATABASE dbx ADD FILE (NAME='dbx_file_group1', filename= 
'N:\MSSQL13.MDM\MSSQL\DATA\dbx_file_group1.ndf') TO dbx_file_group; 


In order to move a index to a new FileGroup, you need to drop and recreate it (yeah, messy indeed):


DROP INDEX [CIX_INDEX1] ON table1 WITH ( online = OFF ) 

go 



CREATE CLUSTERED INDEX [CIX_INDEX1] 
  ON table1 ( [col1] ASC ) 
  ON [dbx_file_group] 

go 

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06

terça-feira, 19 de dezembro de 2017

[From Technet] The December release of SQL Operations Studio is now available

This post is authored by Alan Yu, Program Manager, SQL Server.

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.



from SQL Server Blog http://ift.tt/2kN29iX

quarta-feira, 13 de dezembro de 2017

[From Technet] Bring clarity to complex data with graph databases on SQL Server 2017

SQL Server 2017, in addition to processing relational data, now fully integrates with graph database models, all on the same familiar system. This will bring clarity to the increasing amounts of data businesses generate every day.

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.



from SQL Server Blog http://ift.tt/2z9TcbM

terça-feira, 12 de dezembro de 2017

[From Technet] Try mssql-cli, a new interactive command line tool for SQL Server

This post is authored by Alan Yu, Program Manager, SQL Server.

We are excited to announce the Public Preview release of mssql-cli, a new and interactive command line query tool for SQL Server. This open source tool works cross-platform and is a proud member of the dbcli community.

See the install guide to download mssql-cli and get started.

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. 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

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

If you are having installation issues or would like more specific instructions, please see the installation guide.

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 http://ift.tt/1HlbGTY 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.



from SQL Server Blog http://ift.tt/2Axq3df

segunda-feira, 11 de dezembro de 2017

[From Technet] What’s new in SSMS 17.4: SQL Vulnerability Assessment

This post is authored by Ronit Reger, Senior Program Manager, SQL Data Security and Alan Yu, Program Manager, SQL Server

We are excited to announce the release of SQL Server Management Studio (SSMS) 17.4!

Download SSMS 17.4 and review the Release Notes to get started.

SSMS 17.4 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.4 comes with an exciting new feature: SQL Vulnerability Assessment!

What is Vulnerability Assessment?

SQL Vulnerability Assessment (VA) is your one-stop-shop to discover, track and remediate potential database vulnerabilities. It can be used as an excellent preventative security measure, providing visibility into your security state and offering actionable steps to investigate, manage and resolve security issues and enhance your database fortifications. It is designed to be usable even for non-security-experts – getting started and seeing an initial actionable report takes only a few seconds.

Vulnerability Assessment report in SSMS

VA truly enables you to focus your attention on the highest impact actions you can take to proactively improve your database security stature! In addition, if you have data privacy requirements, or need to comply with data protection regulations like the EU GDPR – then VA is your built-in solution to simplify these processes and monitor your database protection status. For dynamic database environments where changes are frequent and hard to track, VA is invaluable in detecting the settings that can leave your database vulnerable to attack.

How does Vulnerability Assessment work?

The VA service runs a scan directly on your SQL database or server. VA employs a knowledge base of rules that flag security vulnerabilities and deviations from best practices, such as misconfigurations, excessive permissions, and exposed sensitive data. The rule base grows and evolves over time, to reflect the latest security best practices recommended by Microsoft.

Results of the assessment include actionable steps to resolve each issue and provide customized remediation scripts where applicable. An assessment report can be customized for each customer environment and tailored to specific requirements. This process is managed by defining a security Baseline for the assessment results, such that only deviations from the custom Baseline are reported.

VA is supported for SQL Server 2012 and later, and can also be run on Azure SQL Database.

Get started now!

To gain the benefits of a Vulnerability Assessment on your database, all you need to do is run a Scan, which will scan your database for vulnerabilities. The scan is lightweight and safe. It takes a few seconds to run and is entirely read-only. It does not make any changes to your database!

To learn more about VA, check out this demo on Channel 9:

Also, take a look at Getting Started with Vulnerability Assessment for more details on how to run and manage your assessment.

Try it out, and let us know what you think!



from SQL Server Blog http://ift.tt/2BbpZ24

quinta-feira, 30 de novembro de 2017

[From Technet] Azure Database Migration Service Preview brings the “lift and shift”

It’s time to get excited—the Azure Database Migration Service Public Preview is here to help you move on-premises SQL Servers to the cloud with near-zero downtime.

There’s never been a better time to start migrating your database to the cloud. Getting ahead in business today means moving to the cloud so that your company can grow and succeed.

The Azure Database Migration Service Preview is the quickest and easiest way for businesses to migrate on-premises databases to the cloud. It’s a fully managed migration service designed to work together with our time-tested migration engines such as the Data Migration Assistant, the Database Experimentation Assistant, and SQL Server Migration Assistant. These tools are tuned to ensure the best migration experience, whether upgrading from legacy versions of Microsoft SQL Server or moving from sources such as Oracle, Sybase, DB2, MySQL, and others. The new service uses a guided, easy-to-implement process to streamline tasks. Regular operations can continue normally during the migration.

Azure Database Migration Service Public Preview uses this functionality to provide rich orchestration capabilities that enable you to organize your databases into project(s) and perform source assessment, schema and data conversion, and validation activities. These activities can be assigned to one or more compute nodes to meet your budget and timeframe goals. With these capabilities, Azure Database Migration Service Public Preview makes it easy to plan migration tasks, run proof-of-concept migrations, author scripting for automation, and ensure that your final production migration to the Microsoft Data Platform is friction-free.

All these capabilities allow you to focus on what’s important for your business and accelerate your Data Estate transformation with the cloud. Now is a great time to find out more about how to make your organization’s database migration as smooth and seamless as possible. To start, check out the Database Migration Guide, which allows you to customize your migration plan based on your organization’s source and target data platforms, along with other key criteria. For a broad scope of information about cloud migration in general, visit the Azure Migration Center.

Most importantly, be sure to take advantage of the Azure Database Migration Service Public Preview today.



from SQL Server Blog http://ift.tt/2it196A

quarta-feira, 29 de novembro de 2017

[From Technet] SQL Server 2017: The world’s first enterprise-class diskless database

This post is authored by Bob Ward, Principal Architect, and Jamie Reding, Senior Program Manager and Performance Architect, Microsoft Database Systems Group.

Perhaps you saw the keynote at the recent PASS 2017 Summit where Microsoft demonstrated the performance of the world’s first enterprise-class “diskless database”. This demonstration showed how Microsoft, Hewlett Packard Enterprise (HPE), and SUSE Linux Enterprise Server partnered together to deliver > 5x performance on analytic queries directly against storage at up to 50 percent of the cost.

Recently HPE published a new world record 1TB TPC-H benchmark result¹ using this configuration with their DL380 Gen10 Server showing 1,009,065 QphH at an incredible price/performance of $0.47 USD per QphH. Performance and price are achieved by combining the power of SQL Server 2017, HPE’s scalable persistent memory, and SUSE Linux Enterprise 12 SP3 Persistent Memory Support.

HPE’s scalable persistent memory is a new innovation which combines standard memory with the persistence of standard storage. It allows database engines like SQL Server 2017 to retrieve data from its data files in a matter of seconds.

To see this technology in action, check out this video. To learn more about this amazing result and technology, read HPE’s blog post and SUSE’s blog post. To learn more about SQL Server on SUSE Linux Enterprise Server, check out SUSE’s SQL Server on Linux website.

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

References



from SQL Server Blog http://ift.tt/2zNXNT7

quinta-feira, 16 de novembro de 2017

[From Technet] SQL Server 2017: A proven leader in database performance

This post was authored by Bob Ward, Principal Architect, and Jamie Reding, Senior Program Manager and Performance Architect, Microsoft Database Systems Group.

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.


from SQL Server Blog http://ift.tt/2A4SVZs