Introduction to Microsoft Azure

Microsoft Azure

Microsoft Azure is an open, flexible, enterprise-grade cloud computing platform. Azure was announced in October 2008 and released on February 1, 2010.

It provides

Software as a service (SaaS)

Platform as a service (PaaS)

Infrastructure as a service (IaaS)

Why is it different from other cloud computing?

  1. Microsoft cloud is global, trusted and hybrid.
  2. 42 Azure regions, more than any other cloud provider
  3. 90% of fortune 500 companies use the Microsoft cloud.
  4. 1,20,000 new Azure customer subscriptions per month
  5. 62 compliance offerings more than any other cloud provider

Microsoft Azure – Popular Services\Products

  1. Virtual Machines
  2. App Services
  3. SQL Databases
  4. Storage
  5. Cloud Services
  6. Azure Cosmos DB
  7. Azure Active Directory
  8. Backup
  9. SQL Data Warehouse
  10. Traffic Manager
  11. Azure Database for MySQL
  12. Azure Databases for PostgreSQL

signature_3

Advertisements
Posted in T-SQL, Database Administration, Azure | Tagged , , , , , , , , , , | Leave a comment

How to add and remove a database to an availability group in Always On

How to add and remove a database to an availability group in Always On

Rule:

  1. Connect to the server instance that hosts the primary replica.
  2. Use the T-SQL statement as below
  3. After you add a database to an availability group, you need to configure the corresponding secondary database on each server instance that hosts a secondary replica.

To add a database to an availability group

Syntax:

ALTER AVAILABILITY GROUP group_name ADD DATABASE database_name [,…n]

E.g.

Group Name = MirzaAG

Database Name= Mirza (Existing database in the instance)

T-SQL

ALTER AVAILABILITY GROUP MirzaAG ADD DATABASE Mirza

How to start data synchronization after adding a database to an Always On availability group

  1. For each new primary replica, secondary databases must be prepared on the server instances that host the secondary replicas. Then each of these secondary databases must be manually joined to the availability group.
  2. If the file paths are identical on every server instance that hosts a replica for an availability group then the data synchronization starts automatically else we have to manually to do it as below.

 To start data synchronization manually follow these steps:

  1. Restore current backups of each primary database and its transaction log (using RESTORE WITH NORECOVERY). You can use either of the following alternative approaches:
    • Manually restore a recent database backup of the primary database using RESTORE WITH NORECOVERY, and then restore each subsequent log backup using RESTORE WITH NORECOVERY. Perform this restore sequence on every server instance that hosts a secondary replica for the availability group.
    • As soon as possible, join each newly prepared secondary database to the availability group.

 To remove an availability database,

Rule:

  1. This task is supported only on primary replicas. You must be connected to the server instance that hosts the primary replica.
  1. Requires ALTER AVAILABILITY GROUP permission on the availability group,
  2. Use the Alter Availability Group T-SQL statement

Syntax:

ALTER AVAILABILITY GROUP group_name REMOVE DATABASE availability_database_name

e.g.  Group_Name = MirzaAG

Database Name= Mirza

T-SQL

 Alter availability group MirzaAG remove database Mirza

Summary:

Removing an availability database from its availability group ends data synchronization between the former primary database and the corresponding secondary databases. The former primary database remains online. Every corresponding secondary database is placed in the RESTORING state.

At this point there are alternative ways of dealing with a removed secondary database:

  • If you no longer need a given secondary database, you can drop it.
  • If you want to access a removed secondary database after it has been removed from the availability group, you can recover the database. However, if you recover a removed secondary database, two divergent, independent databases that have the same name are online. You must make sure that clients can access only one of them, typically the most recent primary database.

Looking forward your valuable likes and comments!

Facebook Page:  https://www.facebook.com/MirzaDBA/

 

 

 

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

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