Mirza Husain Administration, General topics, SQl server Administration

How to create a SQL Database in Azure


How to create a SQL Database in Azure

(A Step by step procedure)

I am writing this blog and explaining how to create a database in Azure. As we know that Azure is a cloud computing service provided by Microsoft and is becoming popular in the world. As a DBA we need to know how to work in a cloud computing environment and therefore for the ease I providing a step by step solution for creating a database in Azure.

sql-database-windows-azure

Explanation:

First of all we need to login to Azure portal and need to check for SQL Database options as it will be on the left hand side as given in picture below.

SQL Database Options

As soon as we click on SQL database option, a new windows will open up as given in picture below:

pic 1

Here we can see in this picture that there is no database available. Now we have to create a new database and for that we need to click on Add button on the top as shown in picture.

As we click on add button so it will display a new windows which requires the information to fill in to create a database.

pic 2

pic 3

Here we need to provide the information as database name, subscription, resource group (if existing then use that and no need to create a new resource group for each database), another option here elastic pool which is already discussed in my previous blog , go through if not read

https://mirzahusain.wordpress.com/2018/08/06/sql-database-as-a-service-in-azure/

As this is a test database which I have created so not using elastic pool and simple creating a standalone single database in Azure. Rest settings pricing and collation we need to set here as per our requirements or choose default.

One more thing which is important that is location we need to choose as I have chosen East US , you may choose as per your company requirement and policies, for the test purpose you may choose any location and just try and hit.

pic 4

Check and fill all the option carefully as shown in the above picture and then hit the create button.

The deployment will start to create the database. I have created here the database named as “MirzaDB”. You would be getting the alert in the alert section as soon as deployment succeeded.

pic 5

Now database has been created and we can explore it by checking its size and other details as below in different pictures.

pic 6

Looking forward your likes & comments!

Mirza Husain

 

 

Ayman Elnory General topics, Performance MSSQL, Query Store, SQL Server 2012, SQL Server 2014, SQL Server 2016

Query Store for Solving Query Performance Regressions


Query performance is a very important area of SQL server. We always have badly performance queries around.

Query store is the newest tool for tracking and resolving performance problems in SQL server.

In this article, we are going to have a look at some practical uses of SQL server Query store.

What is Query Store?

The query store has been described by Microsoft as a ‘flight data recorder’ for SQL server queries. It tracks the queries run against the database, recording the details of the queries and their plans and their runtime characteristics. Query store is per database feature and runs automatically. Once turned on, nothing further needs to be done to get it to track data. It runs in the background collecting data and storing it for later analysis.

Query store is available in SQL Server 2016 and later, and Azure SQLDB v12 and later. It is available in all editions of SQL server, even in Express edition.

How is Query store different from other tracking options?

We have had query performance tracking for some time though in the form of dynamic management views. Mostly, sys.dm_exec_query_stats and sys.dm_exec_query_plan and tracing tools like SQL server profiler and extended events.

So, what makes Query Store different? Let me start answering that by describing a scenario that I encountered a couple of years ago.

A particular critical system was suddenly performing badly. It had been fine the previous week and there have been no extended events sessions or profiler traces running historically. The admin had restarted the server when the performance problem started, just to make sure it was not something related to a pending reboot.

As such, there was no historical performance data at all and solving the problem of what happened, why the query performance is different this week was extremely difficult.

1

Continue reading “Query Store for Solving Query Performance Regressions” →

Mustafa EL-Masry General topics

Methods of protection and recommendations from threats and dangers of a Ransomware virus


I would like to inform you that there are warnings and risks on our servers, Computers based on the statements published today in more than one website and published through (Microsoft, NCSC,..ETC) the emergence of Ransomware virus.

This virus attacked many of PC,s, and servers over all the world that’s why We need necessarily to do the below things ASAP

 

WhatsApp Image 2017-05-13 at 3.18.07 PM

Continue reading “Methods of protection and recommendations from threats and dangers of a Ransomware virus” →

Mustafa EL-Masry General topics

Hybrid Cloud and Hekaton Features in SQL Server 2014


Introduction

Microsoft SQL Server 2014 is considered to be the first version that supports Hybrid Cloud by adding a lot of exciting new features.

In this article, I will cover some of the top new features in these main points including Hekaton and Hybrid Cloud enhancements:

Hekaton

Hekaton is the code name of the new feature of In-Memory OLTP. It is a new database engine, fully integrated with SQL server and designed to enhance memory resident data and OLTP workloads. In simple words, with Hekaton we can store the entire table in memory.

Let’s list some of the benefits of this new feature:

  • Memory-Optimized-Tables can be accessed using T-SQL like Disk-Based-Tables.
  • Both of Memory-Optimized-Tables and Disk-Based-Tables can reference in the same query, and also we can update both types of tables by one transaction.
  • Stored procedures that only reference Memory-Optimized-Tables can natively compile into machine code which results in improving performance.
  • This new engine designed for a high level of session concurrency for OLTP transactions.

There are still some limitations for Memory-Optimized-Tables in SQL server 2014 which are:

  • ALTER TABLE statement, SP_RENAME stored procedure, ALTER BUCKET_COUNT statement, and add\remove index outside statement of CREATE TABLE, all of these not supported by In-Memory table
  • Some constraints not supported like (CHECK, FOREIGN KEY, UNIQUE)
  • RANGE INDEXES and TRIGGERS not supported by In-Memory table
  • REPLICATION, MIRRORING, and LINKED SERVERS are incompatible with Memory-Optimized-Tables.

To know more information, you can check SQL Server Support for In-Memory OLTP.

Memory-Optimized-Tables are appropriate for the following scenarios:

  • A table has a high insertion rate of data from multiple concurrent sources
  • A table cannot meet scale-up requirements for high performance of reading operations especially with periodic batch inserts and updates
  • Intensive logic processing inside a stored procedure
  • A database solution cannot achieve low latency business transaction

Let’s now go through the steps to create a Memory-Optimized-Table

 

For more information check the source article from HERE

Mustafa EL-Masry General topics

How to analyze Storage Subsystem Performance in SQL Server


introduction

To improve performance, it is common for DBAs to search in each aspect except analyzing storage subsystem performance even though in many times, issues are, in fact, caused by poor storage subsystem performance. Therefore, I want to give you some tools and recommendation that you can use it to prevent your storage subsystem from being a performance issue for you.

In this article, I will cover how to measure and analyze your storage subsystem performance and how to test your storage subsystem including

  1. Main metrics for storage performance
  2. Operating System Tools to measure storage performance
  3. SQL Server Tools to measure storage performance
  4. Using SQL Server to test storage performance

Main metrics for storage performance:

In this section I will introduce the three main metrics for the most storage performance issues as follows:

  1. Latency
    Each IO request will take some time to complete this latency is measured in milliseconds (ms) and should be as low as possible
  2. IOPS
    IOPS means IO operations per second, which means the amount of reading or write operations that could be done in one second. A certain amount of IO operations will also give a certain throughput of Megabytes each second, so these two are related
  3. Throughputs
    The most common value from a disk manufacturer is how much throughput a certain disk can deliver. This number usually expressed in Megabytes / Second (MB/s), and it is simple to believe that this would be the most important factor

For More information please check the source article from HERE