Top 8 Features of SQL Server 2016


SQL Server - SeniorDBA

Microsoft recently announced the top eight features of SQL Server 2016. The earlier versions of SQL Server has had unparalleled performance, but Microsoft mat have outdone themselves this time with groundbreaking additional features. Microsoft SQL Server is the most popular commercial enterprise-level relational database management system available today. You will be able to scale up to 12TB of memory, reach up to 30x faster transactions and 100x faster queries with enhanced in-memory performance, and run real-time operational analytics over transactional data. Do you agree on their feature selection?

  • In-Memory OLTP helps meet business requirements for increased agility. In-memory OLTP has continued to mature in SQL Server 2016 by extending the functionality to more applications while enhancing concurrency. Microsoft expanded the T-SQL surface area, increasing the number of memory supported into the terabyte range as well as supporting a greater number of CPUs.

Continue reading “Top 8 Features of SQL Server 2016”

Advertisements

Reset SQL Server SA Password on Linux

The “sa” account is a critical account on all versions of SQL Server. If you forget your “sa” password, it can cause problems. This article will help you identify the steps required if you have forgotten the password of your “sa” account of your SQL Server instance on Linux.

The mssql-conf utility is a configuration tool that installs with SQL Server on Linux (Red Hat Enterprise Linux, SUSE Linux Enterprise server, and Ubuntu). This handy tool as a replacement for SQL Server Configuration Manager which exists when you install SQL Server on Windows.

Reset the “sa” account password 

  • Connect SQL Server using command-line tool with the existing password to make sure that your current password is working. You will skip this step if you don’t know the password.
sqlcmd -S <SQLInstanceName>-U <UserName> -P <Password>

  • To change the “sa” password, first stop SQL Server service on Linux:
sudo systemctl stop mssql-server
sudo systemctl status mssql-server
  • Reset the “sa” password by creating a new strong password :
/opt/mssql/bin/mssql-conf set-sa-password

Note: When you are resetting/changing “sa” password using sqlcmd in a bash terminal,  you must not use the special character “$”. 

  • Start and verify the status of SQL Server Service:
sudo systemctl start mssql-server
sudo systemctl status mssql-server

  • Connect SQL Server with the new password:
sqlcmd -S <SQLInstanceName>-U <UserName> -P <Password>

  • Once you are successfully connected, you know the new password is correctly altered.

Change password using sp_password

  • Login with any User account that has sysadmin access on the server.
  •  syntax: sp_password NULL, ‘<insert_new_password_here>’, ’sa’
  • command :-

sp_password NULL, 'Admin@1433', 'sa'

  • Log into the SQL Server instance using the “sa” account and the new password to verify it has been successfully changed.

Free Download: SQL Server Management Studio 17.4

SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

Microsoft has announced the latest release of SQL Server Management Studio (SSMS) in October. This release features improved compatibility with previous versions of SQL Server and a stand-alone web installer.

Get it here:

Continue reading “Free Download: SQL Server Management Studio 17.4”

TIOBE Index for December 2017

Have you seen the latest TIOBE rankings report?

The TIOBE Programming Community index is an indicator of the popularity of programming languages. The index is updated once a month. The ratings are based on the number of skilled engineers world-wide, courses and third party vendors. Popular search engines such as Google, Bing, Yahoo!, Wikipedia, Amazon, YouTube and Baidu are used to calculate the ratings. Observe that the TIOBE index is not about the best programming language or the language in which most lines of code have been written.

This month R and MATHLAB have made huge improvements, but the big news is Visual Basic.Net has dropped out of the top 5.

Dec 2017 Dec 2016 Change Programming Language Ratings Change
1 1 Java 13.268% -4.59%
2 2 C 10.158% +1.43%
3 3 C++ 4.717% -0.62%
4 4 Python 3.777% -0.46%
5 6 change C# 2.822% -0.35%
6 8 change JavaScript 2.474% -0.39%
7 5 change Visual Basic .NET 2.471% -0.83%
8 17 change R 1.906% +0.08%
9 7 change PHP 1.590% -1.33%
10 18 change MATLAB 1.569% -0.25%

The TIOBE Top 10 for this month:Read the entire results at this site.

How Database Joins Work

This image from Essential SQL and Kris Wenzel shows an overview of how joins work in modern databases. I couldn’t find the image on Essential SQL, but I did stumble across it on Pinterist.

Database-Joins

Common Database Design Mistakes

Project Management

When creating a new database instance, people will often make mistakes. While I can’t list all the mistakes that people can or will make, I hope this brief list will help you know what mistakes are possible, and help guide you to not making as many mistakes. Sometimes we attack a design problem with the idea that we will just get the work done, but most times it is better to take the extra time to do it right.

I’m not perfect, and I have made these (any many other) mistakes in database design. I’m not trying to tell you what to do or even how to do it. I’m just trying to take my lessons learned and provide a simple list so that you might not make the same mistakes. I also want to point out that no list will ever be the only way to do anything. With database design questions, the best answer is usually “it depends”. When considering the many variables that make up your environment, you will need to make many decisions that help your database instance work best in your unique environment. You have to take into account the personnel you are working with, limits of your hardware, company policies, etc.

Continue reading “Common Database Design Mistakes”

Available Now: Microsoft SQL Server 2012 Service Pack 4

Service Pack - @SeniorDBA

Microsoft’s SQL Server development team has delivered the final service pack release for SQL Server 2012. This latest service pack is now available for download on the Microsoft Download Center and will be available to Visual Studio Subscriptions, MBS/Partner Source, and VLSC. SQL Server 2012 Service Pack 4 (SP4) contains a roll-up of released hotfixes as well as more than twenty improvements centered around performance, scalability, and diagnostics based on the feedback from their customers and the SQL community.

SQL Server 2012 Service Pack 4 includes:

  • All fixes and Cumulative Updates (CUs) for SQL Server 2012 up to and including SQL Server 2012 SP3 CU10.
  • Scalability and performance improvements for SQL Server.
  • Additional monitoring capabilities through enhancements in DMV, Extended Events and Query Plans and the ability to clone the database including statistics with DBCC CLONEDATABASE.
  • New improvements based on Connect feedback items filed by the SQL Server Community.
  • Some of the improvements originally introduced in SQL Server 2014 SP2 and SQL Server 2016 SP1.

Continue reading “Available Now: Microsoft SQL Server 2012 Service Pack 4”