Changes to SQL Server 2016 Management Studio(SSMS)

With each new release of SQL Server, there are many new features that get introduced. Microsoft has put lot of effort in improving the SQL Server Management Studio as well with the new releases to support the new features that get introduced. Also, there has been lot of support improvements for Azure integration and management with SSMS. Previously there was not much, DBAs were able to make changes to Azure databases from SQL Server Management, but with the release of SSMS 2016, there has much much closer integration for managing SQL Azure instances and databases. For those who are not aware, we can download and install SQL Server Management Studio (SSMS) as separate individual download and install just SSMS on a client machine or on a server. Some of the new features support for SQL Server Management Studio(SSMS) include,

– Addition of human-readable error messages. With this, now when there is an error while performing an operation using SSMS, we get more meaningful error, which will be of great help in troubleshooting, instead of wondering or decoding on what the error is about, as like in some of the previous versions of SQL Server Management Studio.
– Stretch database wizard improvements which adds support for predicates.
– SQL Server 2016 management Studio now allows us to create ER Entity-Relationship Diagrams for SQL Azure Databases without the need for any external tool.
– Now we can see some new options when we right click on the SQL Azure database like “Open in Management Portal”, new “Reports” to view “Transaction Performance Analysis Overview”.
– New options supported when we right click on the user table like, Design(now we can design SQL Azure database tables from SSMS), option to choose selecting or edition top x rows, options for graphical interface to create and administer Full-Text indexes on SQL Azure database tables.

– Database properties now have additional properties or settings like, in the options tab, there is a section for Azure which has options which allows us to change the database edition, the Service Level Objective and the maximum database size.
– There have been many other options enabled in SQL Azure database and table properties which allows us to manage or make changes to database or tables of SQL Azure database and tables, just like regular on premise SQL Server database or table.
– Improvement in the AlwaysEncrypted Powershell commandlet to add key encryption APIs.
– Some known issues has been fixed, like to turn off IntelliSense in the SSMS toolbar, if it has been disabled in the Tools,Options dialog.

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

 

Basics of Temporal Tables in SQL Server 2016

SQLServerF1

Microsoft has introduced many new features with their new release of SQL Server 2016. One of the such interesting feature released with SQL Server 2016 is Temporal Tables. Temporal Tables are also known as system-versioned tables. In a single liner, these Temporal Tables allows SQL Server to automatically keep history of the data in the table instead of just the current data which we see in regular or temporary tables or variables. This Temporal Tables is a database feature, introduced newly with SQL Server 2016. Although this is called as system-versioned temporal table, but this is a new type of user table in SQL Server 2016, which is designed to keep a full history of data changes in the table and allows an easy point in time analysis of the data. You might wonder, why this type of temporal table is called as a system-versioned temporal table, it is because the period/time of validity for each row is managed by the system, which is the database engine.

Temporal tables were introduced in the ANSI SQL 2011 standard, which is now adopted by Microsoft SQL Server 2016 onwards. To understand more about the Temporal tables, we can compare it with regular user table to understand its behavior, which is different from a normal user table. In a normal user table, we insert the data and we can see the inserted data using select statement, but once we issue a delete or update statement, we will now see the latest data only with the new select statements, but with Temporal tables, we can query and get the data which was deleted or the data which was modified and what was the old value of the data. Taking a real time example, suppose we have a table with one column and it has one row with a data value of ABCD, now we updated this data from ABCD to ABC, now in a normal user table, we can only see ABC value, but with a Temporal tables we can actually see both the old value ABCD and ABC. This is possible by storing the history of the data changes and its values in a history table. History table has the old data and also the start and end times to identify when the data was present in the Temporal tables, thus allowing us to see the data at a older given point of time.

Now, as we have some basic idea about the Temporal tables, lets try to understand further about how to create and query these Temporal tables. Before, we proceed with seeing, how to create these Temporal tables, as like any other feature or object, there are some pre-requisites for creating Temporal tables.
– It is mandatory for a temporal table to have a primary key defined.
– Two additional columns are required to be defined while creating the temporal tables, which are used to record the start and end date and times. These two columns must be of datetime2 data type. These columns are referred to as period columns. These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.
– These tables cannot be part of In-memory OLTP feature.
– There are some restrictions on types of triggers that can be created on these tables.

CREATE TABLE dbo.TestTemporalTable
	(ID int primary key,
	Col1 int
	Col2 int
	SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
	SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
	PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) 
        WITH(SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.TestTemporalTableHistory));

If we don’t specify and History_table, the SQL Server creates a history table with its own name like dbo.MSSQL_TemporalHistoryFor_xxx, where xxx is the object id of the main table. In GUI, the TestTemporalTable is shown under the tables list, where as the TestTemporalTableHistory will be shown under as a subset of TestTemporalTable. TestTemporalTableHistory has same columns as the actual temporal table, but any constraints will be removed. Also, we can create different indexes on TestTemporalTableHistory and TestTemporalTable tables based on the usage of these tables in the application and having different indexes, will greatly help improving the performance. Also, this is not compared to the CDC, which mainly uses transaction log file to track the changes.

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

 

SQL Server 2014 Developer Edition is Free From Now

As a DBA or Developers it is important to have a SQL Server instance to be able to install and use it to understand the working and usage for the applications. So far, DBAs mostly rely on the free Evaluation edition of SQL Server provided by the Microsoft team. This Evaluation edition expires after 180 days, after which we need to purchase a license and upgrade the Evaluation edition to licensed edition depending on our requirement. Microsoft offers different editions for different purposes, like Enterprise edition for important and critical production applications, Standard edition for less critical applications to reduce the cost of the licensing, Developer edition specifically for developers for development and unit testing the applications before moving to production environment. Previously, most developers preferred buying developer edition as the cost is less and is is specifically released for the purpose and it consists of all features available in enterprise edition and also it could be easily upgraded to enterprise edition if required.

Starting with SQL Server 2014, Microsoft has made a decision to make the SQL Server developer edition free of cost for the developers through Visual Studio Dev Essentials members. Once you login as Visual Studio Dev Essentials members, you will be able to download the SQL Server Developer edition and use it for free of cost. This decision was taken inorder for developers to leverage the capabilities that SQL Server 2014 and higher has to offer for their applications and also this is considered as another step for making SQL Server more accessible to the users which helps in spreading the SQL Server in more companies. However, it is important to keep in mind that SQL Server Developer Edition is still specifically for development and testing purposes only, and not to be used in production environments or not to be used with any production data.

Few important things to keep in mind are below.
– This only includes SQL Server Developer Edition and does not include Operating System license or license for any other products.
– SQL Server 2016 developer edition is also will be made available free under same program and this will mostly be continued with the future releases of SQL Server.

In case you are not part of Visual Studio Dev Essentials members, then you may either buy SQL Server developer edition or can try other options available in the market like SQL Server Evaluation edition. If the application is small and not so critical, you may even choose trying SQL Server Express edition. Hope you will get your free developer edition and explore the new features available like InMemory OLTP and other features.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014
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

 

SQL Server Cumulative Updates are More Stable to Install

Microsoft releases various service packs, cumulative updates, hotfixes for SQL Server to fix any bugs or known issues. Each of these serves different purposes. For example hotfixes address specific bug or issue and released when a customer opens a case with Microsoft and issue identified as bug, then hoxfix will be released to rectify the specific issue. So, DBAs are required to only apply the hotfix if they are facing that issue. Cumulative updates contain more number of fixes which include multiple hotfixes and additional bug fixes raised by customers. Service Packs are more higher level and contain all service packs, cumulative updates released so for for that version of SQL Server.

With each release of SQL Server version, Microsoft has been making various improvements to the product and the process based on the feedback received from the customers. For example, since SQL Server 2008, Microsoft has allowed possibility of uninstalling service packs, thus providing a better and easier way of rollback in case something goes wrong after applying the patch. Now in year 2016, Microsoft has come up with approach of recommending to install latest available service pack or cumulative update and stated that both of these will be stable as the level of testing done will be same. Previously, in DBA community, there has been assumption that service packs are more stable compared to cumulative updates, as service packs contain more fixes and tested thoroughly and thought that CUs are not tested as thoroughly as SPs.

Now with the article, Microsoft has made it clear that even service packs going forward from year 2016 are stable and recommends to install them as soon as they are available, similar to how DBAs react to release of service packs. This is due to change in the way the service packs and cumulative updates are created and tested. Microsoft has made effort to update the KB articles to reflect the same. Previously, the KB articles used to have message that “This cumulative package is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems.”, which made many customer back down from applying the cumulative updates, but now they are updating the message with “we now recommend ongoing, proactive installation of CU’s as they become available.”

This change was welcomed by the DBA community where it makes it easier to convince the management to go ahead with applying the latest updates available from Microsoft whether it is a service pack or a cumulative update. Hope, with coming years there will be many more changes which not only improves the product, but also improves the processes.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
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

 

SQL Server 2016 Can Be Installed on Linux

As a SQL Server DBA, we never would have expected that Microsoft will someday support installing and running SQL Server instance on a linux machine, but it has come true as a surprise, as Microsoft confirmed in their official website about the same. Refer here for more information. Since the inception of SQL Server, it was never supported or possible to install SQL Server on a linux Operating System. Although, some baby steps on this was started, when SQL Server was supported to run on Windows Core. Since many years Microsoft had a firm stand on not supporting its applications on different Operating System platforms, but with changing world, Microsoft had to change its view as well. There has been mixed response from the SQL Server community regarding this move.

One of the main reasons sighted by Microsoft regarding this move was taking into consideration clients who preferred to use Linux operating System, but wanted to use SQL Server. As SQL Server was not supported to run on Linux, many organizations have moved away from SQL Server as they do not want to get stuck with Microsoft suite, which benefited oracle and other open source technologies like MySQL. With this move, now SQL Server will be picked up by many organizations which are running linux or other open source applications. Although this news has been received positively across the SQL Server and other platforms community, still there are many questions about the stability and performance of SQL Server running in linux operating system. Once we have more details on this and people start installing the SQL Server on linux, we get more details about the problems that arise while installing SQL Server in linux and trouble administering the SQL Server on linux. Also, it would be interesting to see how the performance would differ, while SQL Server running on Windows and linux systems with same hardware.

This will be a benefit for the SQL Server DBAs as they will now get an opportunity or even forcefully have to learn working with linux operating system, which mostly operates through commands better. Most SQL Server DBA’s get stuck with knowing only windows operating system, which makes it difficult for them to lean other RDBMS products like Oracle or MySQL as they can run on both Windows as well as linux operating systems. Oracle/MySQL DBA’s had more feasibility to learn SQL Server, than SQL Server DBA’s leaning Oracle or MySQL, now this will change. Also, this will change the mindset of the SQL Server DBA’s to understand the importance of managing or administering SQL Server and operating system though commands, rather than GUI. There is still a long way to go, as it is expected that there are many challenges on the way for the SQL Server to work seamlessly on linux operating as it works on Windows operating System. This will bring more customer adopt SQL Server product, thus opening more DBA jobs. Also DBA’s who like challenging tasks would love this move, as it is completely new and no or very little documentation will be ever on this, which makes their job more challenging and interesting.

Stay tuned for more information.

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

 

Impact of LEAP Second on SQL Server Databases

SQLServerF1

SQL Server is not really impacted by the leap second. Most affected systems by the second leap are those based on Linux and Java, and those that depends heavily on replicated data. Windows based systems do not seem affected by the second leap as they use Windows Time Service through external NTP Time Server. It is recommended that Systems admins sync domains controllers rather than waiting till next synchronization, which might take up to one week. SQL Server database engine is also not affected by the second leap, as it is reading time through the OS.

Most applications also will not be affected by the second leap, as they don’t depend on seconds in calculations. Some of the systems might get affected, especially those that might be real-time systems or those that depend on seconds on their internal calculations. Money Markets systems, and similar ones, such as securities, FX, derivatives and electronic trading systems that channel orders in real-time might get impacted by the second leap. However, this is depending on systems architectures and the way orders are channeled through local markets, international markets, Bloomberg or Reuters.

In most cases, timestamps and execution orders are handled by intermediate systems or market systems, and therefore we do not really need to worry about it.
In case specific critical application or system needs to be reviewed, it is suggested to do a thorough study to application architecture, timing dependencies, and code review if necessary.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

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

 

Pre-Installation Details for Microsoft SQL Server 2016

SQLServerF1

Recently Microsoft has made an announcement about the upcoming release of SQL Server 2016 Community Technology Preview 2 (CTP2 ). There has been great enthusiasm about features that will be released with the new SQL Server 2016 and the enhancements that are made to the new SQL Server 2016. To test out the new features we need to download and install new SQL Server 2016, so below are the details you would need to know before installing SQL Server 2016 which are referred to as SQL Server 2016 CTP2 Pre Installation Details.

Installation Guidelines for SQL Server 2016
– Make sure that you have an account that has local administrative rights on the computer where installing SQL Server 2016 CTP2.
– SQL Server 2016 CTP2 DVD Image: You can use this ISO image from http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016 and then burn it to your own DVD. Download the following .ISO file from the Evaluation Center: http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016
SQLServer2016CTP2-<architecture>-<language>.iso
Using your DVD-burning software, select the options to burn a DVD from an .ISO image. When you are prompted for a file to use, select the image file that you downloaded. After burning the DVD, locate and double-click Setup.exe on the DVD to begin the installation.
SQL Server 2016 CTP2 CAB file:
– Download the following files from the Evaluation Center to a temporary directory on your device:
SQLServer2016CTP2-<architecture>-<language>.box
SQLServer2016CTP2-<architecture>-<language>.exe
Once the download has completed, extract the zip files then double click on the SQLServer2016CTP2-<architecture>-<language>.exe to begin the installation process.

Instructions for installing SQL Server 2016 CTP2 on Microsoft Azure
Microsoft Azure has been in news of recent and many DBAs and developers want to try installing on Microsoft Azure too. For instructions on how to install SQL Server 2016 on Microsoft Azure, please go to the Microsoft Azure Infrastructure Services Page.
Some important Notes About SQL Server 2016 CTP2:
– The Microsoft SQL Server 2016 CTP2 release is NOT supported by Microsoft Customer Services and Support (CSS). Meaning, if you encounter any issues, you cannot open a support case with Microsoft, instead you can post it on any popular community forum which will be addressed if it is a critical issue.
– The Microsoft SQL Server 2016 CTP2 release is made available by Microsoft mainly 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.

Hope this was helpful to you

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 Coming with New SQL Server 2016 Release

SQLServerF1

SQL Server 2016 is making buzz in the community where lots of interested Database Administrators, Developers, Managers trying to get more insights about the product and trying to get an idea about new features introduced with SQL Server 2016 and what are the enhancements or changes to the existing features like AlwaysON, In-Memory OLTP, etc. Microsoft SQL Server team works on new features based on what competitors are already providing and something new and more than what competitors already have. Also, there are lot of enhancements made to existing features based on the feedback from user community and customers using the SQL Server product.

Blow are the features or enhancements that come up with new SQL Server 2016 as reported in their official blog.
BENEFITS of SQL Server 2016
– Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk-based relational databases and real-time operational analytics
– New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes
– Built-in advanced analytics provide the scalability and performance benefits of building and running your 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
– Stretch Database technology keeps more of your customer’s historical data at your 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 your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure.

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