Always On in SQL Server 2017 (Read-Scale Feature)

Always On in SQL Server 2017 (Read-Scale Feature)

SQL server 2017 has introduced new feature and technology (read-scale availability group) with Always on. Now, Always On availability groups provide high availability, disaster recovery and read-scale balancing.

Read-Scale Availability Group: This architecture provides replicas for read-only workloads but no HA. There is a question why we need this new architecture:

In SQL Server 2016 and before, all availability groups required a cluster and cluster provides a business continuity HADR. SQL Server 2017 introduces read-scale availability groups without a cluster.

This feature is very useful in case if the business needs to keep the resources for mission-critical workloads running on the primary then users can route to readable secondary replicas without using any cluster technology.

CLUSTER_TYPE:  There are three different cluster types which are used while creating availability group as below

  • WSFCWinows server failover cluster. On Windows, it is the default value for CLUSTER_TYPE.
  • EXTERNALA cluster manager that is not Windows server failover cluster – for example, on Linux with Pacemaker.
  • NONENo cluster manager. Used for a read-scale availability group.

How to get the details for cluster type used in availability group:

DMV:  sys.availability_groups

Two new columns:  cluster_type and cluster_type_desc

T-SQL:

Select name as ‘Availability Groups ’, cluster_type, cluster_type_desc from sys.availability_groups.

Thanks for reading!

Awaiting your valuable responses…

 

 

Posted in always on, Clustering, Database Administration, T-SQL | Tagged , , , , , , , , , | Leave a comment

10 Most Important handy system stored Procedures for DBA

10 Most Important handy system stored Procedures for DBA

This blog contains the most important system stored procedures which are supported by SQL Server and are used on a daily basis by DBA. These stored procedures are used commonly for maintenance of the server instance.

  1. Sp_helpdb

It provides information about the databases as mentioned in picture below. If any specific database is specified with this command then the information will be displayed about only that database.

sp_helpdb

  1. Sp_who2 

It provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.

sp_who2

  1. Sp_spaceused

It displays the disk space reserved and used by the whole database (any specific database).

sp_spaceused

  1. Sp_databases

It shows the database information for all databases on an instance of  a server as below.

sp_databases

  1. Sp_server_info

This stored procedure returns a list of attribute names and matching values for SQL Server.

sp_server_info

  1. Sp_tables

It displays the information about the tables, name and type etc.

sp_tables

  1. sp_lock

It reports information about locks.

sp_lock

  1. sp_monitor

It displays statistics about Microsoft SQL Server.

sp_monitor

  1. sp_updatestats

It runs UPDATE STATISTICS against all user-defined and internal tables in the current database.

  1. Sp_configure

It displays or changes global configuration settings for the current server.

sp_configure

Remarks: For more information and more stored procedures, please review Microsoft Blog. It is recommended to execute these stored procedures and see the results in detail which will be beneficial.

 

 

Posted in Database Administration, Sql Server | Tagged , , , , , , , , , , , , | Leave a comment

MS SQL Server Version History – Year Wise

MS SQL Server Version History – Year Wise

MS SQL Server is a RDBMS (Relational Database Management System) which is developed by Microsoft.

Here we will go through a series of different versions of SQL Server with specified year when it was launched\born.

Being a DBA we must know which version we are working on and what were the previous versions because this knowledge will help us to solve the compatibility issues in SQL Server.

SQL Server Release History

Release Name Version Year
SQL Server 1.0 (16-bit) 1.0 1989
SQL Server 1.1 (16-bit) 1.1 1991
SQL Server 4.2A (16-bit) 4.2A 1992
SQL Server 4.2B (16-bit) 4.2B 1993
SQL Server 6.0 6.0 1995
SQL Server 6.5 6.5 1996
SQL Server 7.0 7.0 1998
SQL Server 2000 8.0 2000
SQL Server 2005 9.0 2005
SQL Server 2008 10.0 2008
SQL Server 2008 R2 10.50 2010
SQL Server 2012 11.0 2012
SQL Server 2014 12.0 2014
SQL Server 2016 13.0 2016
SQL Server vNext 14.0 2017

Command to check version in SSMS-

Select @@version

Posted in Database Administration, Sql Server | Tagged , , , , , , , | Leave a comment

AlwaysOn Pre-Requisites

AlwaysOn Pre-Requisites

 1.Windows 2.SQL Server 3. Databases

Hotfix for .NET 3.5 SP1 adds support to SQL client for AlwaysOn.

Windows Requirement:

  1. The system should not be in a domain controller
  2. Windows server 2012 or later versions should be running on each computer.
  3. Each and every computer or node should be in WSFC (windows server failover clustering).
  4. Ensure that WSFC cluster contains sufficient nodes to support availability group configurations.
  5. 5.   The user must be a system administrator on every cluster node to administer a WSFC
  6. 6.To ensure about sufficient disk space, dedicated network adapter and coparable systesm before proceeding.

SQL Server Prerequisite:

  1. The host computer must be a windows server failover clustering (WSFC) node.
  2. Each and Every server instance must be running the Enterprise Edition of SQL Server 2016.
  3. All the server instances involved in availability group must use the same sql server collation.
  4. Make sure the Always on Availability groups feature be enable on each server instance.
  5. Each server instance requires a database mirroring endpoint.
  6. Sysadmin fixed server role requries to create endpint.

Database prerequisite:

  1. Only user databases are allowed in availability group , system databases doesn’t.
  2. The database should be a read-write database. Read-only databases can’t be added to the AG.
  3. The database must be a multi user database.
  4. Full recovery model is needed for all the databases.
  5. We need to have atleast one full database backup for each database.
  6. The database should not belong to any existing availability group.
  7. The database should be be configured already for database mirroring

 

Looking forward your inputs\suggestions\likes on the same!

Posted in Clustering, Database Administration | Tagged , , , , , , , , , , , , , | Leave a comment

Resolve Microsoft SQL Server Attach Database Error Code 5120

When the users try to attach a database to SQL server then sometimes it becomes difficult to do this due an error. This error is known as Microsoft SQL Server error 5120 access denied. The error message occurs on the user’s screen is mentioned-below:

error-5120

Error code 5120 occurs, when the account that is running SQL server service does not have the permission to access MDF and LDF files of a database. Therefore, in this post, we are going to discuss the solution to fix the error message.

Fix Attach SQL Database Error 5120

Create two users account such as SQLAdminOne and SQLAdminTwo on the SQL server and then follow the below-mentioned steps to solve the error 5120 in SQL server:

  • Create A New Database: Follow the below-mentioned steps to create a new database such as Test:
  • CREATE DATABASE [Test]
    CONTAINMENT = NONE
    ON PRIMARY
    (NAME = N'Test', FILENAME = N'D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test.mdf')
    LOG ON
    (NAME = N'Test_log', FILENAME = N'D:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\DATA\Test_log.ldf')
    GO

  • Check Test File Permission For MDF and LDF: Browse the location of MDF and LDF files and then right-click on the file >> Properties >> Security
  • error-5120-2

  • SQLAdminOne Detaches Test Database: Right-click on the Test database>> Tasks >> Detach
  • Check Test File Permission For MDF And LDF Again: Test database MDF and LDF files permissions have changed and full permission are granted to SQLAdminOne only.
  • error-5120-3

  • SQLAdminTwo Tries To Attach Test Database: When SQLAdminTwo tries to attach the Test database SQL error code 5120 occurs due to the limited permission on the database files
  • Grant Full Permission On Both MDF and LDF Files Of Test Database: Follow the below-mentioned steps to grant the full permission on the Test database file:
    • Right-click on the Test database file and then select Properties
    • Now, click on Security tab and then click on Edit button
    • Click on the Add button and then enter the object name to select as NT Service\MSSQL$SQL2013. After that, click on Check Names button and then click on OK
    • Now, give this account Full Control to the file and then click on OK button
    • error-5120-4

  • Reattach Test database: SQLAdminTwo become successful to attach the Test database

Conclusion

Sometimes, the users of SQL server faced MS SQL server error 5120 when they try to attach a database in SQL server. This error mainly arises when the user’s account does not have access permission on a database (MDF and LDF) files. To attach a database file into SQL server, the account that is running on MS SQL server service should have the full access permission on both MDF and LDF files of a database.

Posted in Sql Server | Tagged , , | Leave a comment

Shift Handover importance in a DBA job- DBA Interview Question

One of my blog readers asked me about shift Handover in a DBA job and how to prepare it. What is the importance of it for a DBA?

I am trying to reply and the best possible points to ponder.

Handing File Folder, teamwork concept

Handing File Folder ,teamwork concept

In a 24/7 production environment, handover plays an important role for all DBA team members. Hand over is a list of running, pending activities which is given from the current shift to coming shift members during the overlap timing in a DBA team so that  all members must aware of all the activities and they can proceed and follow up the same in the absence of other team members.

Handover format may be Excel sheet or company specific standard template.

A summary of few points are below to ponder & useful for preparing appropriate Handover:

  1. To share all details of all on going activities e.g. ticket number, server name, database name etc.
  2. Need to provide the information about any new task assigned in the shift.
  3. Provide the information about any issue which needs to be troubleshooting and reply to client.
  4. To prepare an activity plan if there is any upgrade or any project work needs to be done in near future.
  5. Follow up emails or communication which is pending to reply.
  6. Any meeting or conference call with client needs to cover up, mention the details in the list.
  7. Shift reports if pending and need to send to client or within team or to the management.
  8. Any technical document which needs to be prepared.
  9. Any technical session, discussion within team, with other teams or with client needs to be in the list.
  10. Any other task specifically assigned to specific DBA, needs to be shared with him\her.

If anyone has any other points please make the comments so that people may get advantage.

Looking forward your likes & comments!!!

Posted in Database Administration, Other, Q & A | Tagged , , , , , , , , , | 1 Comment

Always On – Interview Notes

Always On

Always on Availability Groups is a solution which improves database availability and supports a failover environment for a discrete set of user databases.

It is introduced in SQL server 2012.

Benefits:

  1. With the release of Windows Server 2016, Windows Server Failover Clusters will no longer require that all nodes in a cluster reside in the same domain.
  2. You can now form a WSFC (windows server failover cluster) cluster from machines which are in workgroups. Because of this change, SQL Server 2016 is now able to deploy Always On Availability Groups in environments with:
  • All nodes in a single domain
  • Nodes in multiple domains with full trust
  • Nodes in multiple domains with no trust
  • Nodes in no domain at all
  1. Availability Modes:
  • Asynchronous-commit mode: This availability mode is a disaster-recovery solution that works well when the availability replicas are distributed over considerable distances.
  • Synchronous-commit mode. This availability mode emphasizes high availability and data protection over performance, at the cost of increased transaction latency. A given availability group can support up to three synchronous-commit availability replicas, including the current primary replica.
  1. Always On Availability Groups Supports up to nine availability replicas.
  2. Zero data loss protection in case of synchronous data flow.
  3. Failover of multiple databases.
  4. Active use of secondary replica for the purpose of backup and read only operation.
  5. It supports three major failover forms of availability group
  • Automatic
  • Manual
  • Forced
  1. A listener is assigned to each availability group which is a serve to which client can connect in order to access a database in a primary or secondary replica of AAG.
  2. It supports automatic page repair in case of a page corruption.

 

Note: Always on another blog coming soon ……

 

Posted in Clustering, Database Administration | Tagged , , , , , , , , , , , | Leave a comment

Interview Questions

Basic interview questions and answers for my fresher technical friends:

Q & A

  1. Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.

  1. Which command using Query Analyzer will give you the version of SQL server and operating system?

Select serverpropperty (‘productversion’),

Select serverproperty ( ‘productlevel’),

Select serverproperty (‘edition’).

3. Name the system databases?

  • Master
  • Model
  • MSDB
  • Temp
  • Resource (Hidden)

4.  Name the recovery models in Database?

  • Full
  • Bulk_ logged
  • Simple

5. What does DBCC stands for?

DBCC stands for database consistency checker

  1. What is the fundamental unit of storage in SQL Server data files and what is its size?

A page with a size of 8kb

  1. How many different types of pages exist in SQL Server?
    • Data
    • Index
    • Text/Image (LOB, ROW_OVERFLOW, XML)
    • GAM (Global Allocation Map)
    • SGAM (Shared Global Allocation Map)
    • PFS (Page Free Space)
    • IAM (Index Allocation Map)
    • BCM (Bulk Change Map)
    • DCM (Differential Change Map)
  1. What is SA login in SQL Server?

SA stands for System Administrator and it is the most powerful login in sql server.

  1. What are the authentication modes available in SQL Server?
  1. Windows
  2. Mixed
  1. What is the default fill factor value?

Default fill factor value is 0 or 100

  1. What are the DCL commands?

DCL- Data control Language

Commands:

  • Grant
  • Revoke
  • Deny

 

Posted in Database Administration, Q & A | Tagged , , , , , , , | Leave a comment

SQL Injection

sqli

SQL Injection (SQLi) refers to an injection attack wherein an attacker can execute malicious SQLstatements (also commonly referred to as a malicious payload) that control a web application’s database server. Such attack affects any website or web application.

An attacker can bypass a web application’s authentication and authorization mechanisms and retrieve the contents of an entire database. SQL injection can also be used to add, modify and delete records in a database.

In a 2012 study, it was observed that the average web application received 4 attack campaigns per month, and retailers received twice as many attacks as other industries.

A SQL injection needs two conditions to exists :

  • A relational database that uses SQL
  • A user controllable input which is directly used in an SQL query.

Subclasses of SQLi –

  1. Classic SQLi
  2. Blind or Inference SQLi
  3. Database management system-specific SQLi
  4. Compounded SQLi

Example—

sql-example-1

Here user need to provide user name and password, if attacker provides ‘or 0=0’ as the username and password then the query will be like this.

sql-example-3

Since the inputs provided by the attacker are valid in all circumstances, the query will return all records in the database.

And by this way an attacker will be able to view the sensitive information.

stop-sqli

  • Adopt an input validation technique where user input is checked against a set of rules.
  • Users should have least privileges on the database.
  • Don’t use ‘SA’ accounts for web applications.
  • Need to have application specific database user accounts.
  • Remove all stored procedures which are not in use.

——————————-

Posted in Database Administration, Other, Security, Sql Server, T-SQL, Uncategorized | Leave a comment

SQL Server 2012 Features:5: 5. Windows server core support

In continuation of previous blog-

Server Core is a minimalistic Microsoft Windows server installation option.

You can install SQL Server on a Server Core installation of Windows Server 2008 R2 SP1 or Windows Server 2012. This option of windows server 2012 provides a minimal environment for running specific server roles. This helps to reduce maintenance and management requirements and the attack surface for those server roles. Starting with Windows Server 2012, it is possible to transition between Full Server and Server Core without reinstalling the operating system.

Server Core is designed to provide an environment that reduces:

  • Servicing requirements
  • Management requirements
  • Attack surface
  • Disk space usage
  • Memory (RAM) usage

Server Core supports the following server roles on Windows Server 2012 and Windows Server 2012 R2:

  • Active Directory Domain Services (AD DS)
  • Active Directory Lightweight Directory Services (AD LDS)
  • DHCP Server
  • DNS Server
  • File Services
  • Hyper-V
  • Print Services
  • Streaming Media Services
  • Web Server (IIS)
  • Active Directory Certificate Services
  • Windows Server Update Server (WSUS)
  • Active Directory Rights Management Server
  • Routing and Remote Access Server, including the following sub-roles:
    • Remote Desktop Services Connection Broker
    • Licensing
    • Virtualization

Configuration Level: configuration levels in order of increasing complexity for windows server installations.

windows_server_installation_types_from_nt_to_vnext-svg

Note: In newer Windows versions there are more granularity in configuration levels.

Posted in Database Administration, Sql Server New Feature | Leave a comment