Archive
SQL DBA – Windows could not start the SQL Server… refer to service-specific error code 17051 – SQL Server Evaluation period has expired
Ok, one fine day you opened SSMS (SQL Server Management Studio) and tried to connect to a SQL Instance, but it is not getting connected. You are getting following error message on a popup box:
Cannot connect to XYZ_Instance. ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2) The system cannot find the file specified.
–> You may also get following error:
—————————
Microsoft SQL Server Management Studio
—————————
Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy
—————————
OK
—————————
–> Investigate
Go to RUN and type services.msc to open Services window and see that SQL Server services are not running. On starting the SQL Server service it is giving you following message with error code 17051:
Now as per the error message you open the Event Viewer and see that the Event with ID 17051, which shows SQL Server Evaluation period has expired under Details section:
Now, you recall that the Instance that you had installed was under Evaluation of 180 days, because you didn’t applied any Product Key. So, now how can you make it live again? All you need is a Product key of SQL Server and some clicks:
1. Open the SQL Server Installation Center and click on Maintenance link, and then click on Edition Upgrade:
2. Now on the Upgrade window Click Next and you will reach the Product Key page, apply the Key and click Next:
3. On the Select Instance page, select the SQL Instance that you want to fix and Click next. It will take some time and finally you will see a final window and click Upgrade:
4. Finally you will see the successful window, click on Close button:
–> But, if the above process fails at Engine_SqlEngineHealthCheck step or anywhere in between, then you can use following command line installation option to skip this specific rule to allow the upgrade process to complete successfully:
a) Open Command Prompt (CMD)
b) Go to the folder where SQL Server Setup, setup.exe file is located (like C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\)
c) Apply following command: setup.exe /q /ACTION=editionupgrade /InstanceName=MSSQLSERVER /PID=<appropriatePid> /SkipRules= Engine_SqlEngineHealthCheck
The <appropriatePid> should be the 25 digit Key.
The above command line runs SQL Server setup in silent mode.
5. Now Restart the SQL Server Service for this Instance, and you will see it running fine.
–> Finally, go back to SSMS and now you can connect to the SQL Instance.
Query Store – new feature in SQL Server 2016
In my [previous blog post] on “SQL Server 2016 Enhancements” we discussed about the new features coming in. Query Store is on of those feature that will add a lot value in troubleshooting and getting insights on Query & overall performance of a database.
–> Query store is a new component in SQL Server that captures Queries, Query Plans, Runtime Statistics, etc. in a persistent store inside a database. You can think of it as a aircraft’s black box, for your database.
It can also enforce policies to direct the SQL Server Query Processor to compile queries to be executed in a specific manner, such as forcing plans.
It is a database-scoped persistent store of query workload history. Query store is primarily targeting administrative scenarios for performance troubleshooting and identifying regressed workloads.
Query store collects query texts and all relevant properties, as well as query plan choices and performance metrics. This collection process works across restarts or upgrades of the server and across recompilation of indexes
It provides many configurable options for customization and it integrates with existing Query Execution Statistics, Plan forcing, and Manageability tools.
–> Query Store lowers the bar dramatically for performance troubleshooting and allows you to perform several kinds of scenarios:
– Conduct system-wide or database-level analysis and troubleshooting
– Access the full history of query execution
– Quickly pinpoint the most expensive queries
– Get all queries whose performance regressed over time
– Easily force a better plan from history
– Safely conduct server restarts or upgrades
– Identify issues with upgrades
–> Typical performance troubleshooting workflow:
–> All data that query store stores will be available through the following Views:
– sys.query_store_query_text
– sys.query_store_query
– sys.query_store_plan
– sys.query_context_settings
– sys.query_store_runtime_stats_interval
– sys.query_store_runtime_stats
By combining these views in various queries, you will gain necessary insights about user workload for a period of time when Query Store was active. Query Store also has a rich UI component that uses several of these same DMVs as shown below:
I’ve collated all these details from SQL Server white papers and other sources from Microsoft.
Check my [next post] on how to enable the Query store on a database from SSMS.
Installing SQL Server 2016 CTP2 | and new features in SSMS
SQL Server 2016 was released as the Community Technology Preview (CTP) 2 just yesterday (27th May 2015). And in my [previous post] I discussed about it with the Registration and Direct Download link.
After downloading the bits I installed it on my machine and the setup process was very smooth with few changes, check the YouTube video or go through the post below:
1. After you execute the Setup.exe: you will be greeted with the similar Install window You just need to click on the Installation link on the left and then click on the first link shown below:
2. This will open up a new Setup window and takes you to the Setup Role page which this gives you 2 options, 1st Feature (custom) install and 2nd Default. I chose the first option to select only the features I want. Click Next.
3. Now on the Feature Selection page: you can select what exactly you want to work on. You can see here one more option PolyBase Query Service for External Data. Just select the features and click Next:
4. I got stuck at the the Feature Rule page: with this error Oracle JRE 7 Update 51 or higher is required, Failed. Just click on the Failed link and it will give you the URL from where you can download the JRE or Java Runtime Environment, download it from [here].
… the above Page also shows the limitation of Polybase that it will run only in one Instance per computer.
Re-run the rules and it will be Passed this time, click Next.
5. You will come to Instance Config page: If its is first SQL Server install on your PC then select as Default Instance, otherwise if you had installed SQL Server earlier you will have to choose a Named Instance and Click Next.
6. In the Database Engine Config page: you will see an extra option to set number of TempDB files. The label below mentions “The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores”.
Update: Now with CTP 2.4 release the installer provides a separate tab for tempdb files configuration.
So, in my [C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2016\MSSQL\DATA\] folder I could see 8 data files, with 1 log file:
7. on Ready to Install page just click Install, and it will take some time to finish the installation.
What’s new in SSMS:
–> After finishing the installation I opened the SQL Server Management Studio 2016: and the object explorer looked like this:
You will see three Databases present by default:
1. DWConfiguration
2. DWDiagnostics
3. DWQueue
… some of these tables in these databases contains pdw prefix, these tables & databases are related to Polybase feature. I will blog about this feature in my coming posts as in when I get more information on this.
–> I created a new Database by name TestManDB, and I got this while expanding some Objects in Object Explorer:
– – On expanding Tables you can see 2 new table options:
1. System Tables
2. External Tables – for Polybase to query Non-Relational data (also relational)
– – There is a new category External Resources (for Polybase), on expanding you get:
1. Data Sources
2. File Formats
– – And as you expand Stored Procedures folder you can see a SP present by default with following name under pdw schema:
– pdw.instpdw, with one parameter @DatabaseName NVARCHAR(MAX).
–> There is one hidden feature in SSMS, i.e. Query Store, check this [blog post] on how to enable it fr a database.
Microsoft SQL Server 2016 Public Preview (CTP2) Available – download now
Just got an email from Microsoft:
Microsoft SQL Server 2016 Public Preview Available – Try it Today!
[Register and Download CTP-2 Evaluation version (180 days)]
Direct download link:
– SQLServer2016CTP2-x64-ENU.box
– SQLServer2016CTP2-x64-ENU.exe
–> Smooth Installation with new setup-option and new features in SSMS:
Microsoft SQL Server 2016, the next major release of Microsoft’s flagship database and analytics platform, provides breakthrough performance for mission critical applications and deeper insights on your data across on-premises and cloud. The first public preview, SQL Server 2016 Community Technology Preview (CTP) 2, is now available for you to download to trial via Microsoft Azure.
–> Try it today for an early look at these new capabilities:
– Always Encrypted: helps protect data at rest and in motion
– Stretch Database: dynamically stretch your warm and cold transactional data to Microsoft Azure, demo video.
– Real-time Operational Analytics: our in-memory technologies are enhanced to provide real-time analytics on top of breakthrough transactional performance
–> Additional capabilities include:
1. PolyBase: More easily manage relational and non-relational data with the simplicity of T-SQL.
2. AlwaysOn Enhancements: Achieve even higher availability and performance of your secondaries, with up to 3 synchronous replicas, DTC support and round-robin load balancing of the secondaries.
3. Row Level Security: Enables customers to control access to data based on the characteristics of the user. Security is implemented inside the database, requiring no modifications to the application, demo video.
4. Dynamic Data Masking: Supports real-time obfuscation of data so data requesters do not get access to unauthorized data. Helps protect sensitive data even when it is not encrypted, demo video.
5. Native JSON support: Allows easy parsing and storing of JSON and exporting relational data to JSON, demo video.
6. Temporal Database support: Tracks historical data changes with temporal database support.
7. Query Data Store: Acts as a flight data recorder for a database, giving full history of query execution so DBAs can pinpoint expensive/regressed queries and tune query performance.
8. MDS enhancements: Offer enhanced server management capabilities for Master Data Services.
9. Enhanced hybrid backup to Azure: Enables faster backups to Microsoft Azure and faster restores to SQL Server in Azure Virtual Machines. Also, you can stage backups on-premises prior to uploading to Azure.
–> Other Benefits:
1. 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
2. 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
3. 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
4. Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android
5. Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology
6. 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, demo video.
7. 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
Learn more about SQL Server 2016: SQL Server 2016 Preview Page
Native JSON support – new feature in SQL Server 2016
In my [previous blog post] on “SQL Server 2016 Enhancements” we discussed about the new features coming in. Native JSON support is on of those feature that will help you support and manage NoSQL/Unstructured document data within a SQL Server database.
–> JSON (or JavaScript Object Notation) is a popular, language-independent, lightweight data-interchange format used in modern web and mobile applications, as well for storing Unstructured data. JSON is an alternate to XML and is more compact than that format, and thus has become the first choice for many applications that need to move data around on the web. One of the biggest reasons JSON is becoming more important than XML is that XML has to be parsed with an XML parser, while JSON can be parsed by a standard JavaScript function. This makes it easier and faster than working with XML.
For more details on JSON check at json.org
–> JSON in SQL Server 2016 is not treated as a separate Datatype by the DB Engine, like XML. For example, appending FOR JSON AUTO to a standard SELECT statement returns the result set in a JSON format. The JSON data is stored as NVARCHAR type, unlike XML datatype for XML data, thus JSON will be supported wherever NVARCHAR is supported.
–> Here is a sample & simple query to convert a row-set into a JSON format:
SELECT TOP 10
M.ProductModelID,
M.Name AS [ProductModel.Name],
ProductID,
P.Name AS [Product.Name],
ProductNumber,
MakeFlag
FROM Production.Product P
INNER JOIN Production.ProductModel M
ON P.ProductModelID = M.ProductModelID
FOR JSON PATH, ROOT('ProductModel') -- here, JSON syntax similar to XML
Check the last line, the syntax is almost similar to XML datatype. So, if you familiar with XML syntax in T-SQL, working with JSON will be a seamless experience.
Check my [next post] on how to work with JSON string with SQL queries.
Check this video on how to work with JSON and SQL:
–> More from MS BoL and whitepapers on JSON support:
JSON syntax is simple and human-readable. JSON values consist of name/value pairs, and individual values are separated by commas. Objects are containers of one or more name/value pairs and are contained within curly brackets. JSON arrays can contain multiple objects, and arrays are contained within square brackets.
JSON is the storage format used in several NoSQL engines, including Azure DocumentDB. DocumentDB uses Azure Blob storage to store schema-less documents, but provides a rich SQL query dialect that allows you to conduct SQL queries over the data contained in the documents. In addition to DocumentDB, Azure Search also utilizes JSON. Azure Search is a fully managed search solution that allows developers to embed sophisticated search experiences into web and mobile applications without having to worry about the complexities of full-text search and without having to deploy, maintain, or manage any infrastructure.
The combination of SQL Server’s new support for JSON with these other Microsoft tools enables many scenarios for moving data back and forth between relational and schema-less storage and the applications that access such data. For example, these tools would allow you to set up periodical extractions of relational data in SQL Server, transforming the data into JSON and loading it to a JSON-based Azure DocumentDB storage that is searchable from a mobile device, along with data from many other sources, utilizing Azure Search.
Check more on [MSDN BoL] for JSON support in SQL Server 2016.
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 