How to Decide If Stretch Database Can Be Used in Your Environment

SQLServerF1

Stretch Database is an interesting and popular new feature introduced with new SQL Server 2016. Stretch database helps in migrating our historical or less frequently used or archived or cold data transparently and securely to the Microsoft Azure cloud. Stretch Database provides many benefits to the organizations like reducing the storage costs, automatically archiving the old data to Azure, etc. However, stretch database also has its own limitations which make it less likely to be used as of now with SQL Server 2016 RTM release. Hopefully Microsoft comes up with significant improvements soon in service packs or with another release in another couple of years, like it has been releasing since year 2008. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc.

Stretch Database in a SQL Server instance requires at least one table. Once we enable it at instance level, database level and table level, it then silently begins to migrate the historical data to Azure SQL Database on Microsoft cloud. If we are storing historical data in a separate table on-premise database, then we can migrate the entire table to Azure cloud. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration. However, most important question from many organizations management and DBAs or developers is how to decide if stretch database suits their environment or requirement. Below are some points to help decide if Stretch Database can help in meeting your requirements and solve the existing problems.

– Are you looking to store historical data, and do not want to get rid of very old data, as it may be still required to be accessed rarely, then stretch database will be of great help in reducing the storage and maintenance costs of the old data and still allows you to access the old data from the azure cloud.
– Are you looking for archival solution to archive old data from the frequently accessed production data, then stretch database feature is a very good solution to your problem, as you can archive old data on to Azure SQL database cloud, and best thing is you do not need to change anything in your application, as the data storage and access is taken care purely by SQL Server itself.
– If you are looking to reduce storage, compute and memory costs, then this is ideal solution.
– If you see that some tables are very large and causing issues with maintenance like backups, reindexing, stats update, etc, then archiving is best solution and stretch database will be very good solution.
– If your backup/restore are taking too long and missing SLAs, then moving old data to Azure cloud using stretch database will move old data, thus reduces the size of on=premise tables and thus reduces size of on-premise database and now the backup/restore times will be less and should meet the SLA requirements.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

How Does Stretch Database Works in SQL Server 2016

SQLServerF1

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides many benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

Before deciding to this new feature in production environment, it is important to understand how does Stretch Database works and its behavior. First, to enable stretch database, we need to enable the instance level configuration option “remote data archive”, after which we will be able to Enable a Stretch Database for a database or table. Once we enable stretch database for atleast one database and one table, it will internally start to migrate our historical data to Azure SQL database. If the historical data is stored in a separate archive table already, then we can migrate the entire table, if table contains both historical and current data then we need to specify a filter predicate to select the rows which are to be migrated to the Azure SQL database. Some of the cool features with the Stretch Database is that it ensures that no data is lost in case a failure occurs during the data migration to Azure SQL database. It also has retry logic to handle connection issues that may occur during migration. A dynamic management view also provides us with the status of migration.

Also there us option to pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth or to migrate data only during non-business hours or during low activity period. Another cool thing about using stretch database is that we don’t have to change existing queries or client applications. We can continue to have seamless access to both local and remote data, even during data migration. There is a small amount of latency for remote queries, but you only encounter this latency when you query the historical data.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Benefits at Glance of Using New SQL Server 2016

SQLServerF1

Microsoft has released most awaited RTM version of SQL Server 2016 on June 1 2016. There was lot of buzz around with the release of new SQL Server 2016 and has been already downloaded by many community DBAs and developers to start testing or learning using the new features. Just like any other SQL Server version, even in new SQL Server 2016, there has been lot of new features introduced in areas like security, performance, scalability, stability, etc. Also there is support for SQL Server on linux in pipeline, which makes it much more interesting and to enter new companies which did not use windows operating system. As a DBA or developer it is very important to gets handon new features and suggest to the management how it will benefit the clients or customers or their own business with the new features. Always, test in a test environment thoroughly before deciding to move to production. Also, as like any new version, it is expected to have lot of bugs too, so it is up to you to decide whether to wait till a service pack is released or just go ahead and install it right away now. My preference is to start testing on test environment and see how application reacts to it, and if the new features are beneficial and performance has improved with the applicaion, then I prefer to go ahead with the new SQL Server 2016 on production environment.

Following are some of the important benefits of new SQL Server 2016 which we might want to keep on top of our head.
– Microosft claims Enhanced in-memory performance which provides up to 30x faster transactions, and more than 100x faster queries than disk based relational databases and real-time operational analytics.
– New security feature Always Encrypted which helps in protecting our data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without requiring any changes to the application.
– Built-in advanced analytics– provide the scalability and performance benefits of building and running advanced analytics algorithms directly in the core SQL Server transactional database.
– Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android
Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology

– New features like Stretch Database technology keeps more of our historical/cold data at our fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes.
– Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore our on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure.
– Closer integration with Azure SQL Database which helps more customers comfortable to migrated to cloud with the new security features.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Pre-install Information for SQL Server 2016

SQLServerF1

Microsoft has officially released new SQL Server 2016 on June 01 2016 for public to download and install it on their development, test or production servers. It is important to test the applications thoroughly before moving to production environment, as there may be some changes required to the application to work seamlessly. If the application is vendor application, then contact the respective vendor and get approval to use new SQL Server 2016, most times, vendors releases new version of the application which works fine on new versions of SQL Server. Although, installing and using SQL Server 2016 is straight forward, but important to take care of things prior to install to ensure no hiccups later for the installation process. SQL Server 2016 has a critical pre-requisite for updated Visual Studio VC++ 2013 Runtime Libraries. To install or check if this update is required on your system, refer KB316398 for further information.

Review SQL Server 2016 system requirements – Memory Minimum requirement for Express Editions is 512 MB, All other editions: 1 GB, however recommended for Express Editions is 1 GB and for All other editions is at least 4 GB and should be increased as database size increases to ensure optimal performance. Processor speed required minimum is x64 Processor: 1.4 GHz, however recommended is 2.0 GHz or faster. Type of x64 Processor is AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support. Important point to note is SQL Server 2016 is supported on x64 processors only. It is no longer supported on x86 processors. WOW64 is not supported for SQL Server 2016. Review SQL Server release notes – check the release notes of SQL Server before installation to get all the details.
Download and install full-featured software for a 180-day trial edition or buy a licenseed edition for production environments and developer edition for development purposes. We may also choose to use express edition based on our requirement, if we are not using all features.

Make sure the account which used to RDP to the server has administrative rights on the computer to install SQL Server 2016. We will have SQL Server 2016 DVD Image or ISO file for the installation.
The Microsoft SQL Server 2016 release is available for testing purposes only and should NOT be installed and used in production environments.
Side-by-Side installation with down-level production SQL Server instances as well as in-place upgrades of down-level production SQL Server instances, is supported for SQL Server 2008 and higher.
If you have questions or concerns that come up during your testing and evaluation, we encourage you to use the MSDN forum for SQL Server 2016 to search for answers or ask new questions.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Details regarding SQL Server on Linux and SQL Server 2016 Released on June 1st

SQLServerF1

Microsoft has officially released SQL Server 2016 on June 1st 2016. We can download an evaluation edition from here. This trial edition expires after 180 days, after which we will need to upgrade it to a licensed edition or download free express edition if the features used are minimal. Microsoft has introduced many new features with SQL Server 2016 in different areas. Some of the popular features include, Stretch database, Always Encrypt, Data Masking, also promises of significant improvement in the performance, much closer integration with SQL Azure, temporal tables, query store, Row level security, changes to upgrade advisor, etc. Initially Microsoft has released release candidate versions RC0, RC1, RC2 and RC3, which was downloaded and tested by many users in the DBA and Developer community and by some organizations and enough feedback was provided to improve the product. There were many bugs identified with the initial RC versions on various features, which were subsequently fixed in later RC versions.

Now SQL Server 2016 official RTM version has been launch on June 1st 2016. Once you install the new SQL Server 2016 RTM version, then you will see the build number as 13.0.1601.5. Some insights from Microsoft release documentation are “SQL Server 2016 is here! It is the biggest leap forward in Microsoft’s data platform history with faster transactions and queries, deeper insights on any device, advanced analytics, new security technology, and new hybrid cloud scenarios. SQL Server 2016 delivers breakthrough mission-critical capabilities with in-memory performance and operational analytics built-in. Comprehensive security features like new Always Encrypted technology helps protect your data at rest and in motion, and a world class high availability and disaster recovery solution adds new enhancements to AlwaysOn technology.You can also gain the benefits of hyper-scale cloud with new hybrid scenarios enabled by new Stretch Database technology that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure in a secured way so your data is always at hand for queries, no matter the size. In addition, SQL Server 2016 delivers a complete database platform for hybrid cloud, enabling you to easily build, deploy and manage solutions that span on-premises and cloud.”

Another exciting news from Microsoft team is the announcement of further details regarding SQL Server support on Linux Operating System. So far SQL Server was supported only on Windows environment, but not there is lot of work in progress to get SQL Server work on linux environment too, just like it works on a Windows environment. SQL Server version which works on linux has not yet been released with SQL Server 2016, instead it may be released as a sub version later or as a new SQL Server version, but looks mostly it will be a sub version of SQL Server 2016 R2 or as part of a service pack. If you want to get hands on with SQL Server 2017 linux version, then at this point all we can do is sign up for Microsoft SQL Server on Linux and wait for further updates. You can register here for Microsoft SQL Server on Linux preview. Also, there has been a video released with small demos on installing and running SQL Server on linux environment. You can watch the video here. Important thing to note is that only database engine is so far been created and tested, but other services like Integration services, Reporting services, Analysis Services are still going to take some time to get on to the linux environment.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Azure Pricing for of Stretch Database in SQL Server 2016

SQLServerF1

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure SQL cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

Stretch Database lets us choose retention times of our choice even for large amounts of data without breaking the bank. Depending on our performance requirements, we can choose a performance level, and then scale up or down as needed. Stretch Database charges for Compute and Storage are charged separately, so we choose to only pay for what we use. Compute usage is represented as Database Stretch Unit (DSU) and customers can scale up and down the level of performance/DSUs that we need at any time. We have options for pricing based on different locations based on the currency. If we consider USD, below are the sample pricing options for usage of computing resources,
PERFORMANCE LEVEL(DSU) PRICE
100 $1.25/hr (~$930/mo)
200 $2.50/hr (~$1,860/mo)
300 $3.75/hr (~$2,790/mo)
400 $5/hr (~$3,720/mo)
500 $6.25/hr (~$4,650/mo)
600 $7.50/hr (~$5,580/mo)
1000 $12.50/hr (~$9,300/mo)
1200 $15/hr (~$11,160/mo)
1500 $18.75/hr (~$13,950/mo)
2000 $25/hr (~$18,600/mo)

Another pricing part which we need to pay separately for is storage. Storage rates are based on standard RA-GRS Page Blob rates. Storage transactions are not billed; customers only pay for data stored, not storage transactions.
Here Data Transfers refer to data moving in and out of Azure data centers other than those explicitly covered by the Content Delivery Network or ExpressRoute pricing.
LRS GRS RA-GRS
COOL HOT COOL HOT COOL HOT
First 100 TB / Month $0.01 $0.024 $0.02 $0.048 $0.025 $0.061
Next 900 TB / Month $0.01 $0.0232 $0.02 $0.0463 $0.025 $0.0589
Next 4,000 TB / Month $0.01 $0.0223 $0.02 $0.0446 $0.025 $0.0567

LOCALLY REDUNDANT STORAGE (LRS) – Makes multiple synchronous copies of your data within a single datacenter.
ZONE REDUNDANT STORAGE (ZRS) – Stores three copies of data across multiple datacenters within or across regions. For block blobs only.
GEOGRAPHICALLY REDUNDANT STORAGE (GRS) – Same as LRS, plus multiple asynchronous copies to a second datacenter hundreds of miles away.
READ-ACCESS GEOGRAPHICALLY REDUNDANT STORAGE (RA-GRS) – Same as GRS, plus read access to the secondary datacenter

OUTBOUND DATA TRANSFERS ZONE 1* ZONE 2* ZONE 3*
First 5 GB/Month Free Free Free
5 GB – 10.0 TB $0.087 per GB $0.138 per GB $0.181 per GB
Next 40 TB
(10-50 TB)/month $0.083 per GB $0.135 per GB $0.175 per GB
Next 100 TB
(50-150 TB)/month $0.07 per GB $0.13 per GB $0.17 per GB
Next 350 TB
(150-500 TB)/month $0.05 per GB $0.12 per GB $0.16 per GB

Outbound data transfers are charged at regular data transfer rates. A sub-region is the lowest level geo-location that you may select to deploy your applications and associated data. For data transfers (except CDN), the following regions correspond to Zone 1, Zone 2 and Zone 3.

Zone 1: US West, US East, US North Central, US South Central, US East 2, US Central, Europe West, Europe North
Zone 2: Asia Pacific East, Asia Pacific Southeast, Japan East, Japan West, Australia East, Australia Southeast
Zone 3: Brazil South.

There can be discounts to these prices based on region, number of servers, amount of compute and storage brought.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Stretch Database Feature in SQL Server 2016

SQLServerF1

Many new features gets introduced with each new version of SQL Server releases. Even with SQL Server 2016 many new features were introduced, one of which is Stretch database, which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

Stretch database will help Microsoft in making many of the customers to buy Azure SQL subscription. Following are more details regarding the benefits of using this new feature Stretch database in SQL Server 2016.
cost-effective availability for cold data – Stretch database feature allows us to transfer warm or cold data dynamically from SQL Server to Microsoft Azure SQL database and this entire process is transparent to end users, application developers and DBAs. Unlike typical cold data storage, our data will be always online and available to run queries against it. We can also specify data retention timelines to keep as much of data as required on the Azure SQL database and query it. Azure SQL databases are considered as less cost options compared to on-premise servers, so this will benefit using the low cost of Azure rather than scaling expensive, on-premises storage. We can choose the pricing tier and configure settings in the Azure Portal to maintain control over price and costs and we can scale up or down as needed.

No changes required to queries or applications – This is very important part of this feature. In traditional archiving plans, there are lot of changes required from the application side, but this is greatly reduced with stretch databases. Microsoft claims that there are no changes required at all with using this feature, because the data access to cold data on Azure SQL database is handled by SQL Server internally.
Reduced on-premises data maintenance – Because we are moving old data to Azure SQL database, we will have less amount of data on our on-premise databases, which will reduce the amount of time takes for tasks like backups, Index and statistics maintenance, etc. Also, the storage requirements on-premise will be greatly reduced this maintenance or storage will be reduced and can also use the additional storage available on-premise for other databases.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Finding Tables Suitable for Stretch Databases in SQL Server 2016

SQLServerF1

Most of the DBAs must be familiar with SQL Server upgrade advisor, which we run during planning phase of upgrading to higher SQL Server versions inorder to identify any objects or features that will get affected during or after the instance or database upgrade. With every new release of SQL Server, some old features may be deprecated or retired and behavior of some features might change in new versions. So, running upgrade advisor during planning phase on a test server where the production database is restored, will give us good idea on what objects are going to break during or after upgrading to higher version of SQL Server. In previous releases of SQL Server, the Upgrade Advisor tool was made available only few days before the release of the new SQL Server version, but with SQL Server 2016, the upgrade advisor has been released way early giving options to test the instances or databases and then to proceed with testing on RC releases of the SQL Server. Although, we may be very familiar with using the upgrade advisors for SQL Server 2005, 2008 R2, 2012 and 2014, but there have been significant changes to SQL Server 2016 Upgrade Advisor in look and feel and its usage.

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

We can identity the tables which can be part of Stretch Database using SQL Server 2016 upgrade advisor, SQL Server 2016 upgrade advisor has different options to analyse a regular database and to analyze for Stretch Database. To get started, first download and install SQL Server 2016 upgrade advisor on a test server. Launch the SQL Server 2016 upgrade advisor and select “Scenarios” option -> choose ‘Run Stretch Database Advisor” -> Choose “Select Databases to Analyze” -> Provide SQL Instance name and make the successful connection to the SQL instance against which we want to run the analysis -> Select one or more databases from the list of databases list provided -> Run the analysis. It will take a while for the analysis to complete, after which we will be presented with a summary page of the analysis. We can save the results to HTML file or CSV file or other options as and when available.

But there are many limitations to the tables to be part of Stretch Database at this point of time. Below are some of the limitations.

– Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure SQL table that contains the migrated data.
– Insert/Delete operations are not supported and also insert is not allowed through linked servers as well.
– Views cannot be created on the stretch enabled tables. Filters on SQL Server indexes are not propagated to the remote table.
– There are many other limitations on which data types cannot be used for the tables columns, some database properties are not supported like filetables, Memory-optimized tables, etc.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Revamped SQL Server 2016 Upgrade Advisor

SQLServerF1

Most of the DBAs must be familiar with SQL Server upgrade advisor, which is used to run during planning phase of upgrading to higher SQL Server versions inorder to identify any objects or features that will get affected during or after the instance or database upgrade. With every new release of SQL Server, some old features may be deprecated or retired and behavior of some features might change in new versions. So, running upgrade advisor during planning phase on a test server where the production database is restored, will give us good idea on what objects are going to break during or after upgrading to higher version of SQL Server. In previous releases of SQL Server, the Upgrade Advisor tool was made available only few days before the release of the new SQL Server version, but with SQL Server 2016, the upgrade advisor has been released way early giving options to test the instances or databases and then to proceed with testing on RC releases of the SQL Server. Although, we may be very familiar with using the upgrade advisors for SQL Server 2005, 2008 R2, 2012 and 2014, but there have been significant changes to SQL Server 2016 Upgrade Advisor in look and feel and its usage.

We should be able to download the latest version of SQL Server 2016 Upgrade advisor from official Microsoft link here.
As per Microsoft documentation “SQL Server 2016 Upgrade Advisor Preview is a standalone tool that enables users of prior versions to run a set of upgrade rules against their SQL Server database to pinpoint breaking and behavior changes and deprecated features as well as providing help with the adoption of new features such as Stretch Database.
Once we download the SQL Server 2016 Upgrade Advisor, we will find SqlAdvisor.msi in the downloaded location. Run this msi installer by choosing run as administrator and you will need to have local administrator permission on the server where you are installing this tool. It is always recommended to install Upgrade Advisor on a test server and restore a copy of production database on the test server for analysis.

Once the installation of SQL Server 2016 upgrade advisor is completed successfully, launch the program and you will first see a What’s New screen which has some information regarding What’s new in SQL Server 2016 Upgrade Advisor. After that we will find a title bar with SQL Server 2016 Upgrade Advisor and next to it, you will see two main options “Scenarios” and “Active”. Apart from these two options, there are Notifications, Send Feedback and Settings which allows us to see new notifications for the product and to send any feedback to Microsoft for any bugs or new feature requests. Settings option has a gear icon which is used to choose any settings, as of now there is not much you can do here.

Inorder to start the upgrade analysis process, we need to choose “Scenarios” menu -> Run Database Upgrade Advisor -> Select databases to analyze -> Select Instance -> Select the instance from the drop down list if any available or create a new connection -> If we need to specify port number as well inorder to connect to the instance, then we can specify the port number by clicking on advanced options -> Once we successfully made connection to the SQL Server instance, next we will see list of databases to choose for the upgrade analysis -> Select one or more databases based on your requirement -> Run the upgrade analysis by clicking on Run button. It will take a while for the analysis to be performed. Once analysis is complete, you will be presented with a summary page. We will not only see the analysis for SQL Server 2016 instance, but we will find it for other versions as well based on what is the SQL Server version where upgrade advisor was run against, like if you run it against SQL Server 2008 R2 instance, then you will see the analysis for SQL Server 2012, 2014 and 2016. We can drill down for each of the versions and see the affected objects and the details about the impact and recommendation.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Dynamic Data Masking Feature in SQL Server 2016

SQLServerF1

Microsoft SQL Server 2016 has introduced many new features and Dynamic Data Masking is one such feature useful in some cases. Dynamic data masking main purpose is to limit the exposure of sensitive data by masking it to non-privileged or low privileged users. Dynamic data masking feature helps in preventing unauthorized access to sensitive data by enabling users to only view the sensitive data required with minimal impact on the application layer. This basically hides the data from the user trying to query the data, instead zeros or other symbols are returned instead of actual data. The underlying data does not change, but only the results are not visible to under privileged users. This Dynamic data masking feature is very easy to implement with the existing applications, since masking rules are applied only in the query results. Many applications can mask sensitive data without modifying existing queries.

It is important to understand that this Dynamic Data Masking Feature in SQL Server 2016 is not at all replacement of any of the security features like TDE, Cell-level encryption, SSL, etc, because in Dynamic Data Masking, the underlying data is not converted into another form, rather it is saved in the same format and can be viewed by users with appropriate permissions without any issues or without any requirement of decryption or performing any transformations. The use case of this feature is more for obfuscation of personally identifiable information (PII) from lesser-privileged users. This can also be be used to apply the masking constraints to development or test environments where this information should not be visible to the developers or testers. In the initial CTP releases of this Dynamic Data Masking feature, there were many bugs identified by the community where some tricks could easily reveal the sensitive data to under privileged users, like using alias or temp tables or variables could reveal the sensitive data. Later versions of CTP releases had fixes released to counter the bugs, but still this feature is only has limited usability, but strictly should not be considered as replacement for encryption of data.

Dynamic Data Masking feature supports different types of masks to some or all of the columns in a table which helps in protecting the privacy of the data from low-privileged/normal (non-sysadmin) users. There are four different types of masks currently supported Dynamic Data Masking in SQL Server 2016.
default() – This is for strings to show x for each character up to 4, for numeric types it will show 0, and for dates this will show 2000-01-01.
email() – This only shows the first character, then replaces the remaining characters with XXX@XXXXX.com
partial() – With this option, we can define a custom string to represent the mask, including how many leading and trailing characters to show from the original string.
random – A random masking function for use on any numeric type to mask the original value with a random value within a specified range.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 
1 2 3 4 35