mountainss Cloud and Datacenter Management Blog

Microsoft Hybrid Cloud blogsite about Management


Leave a comment

Take a Deep Dive with this SQL Server 2017 Administration Ebook #SQL #SQL2017 #Azure #dba

Introduction

The velocity of change for the Microsoft SQL Server DBA has increased this decade. The span
between the releases of SQL Server 2016 and 2017 was only 16 months, the fastest new release
ever. Gone are the days when DBAs had between three to five years to soak in and adjust to new
features in the engine and surrounding technologies.
This book is written and edited by SQL Server experts with two goals in mind: to deliver a solid
foundational skillset for all of the topics covered in SQL Server configuration and administration,
and also to deliver awareness and functional, practical knowledge for the dramatic number
of new features introduced in SQL Server 2016 and 2017. We haven’t avoided new content—
even content that stretched the boundaries of writing deadlines with late-breaking new releases.
You will be presented with not only the “how” of new features, but also the “why” and the
“when” for their use.

Go Deep Dive with this Awesome SQL Server 2017 Ebook 😉

Download the Custom excerpt Inside Out SQL Server 2017 Administration Ebook here

Advertisements


Leave a comment

#Microsoft SQL Server 2019 Preview Overview #SQL #SQL2019 #Linux #Containers #MSIgnite

Microsoft SQL Server 2019 Preview

What’s New in Microsoft SQL Server 2019 Preview

• Big Data Clusters
o Deploy a Big Data cluster with SQL and Spark Linux containers on Kubernetes
o Access your big data from HDFS
o Run Advanced analytics and machine learning with Spark
o Use Spark streaming to data to SQL data pools
o Use Azure Data Studio to run Query books that provide a notebook experience

• Database engine
o UTF-8 support
o Resumable online index create allows index create to resume after interruption
o Clustered columnstore online index build and rebuild
o Always Encrypted with secure enclaves
o Intelligent query processing
o Java language programmability extension
o SQL Graph features
o Database scoped configuration setting for online and resumable DDL operations
o Always On Availability Groups – secondary replica connection redirection
o Data discovery and classification – natively built into SQL Server
o Expanded support for persistent memory devices
o Support for columnstore statistics in DBCC CLONEDATABASE
o New options added to sp_estimate_data_compression_savings
o SQL Server Machine Learning Services failover clusters
o Lightweight query profiling infrastructure enabled by default
o New Polybase connectors
o New sys.dm_db_page_info system function returns page information

• SQL Server on Linux
o Replication support
o Support for the Microsoft Distributed Transaction Coordinator (MSDTC)
o Always On Availability Group on Docker containers with Kubernetes
o OpenLDAP support for third-party AD providers
o Machine Learning on Linux
o New container registry
o New RHEL-based container images
o Memory pressure notification

• Master Data Services
o Silverlight controls replaced

• Security
o Certificate management in SQL Server Configuration Manager

• Tools
o SQL Server Management Studio (SSMS) 18.0 (preview)
o Azure Data Studio

Introducing Microsoft SQL Server 2019 Big Data Clusters

SQL Server 2019 big data clusters make it easier for big data sets to be joined to the dimensional data typically stored in the enterprise relational database, enabling people and apps that use SQL Server to query big data more easily. The value of the big data greatly increases when it is not just in the hands of the data scientists and big data engineers but is also included in reports, dashboards, and applications. At the same time, the data scientists can continue to use big data ecosystem tools while also utilizing easy, real-time access to the high-value data in SQL Server because it is all part of one integrated, complete system.

Read the complete Awesome blogpost from Travis Wright about SQL Server 2019 Big Data Cluster here

Starting in SQL Server 2017 with support for Linux and containers, Microsoft has been on a journey of platform and operating system choice. With SQL Server 2019 preview, we are making it easier to adopt SQL Server in containers by enabling new HA scenarios and adding supported Red Hat Enterprise Linux container images. Today we are happy to announce the availability of SQL Server 2019 preview Linux-based container images on Microsoft Container Registry, Red Hat-Certified Container Images, and the SQL Server operator for Kubernetes, which makes it easy to deploy an Availability Group.

SQL Server 2019 preview containers now available

Microsoft Azure Data Studio

Azure Data Studio is a new cross-platform desktop environment for data professionals using the family of on-premises and cloud data platforms on Windows, MacOS, and Linux. Previously released under the preview name SQL Operations Studio, Azure Data Studio offers a modern editor experience with lightning fast IntelliSense, code snippets, source control integration, and an integrated terminal. It is engineered with the data platform user in mind, with built-in charting of query resultsets and customizable dashboards.

Read the Complete Blogpost About Microsoft Azure Data Studio for SQL Server here

SQL Server 2019: Celebrating 25 years of SQL Server Database Engine and the path forward

Awesome work Microsoft SQL Team and Congrats on your 25th Anniversary !


Leave a comment

#Microsoft Azure SQL Query Editor Preview in the #Cloud #Azure #SQL

This quick start tutorial walks through how to create a SQL database in Azure. Azure SQL Database is a “Database-as-a-Service” offering that enables you to run and scale highly available SQL Server databases in the cloud. This quick start shows you how to get started by creating a SQL database using the Azure portal.

The SQL Query Editor is a browser query tool that provides an efficient and lightweight way to execute SQL queries on your Azure SQL Database or Azure SQL Data Warehouse without leaving the Azure portal. This quickstart demonstrates how to use the Query Editor to connect to a SQL database, and then use Transact-SQL statements to query, insert, update, and delete data in the database.

Query Editor Login

When you select a table you can edit the Data

You can edit the data here (preview)

Or delete a row.

This Feature in Microsoft azure is still in preview !


Leave a comment

Connecting to SQL Database By Using Azure Active Directory Authentication #Azure #SQL #Cloud

1aad-auth-diagram

Azure Active Directory authentication is a mechanism of connecting to Microsoft Azure SQL Database by using identities in Azure Active Directory (Azure AD). With Azure Active Directory authentication you can centrally manage the identities of database users and other Microsoft services in one central location. Central ID management provides a single place to manage SQL Database users and simplifies permission management. Benefits include the following:

  • It provides an alternative to SQL Server authentication.
  • Helps stop the proliferation of user identities across database servers.
  • Allows password rotation in a single place
  • Customers can manage database permissions using external (AAD) groups.
  • It can eliminate storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure Active Directory.
  • Azure Active Directory authentication uses contained database users to authenticate identities at the database level.

The configuration steps include the following procedures to configure and use Azure Active Directory authentication.

  1. Create and populate an Azure Active Directory
  2. Ensure your database is in Azure SQL Database V12
  3. Optional: Associate or change the active directory that is currently associated with your Azure Subscription
  4. Create an Azure Active Directory administrator for Azure SQL Server
  5. Configure your client computers
  6. Create contained database users in your database mapped to Azure AD identities
  7. Connect to your database by using Azure AD identities

The following diagram indicates the federation, trust, and hosting relationships that allow a client to connect to a database by submitting a token that was authenticated by an Azure AD, and which is trusted by the database. It’s important to understand that access to a database using Azure AD authentication requires that the hosting subscription is associated to the Azure Active Directory.

2subscription-relationship

Administrator structure

When using Azure AD authentication there will be two Administrator accounts for the SQL Database server; the original SQL Server administrator and the Azure AD administrator. Only the administrator based on an Azure AD account can create the first Azure AD contained database user in a user database. The Azure AD administrator login can be an Azure AD user or an Azure AD group. When the administrator is a group account, it can be used by any group member, enabling multiple Azure AD administrators for the SQL Server instance. Using group account as an administrator enhances manageability by allowing you to centrally add and remove group members in Azure AD without changing the users or permissions in SQL Database. Only one Azure AD administrator (a user or group) can be configured at any time.

3admin-structure

Permissions

To create new users you must have the ALTER ANY USER permission in the database. The ALTER ANY USER permission can be granted to any database user. The ALTER ANY USER permission is also held by the server administrator accounts, and database users with the CONTROL ON DATABASE or ALTER ON DATABASE permission for that database, and by members of the db_owner database role.

To create a contained database user in Azure SQL Database you must connect to the database using an Azure AD identity. To create the first contained database user, you must connect to the database by using an Azure AD administrator (who is the owner of the database). This is demonstrated in steps 4 and 5 below.

Azure AD features and limitations

The following members of Azure Active Directory can be provisioned in Azure SQL Server: – Native members: A member created in Azure AD in the managed domain or in a customer domain. For more information, see Add your own domain name to Azure AD. – Federated domain members: A member created in Azure AD with a federated domain. For more information, see Windows Azure now supports federation with Windows Server Active Directory. – Imported members from other Azure Active Directories who are native or federated domain members. – Active Directory groups created as security groups.

Microsoft accounts (for example outlook.com, hotmail.com, live.com) or other guest accounts (for example gmail.com, yahoo.com) are not supported. If you can login to https://login.live.com using the account and password, then you are using a Microsoft account which is not supported for Azure AD authentication for Azure SQL Database.

Additional considerations

  • To enhance manageability we recommended you provision a dedicated Azure Active Directory group as an administrator.
  • Only one Azure AD administrator (a user or group) can be configured for an Azure SQL Server at any time.
  • Only an Azure Active Directory administrator can initially connect to the Azure SQL Server using an Azure Active Directory account. The Active Directory administrator can configure subsequent Azure Active Directory database users.
  • We recommend setting the connection timeout to 30 seconds.
  • Some tools like BI and Excel are not supported.
  • Azure Active Directory authentication only supports the .NET Framework Data Provider for SqlServer (at least version .NET Framework 4.6). Therefor Management Studio (available with SQL Server 2016) and data-tier applications (DAC and .bacpac) can connect, but sqlcmd.exe cannot connect because sqlcmd uses the ODBC provider.
  • Two-factor authentication or other forms of interactive authentication are not supported.

Here you can read more about the steps Connecting to SQL Database By Using Azure Active Directory Authentication

 

MicrosoftAzure