Comparison – SQL Database & SQL Server in Virtual Machine in Azure

Comparison – SQL Database & SQL Server in Virtual Machine in Azure

Summary:

This blog is based on the comparison of SQL database feature with SQL server installed in virtual machine in Azure. Which option is good or which one we need to opt to full fill our company\business requirements. There are multiple questions regarding this as –

  1. How can we setup database in Azure?
  2. What are the pre-requisite to work on Azure SQL Database?
  3. Can we install SQL Server and create databases there as we do in our premises on a server or VM?
  4. What are the different ways to create databases in Azure?
  5. What are the features available in SQL Database in Azure?
  6. What are the benefits to use SQL Database instead of SQL server in Virtual Machine? Etc. etc.

SQL Database

Explanation:

As Azure is progressing and capturing the IT market by leaps and bounds and companies also have started moving to Cloud and taking services from Azure.

Therefore such questions may arise in the minds of the Database Administrators regarding the databases and their creation/migration/deletion etc. etc.

To answers such questions I am writing this blog to help you out by clearing the doubts. Firstly I would like to tell you that there are two options to work on SQL databases in Azure:

  1. SQL Database in Azure as a service
  2. Create Virtual Machine and use SQL server by installing it

We can work on SQL databases by using either option however there are few differences that’s why both options are available in Azure. First option SQL Database in Azure give us almost all the features and we can use it Platform as a service (PaaS) but we can’t find all the features here.

To get all the features of SQL Server we have to create virtual machine and then install SQL Server into it as we do in our premises. If we compare SQL Database and SQL server in virtual machine, we will find a list of features not available in SQL database. Few common features which are not available in SQL Database  as below:

  1. Windows Authentication
  2. Filestream data
  3. Database Mirroring
  4. Extended Stored Procedures
  5. SQL Server Agent/Jobs
  6. SSRS & SSIS doesn’t support.

SSRS- SQL server reporting Services

SSIS – SQL Server Integration Services

T-SQL Features:

  1. Use statement is not supported. To change databases, a new connection must be established.
  2. Common Language Runtime (CLR)

These are few common features which are not available in SQL Database in Azure. To view the complete list please go through the Microsoft link

https://azure.microsoft.com/documentation/articles/sql-database-general-limitations/.

However the question is then why we need to use SQL Database instead of traditional SQL Server in VM.

The decision to use SQL Database or SQL Server in Virtual Machine can be difficult. There are many factors to consider when choosing between SQL Database and SQL Server in Azure Virtual Machine and these factors may be as database size, existing application versus new application, administration, business continuity etc.

Priorities:

SQL Database is often the right solution for cloud –designed application that are not using unsupported features and need near zero administration.

Azure virtual machine is often the right choice for new or existing application that require a high level of control and customization and which doesn’t require hardware maintenance on premise in future.

Advantages:

Using SQL Database in Azure

  1. Elastic Scale
  2. Predictable Performacne
  3. Business continuity
  4. Near-zero maintenance
  5. Low costCost Difference between SQL Database & SQL Server in VM

    We can understand the cost difference as below:

    Total Cost for SQL Server in VM = Windows Server Cost +SQL Server Licence Cost + Azure Storage cost

    Cost for SQL Database = SQL Database is considered as a service and it is charged per hour basis. There is no cost for VM, licence etc.

    Conclusion

    Therefore I would like to state that it totally depends on the business needs (database size, application type, new vs old application, business continuity etc )whether we need to use SQL Database feature or SQL server in VM as we have seen benefits and explanation above. There are certain limitations with SQL Database feature in Azure while we can use all features of SQL Server without any limit in virtual machine.

    ——————————————————————————————————————————–Hire Me

Advertisements
Posted in Azure, Database Administration, Q & A, Sql Server, windows azure | Tagged , , , , , , , , , , , | Leave a comment

How to learn Microsoft Cloud- Azure

Micrsofot Azure

As we know that technology has changed and Cloud Computing has come into picture. This technology provides easy access with lot of functionalities and is good from the point view of cost. Therefore companies have started to move to cloud technology.

There are several companies which provide Cloud technology however few are popular.

The popular and top most Cloud providers are as:

  1. Microsoft – Azure
  2. AWS- Amazon Web Services
  3. Google– Google Cloud
  4. IBM
  5. Salesforce
  6. Oracle
  7. SAP

Here, I am providing a glimpse of Microsoft cloud which is known as Azure. It was initially released on 1st Feb 2010. Azure is a cloud computing service created by Microsoft for building, testing, deploying and managing applications and services through a global network of Microsoft managed data centers.

To start with Azure,we need to login to https://portal.azure.com/ along with user name and password as below.

Login

Once logged in to the Azure portal website, we will get snapshot of the dashboard of Azure. We can get options on the left hand side to use e.g. SQL Database, Virtual Machines etc. We can search the particular service in search bar.

Azure-Dashboard

Dashboard-Services

Conclusion: To be familiar with the cloud computing and enhance our knowledge , Azure is the platform to learn and move ahead as Microsoft provides free subscription for 30 days with huge amount to use for learning purpose.

So what we are waiting for , move fast and learn new technology.

Looking forward your likes & Comments!!!

Posted in Azure, Database Administration, windows azure | Tagged , , , , , , , , | Leave a comment

Solved: Nonclustered Index Corruption in SQL Sever

Data Corruption is bad. Corruption either occur physically or logically in indexes. The Non-clustered Indexes are pointers of data that helps you in finding data more quickly. It contains Non-clustered key value and each key value has a pointer that contains the key value of data rows.Sadly, the ones who work in SQL Server is not aware about the processing behind it. The error of Non clustered Index is shown as:

<Date> <Time> spid # Error: 8646, Severity: 21, State: 1.
<Date> <Time> spid # Unable to find index entry in index ID 3, of table 2102402659, in database ‘'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

Cause of Corruption in Non-Clustered Index

Index corruption occurs due to the NOLOCK hint run with any DML operation.This causes the query to read same value multiple times or read the table value incorrectly.

Sometimes it’s only the Non-clustered indexes that get corrupted, so you don’t have to run any restore operation on backups or run any repair command. Non- Clustered corruption is quite easy to handle, unlike, corruption in SQL data pages.You will able to fix it by running DROP and CREATE index.

How to Find Corrupted Indexes?

The consistency checks on indexes differ on the level of its compatibility of databases.

  • If the compatibility level <=100;
    • DBCC CHECKTABLE performs physical and logical consistency checks on all Non-clustered indexes and on the single table. By default, only physical consistency checks are done on XML Indexes, spatial indexes and indexed views.
    • In WITH EXTENDED_LOGICAL_CHECKS, logical consistency checks are performed on an indexed view, XML indexes, and spatial indexes. Only logical consistency checks are performed, if NOINDEX is specified.

Note: Running WITH EXTENDED_LOGICAL_CHECKS can highly affect the performance, therefore, you must use it only when:

  1. If you have issues related to Suspect index that are not related to physical corruption.
  2. Page level checksum is turned off.
  3. You suspect column- level hardware corruption.
  • If compatibility level >=90;
    • DBCC CHECKTABLE performs both physical and logical consistency check on a single table, indexed view and on all non clustered indexes. Spatial Indexes are not supported.

How to Fix it Manually

  • When nonclustered index is corrupted, the first step is to Run REPAIR_REBUILD command when the user is in SINGLE Mode.

  • Alter index IX_EmailAddress_EmailAddress
    on Person.EmailAddress
    rebuild
    go

    The corruption FIXATION is not guarenteed because Rebuild reads the old index to build new one as a result of this new index contain the missing rows as old ones.

  • If Rebuild is not possible, then you need Drop & Create. Follow below procedure:
  • Right click on Indexes -> Select Script Index as DROP & CREATE to New query Window.

  • Run DBCC CHECKDB command to check consistency of the database.

Easy way to Repair Corruption By an Alternate solution

If you don’t want to perform lengthy process and want to save your time in repairing indexes, you can use professional third party SQL Recovery tool. The manual process is bit time consuming and may or maynot solve your problem. So you can use third party tool for an easy repairing. SQL Recovery software is capable of repairing corrupted indexes with an ease and fix your repaired indexes as a solution.

Conclusion

In this Blog, I have discussed about the solution to recover Non-clustered index in SQL Server Database. The blog covers two solution of repairing non clustered indexes. Choose the repair solution as per your ease.

Posted in T-SQL | Leave a comment

How to Repair Suspect Database in SQL Server with DBCC CHECKDB

Sometimes when connecting to the database, the database becomes inaccessible and you will get the following error message:

error message 945

The error message indicates that the database is in SUSPECT mode. Continue reading

Posted in T-SQL | Leave a comment

DBA Interview Question# Can we create a snapshot for MSDB system database?

DBA Interview Question# Can we create a snapshot for MSDB system database?

Recently I have been approached for one of my technical friend regarding the snapshot for system databases i.e. Master, Model. MSDB & TempDB whether possible or not.

As per Microsoft recommendation, snapshot is prohibited for Master, Model & TempDB so the question is here what about the MSDB snapshot, is it possible to generate?

Can we create a snapshot for MSDB system database?

Here I am sharing the details as an answer with you all after having  done a practical on it.

Answer:

The answer is yes, we can create the snapshot for MSDB system database.

MSDB

If we try to generate the snapshot for other system databases then we will get the error as below:

Model DB

signature_3

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

Trace Flags in SQL Server

Trace Flags in SQL Server

Traces

Trace flags plays an important role in database administration. These are frequently used to diagnose performance issues or to debug stored procedures. For example trace flag 1222 is to detect the deadlock and returns the resources and types of locks pertaining in a deadlock

In SQL Server, there are three types of trace flags, query, session and global. Query trace flags are only active for the specific query. Session traces flags are active only for that particular session or connection. Global trace flags are set at the server level and are visible to every connection on the server.

There are several types of trace flags available. Here I’ll take mostly example of deadlock trace flags which is the most common for the DBA use.

Permissions

Requires membership in the sysadmin fixed server role.

Rules

1.

A global trace flag must be enabled globally, otherwise the trace flag has no effect. It is recommended to enable global trace flag at start up, by using the –T command line option. This ensures the trace flag remains active after a server restart.

The trace flag should be enabled with appropriate scope whether it is global, session or query scope. A trace flag which is enabled at the session level never affects another session, and the effect of the trace flag is lost when the SPID that opened the session logs out.

How to set Trace flags on or off

1.

Using DBCC TRACEON & DBCC TRACEOFF

E.g. DBCC TRACEON (1222, -1)

The effect of enabling a global trace flag with DBCC TRACEON is lost on server restart.

To turn off a global trace flag, use

DBCC TRACEOFF with the -1 argument

DBCC TRACEOFF (1222, -1)

2.

-T (T stands as Trace flag) start up option

The –T startup option enables a trace flag globally. We can’t enable a session-level trace flag by using a startup option. This ensures the trace flag remains active after a server restart. This can be done via SQL Server configuration manager.

Note: Here we can set the trace flag in Startup Parameters as “-T1222”.

How to start\stop traces:

Dbcc traceon (trace no) – for current session

Dbcc traceon (traceno, -1) – to enable globally

Dbcc traceon (no1, no2, -1) – enable multiple traces globally

How to check trace status:

Dbcc tracestatus

Dbcc tracestatus (-1) – display all globally enabled flags

Dbcc tracestatus () – display all trace flags enabled for current session

signature_1

Note: Hi Friends,

Looking forward your thoughts on my blogs at least one liner email along with your name so that I can publish it with your name in coming blog. Kindly send it on my email id or using contact us here on the blog. Appreciate your time in advance.

Email Id: mirza_dba@outlook.com

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

SQL Server High Availability and Database Recovery Model Relationship

Recovery model

There are four types of high availability options available in Microsoft SQL server. All the options are different from each other. For example, Log shipping, Mirroring and Always On works on database level while Replication works on database objects level. The databases involved in these high availability feature must have a fixed type of recovery models else it is not possible to configure these options. Here I am briefing about the recovery models of the database which are involved in high availability.

HA &amp; Recovery Models

How to check the recovery model of a database?

Method 1:  sp_helpdb

Method 2: select name as [Database Name], recovery_model_desc as [Recovery Model] from sys.databases

How to change the recovery model of a database?

Method: T-SQL

Alter database database name set recovery full/bulk logged/simple

E.g.

alter database Mirza set recovery full

signature_1

signature_2

Earn Money

Posted in Always on, Database Administration, Replication, Sql Server, T-SQL | Leave a comment