Basics of SQL Server Databases

SQLServerF1

A database in SQL Server is made up of a collection of tables that stores a specific set of structured data. A table contains a collection of rows, also referred to as records or tuples, and columns, also referred to as attributes. Each column in the table is designed to store a certain type of information, for example, dates, names, dollar amounts, and numbers.

A PC can have one or more than one example of SQL Server introduced. Every case of SQL Server can contain one or numerous databases. Inside of a database, there are one or numerous item possession gatherings called diagrams. Inside of every mapping there are database protests, for example, tables, sees, and put away methods. A few articles, for example, declarations and uneven keys are contained inside of the database, yet are not contained inside of a composition. For more data about making tables. SQL Server databases data is stored in the file system in physical files. These Files can be grouped into filegroups.

At the point when individuals get entrance to an occurrence of SQL Server they are recognized as a login. At the point when individuals get entrance to a database they are distinguished as a database client. A database client can be in view of a login. On the off chance that contained databases are empowered, a database client can be made that is not in view of a login. For more data about clients.

A client that has admittance to a database can be offered authorization to get to the items in the database. Despite the fact that consents can be conceded to individual clients, we suggest making database parts, adding the database clients to the parts, and afterwards give access consent to the parts. Giving consents to parts rather than clients makes it simpler to keep authorizations reliable and justifiable as the quantity of clients develop and persistently change. For more data about parts consents.

The vast majority who work with databases utilize the SQL Server Management Studio apparatus. The Management Studio device has a graphical client interface for making databases and the articles in the databases. Administration Studio likewise has an inquiry supervisor for collaborating with databases by composing Transact-SQL proclamations. Administration Studio can be introduced from the SQL Server establishment circle, or downloaded from MSDN.

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

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

 

List of Bugs Fixed in SQL Server 2014 CU2 – Part 1

SQLServerF1

With every release of SQL Server product or subsequent service packs or Cumulative updates, new bugs are encountered or discovered. Below are some of the bugs which were fixed with the release of Cumulative update package (CU) 2 for Microsoft SQL Server 2014.

KB Article 2926223
SQL Server 2014 bug ” Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012 or SQL Server 2014 in Management Tools
KB Article 2928300
SQL Server 2014 bug ” Intense query compilation workload does not scale with growing number of cores on NUMA hardware and results in CPU saturation in SQL Server related to SQL Server performance
KB Article 2931815
SQL Server 2014 bug ” Loads or queries on CCI tables block one another in SQL Server 2014 with SQL Server service
KB Article 2932559

SQL Server 2014 bug ” Totals are wrong after you filter on a pivot table item and remove the filter in SSAS 2012 or SSAS 2014 with Analysis Services
KB Article 2933103
SQL Server 2014 bug ” Assertion failure when you execute a query specifying TOP N and ORDER BY in SQL Server related to SQL performance
KB Article 2936154
SQL Server 2014 bug ” DTA utility crashes with an access violation when you use user-defined function in SQL Server with Management Tools
KB Article 2938192
SQL Server 2014 bug ” The execution of SSIS package takes a long time when you cannot enable garbage collector server mode in SSIS 2012 or SSIS 2014 with Integration Services
KB Article 2938476
SQL Server 2014 bug ” Errors when you use export/import data wizard to move data in SQL Server with Integration Services

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

 

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

 

CPU or Processor Speed Slowdown Errors in Windows Eventlogs

SQLServerF1

You may receive or notice below warning message in Windows System logs periodically.
Source: Microsoft-Windows-Kernel-Processor-Power
Event ID: 37
Task Category: (7)
Level: Warning
User: SYSTEM
Computer: ServerName
Description:
The speed of processor # in group # is being limited by system firmware. The processor has been in this reduced performance state for ## seconds since the last report.”

Starting with Windows Server 2008 R2, CPU power plan has been introduced where we can set the CPU power plan to either balanced or high performance. Each of these power plan has its own advantages and disadvantages. Balanced power plan consumes less power, but when the system or server is very busy, the performance will not be so good. With High performance power plan, the power consumption would be high, but on busy systems it dramatically improves the performance. So, on critical and important busy systems, it is advisable to use high performance CPU power plan. Also, in cases where you notices errors or warnings in event logs as mentioned above, go ahead and enable the high performance CPU power plan.

You can enabled this feature from control panel to either high performance or balanced power plan, but on some systems this may also have been enabled at BIOS level, so you will have to make changes both at control panel and BIOS level. Also on VMware or hyper-v or other virtual systems, it is required that this power plan is set to high performance on both guest and host servers or systems and also at BIOS level on the host server.

Below are the steps to change CPU power plan setting to High performance mode
– Open control panel and check the Power Options. Change or Make sure that the CPU Power Scheme is set to High Performance, and check that every option is set to “Always On”.
– Make sure latest drivers and firmware are installed on the server, especially for your motherboard and CPU.
– Reboot the server or system and go to your BIOS settings. Make sure that your power settings are set to maximum. This may differ from server to server.

Hope this was helpful.

This is applicable for below versions of Windows Servers

Windows Server 2008 R2
Windows Server 2012
Windows Server 2012 R2

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

 

Latest Updates on New Features in SQL Server 2016

SQLServerF1

Recently Microsoft has made an announcement about the upcoming release of SQL Server 2016. Although the announcement is made, first there will be few CTP releases used by community users and few company’s to test the new SQL Server 2016 product and to send their feedback and any bugs with the product. As days progress, Microsoft SQL Server team will work and fix and issues or bugs reported on the product and its features and ship newer updated versions. After CTP releases, once SQL Server team is confident, they will go ahead with the announcement about the official release of the product. No matter, how much ever testing is done on the new SQL Server 2016, still there will be log of bugs which will uncover while the product is being deployed on to our servers, which is because there are wide variety of environments which are all different, this testing for all kinds of environments in not possible. Sometimes there are silly bugs which end up due to improper testing.

Microsoft SQL Server 2016 team has announced new features or enhancements to the existing features to make the SQL Server product much better and scalable and tighter integration with the SQL Azure. Below are some of the new SQL Server 2016 features or enhancements to existing features in SQL Server 2016.
Enhancement to In-Memory OLTP Technology – Microsoft SQL Server 2016 team claims that the in-memory OLTP transaction will be 30X faster and in-memory ColumnStore Real-time operational analytics will be 100X faster.
Always Encrypted – SQL Server 2016 team claims that this technology will help in protecting the data at rest and in motion, for on-premises and in the cloud, with master keys sitting with the application, without any application changes.

Enhancements to High Availability features like with the ability to have up to three synchronous secondary replicas, MSDTC support, and load balancing of the secondary replicas.
Scale and Manage expected to enhance the performance, scalability and usability across SQL Server Enterprise Information Management tools and Analysis Services.
Powerful Insights on any Device – Business insights through rich visualizations on mobile devices. Native apps for Windows, iOS and Android. New modern reports for all browsers.
Advanced Analytics at massive scale – Built-in advanced analytics provide the scalability and performance benefits of running your advanced analytical algorithms directly in SQL Server.
Stretch Database – SQL Server 2016 team claims that this technology keeps your historical data at your fingertips by transparently and stretching your warm and cold OLTP data to Microsoft Azure on-demand without application changes.
Temporal Database to Track historical changes.
Enhanced Database Caching – Cache data with automatic, multiple TempDB files per instance in multi-core environments.
Easy migration of on-premises SQL Server using simple steps to migration to SQL 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

 

Latest Update on SQL Server 2016

SQLServerF1

The following release of Microsoft’s well known SQL Server database programming will get to be accessible as an open see this mid year, Microsoft declared today at its undertaking focused Ignite meeting. The following real arrival of Microsoft’s leader database and examination stage gives leap forward execution to mission-discriminating applications and more profound bits of knowledge on your information crosswise over on-premises and cloud.

The discharge will accompany a few new highlights, incorporating encryption of information in movement and very still and a combination with the R programming dialect, taking after Microsoft’s securing a year ago of R appropriation supplier Revolution Analytics. SQL Server 2016 will likewise accompany Stretch Database, a highlight that permits organizations to “extend” certain information that isn’t regularly gotten to into Microsoft’s developing Azure open cloud.

Microsoft has been concentrating on its half breed cloud procedure recently as an approach to separate itself from other significant cloud suppliers, including Amazon Web Services and Google Cloud Platform. Other Microsoft items declared at Ignite today, similar to Azure Stack and Operations Management Suite, show that.

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

 

Latest Developments on New SQL Server 2016

SQLServerF1

Microsoft has made an announcement regarding the new and latest plans about next release of SQL Server which is SQL Server 2016. Satya Nadella, CEO of Microsoft, announced SQL Server 2016, as an intelligent platform for a mobile first, cloud first world. The significant arrival of Microsoft’s lead database and examination stage gives achievement execution to mission basic applications and more profound bits of knowledge on your information crosswise over on-premises and cloud. Some of the latest features that may come up with SQL Server 2016 are as mentioned below which are reported in the technet blog.

Always Encrypted – With Always Encrypted, SQL Server can perform operations on encrypted data and best of all, the encryption key resides with the application in the customers trusted environment. Encryption and decryption of data happens transparently inside the application which minimizes the changes that have to be made to existing applications.

Stretch Database – This technology allows you to dynamically stretch your warm and cold transactional data to Microsoft Azure, so your operational data is always at hand, no matter the size, and you benefit from the low cost of using Microsoft Azure. You can use Always Encrypted with Stretch Database to extend your data in a more secure manner for greater peace of mind.

Real-time Operational Analytics & In-Memory OLTP – For In-Memory OLTP, which customers today are using for up to 30x faster transactions than disk based systems, you will now be able to apply this technology tuned for transactional performance to a significantly greater number of applications as well as benefit from increased concurrency. With these enhancements, we also introduce the unique capability to use our in-memory columnstore delivering 100X faster queries with in-memory OLTP for in-memory performance and real-time operational analytics.

Built-in Advanced Analytics, PolyBase and Mobile BI – SQL Server 2016 expands its scope beyond transaction processing, data warehousing and business intelligence to deliver advanced analytics as an additional workload in SQL Server with proven technology from Revolution Analytics. Building PolyBase into SQL Server, expanding the power to extract value from unstructured and structured data using your existing T-SQL skills. With this wave, you can then gain faster insights through rich visualizations on many devices including mobile applications on Windows, iOS and Android.

visit the SQL Server 2016 preview page(valid URL) to read about the capabilities of SQL Server 2016 and sign-up to be notified once the public preview is available.

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

 

Node and Cluster Edition Match Setup Rule Check Failed

SQLServerF1

One of the common task of a DBA is to perform installation of SQL Server or applying patches. Most often of not the installation completes successfully without any errors, however sometimes the setup fails with small and simetimes complex weird errors. Unfortunately there are also great lot of bugs with the setup media as well. Starting with SQL Server 2008, the cluster installation has changed where we have to install SQL Server as cluster instance on first node and then run ADD Node operation on remaining all other nodes. Below is one of the error we faced in our environment while performing ADD node operation of SQL Server cluster instance.

Rule Check Result
Rule “Node and cluster edition match” failed.
The edition of the new node does not match the edition of the cluster.
From Details.txt log file
Microsoft.SqlServer.Configuration.SetupExtension.AddNodeEditionBlock
Slp: The edition user specified is: ENTERPRISE
Slp: The edition of the cluster instance: STANDARD
Slp: Evaluating rule : AddNodeEditionBlock

The error message in this case is pretty clear which states that the edition is not matching. As we have already performed installation of SQL instance as cluster in first node, and we are performing add node operation, it is possible that the setup media used to run add node operation can be of different edition than what was installed on first node. Checking the detail.txt setup log clarifies the doubt where it specifies that the edition of SQL Server setup media used is ENTERPRISE, but the SQL Server cluster instance to which we are trying to add is STANDARD edition, thus the mismatch. It is not possible or allowed to use different editions on each node, it has to be same.

Sometimes the errors are small and has clear message, so it is just a little common sense to understand and fix the problem. This kind of issue is very rare as installation on all cluster nodes are performed by same DBA on same day in a span of few days, but in some cases where installation of one or more nodes is performed and after few months or years, they decide to install another node. In such cases it is better to review the existing environment rather than directly performing the installation.

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

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

 
1 2