RSS

Category Archives: General topics

General topics in SQL Server

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

 

 

 

Tags: , , , , , , , , , , , , ,

Query Store for Solving Query Performance Regressions

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

Read the rest of this entry »

 

Tags: ,

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

Read the rest of this entry »

 
1 Comment

Posted by on May 14, 2017 in 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

 
Leave a comment

Posted by on March 1, 2017 in 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

 
Leave a comment

Posted by on March 1, 2017 in General topics

 

SQL Server performance – measure Disk Response Time

Introduction

As DBAs, we all get to the point where we are asked to setup a new server for a particular environment. Setting up a new server is not a big thing but giving the answer to the question that “how well it will work” might be tricky.

There are tons of items which we can set up to measure how well the newly installed server will receive a response, but here I will discuss one of the most valuable resources of the server “Disk.” Most often the disk is not measured correctly, or I have seen environments where the disk response time has never been measured. I will discuss here a tool from Microsoft which is very handy and can solve your problem very quickly. The diskspd.exe!

It’s the superseding version of SQLIO which was previously used to measure IO response time for the disk. The source code of diskspd.exe is hosted on GitHub. You can download this free utility from Microsoft’s website using this link.

After you download the utility, you will get a zip file. Just unzip the file, and it will give you the folders and files as shown in the below screenshot. You will-will need the eye of diskspd inside the folder “amd64fre” if you have a SQL Server 64-bit version (most of us will be having this).

To complete this article please check it here

 

word-image-29

 
Leave a comment

Posted by on February 16, 2017 in General topics, SQL Server 2014

 

Tags: , , , , ,

Overview On SQL Server Database Integrity Check

Problem

Sometimes users may have numerous of databases in their SQL Server. However, while working on database they may face a database integrity issue, as there can be integrity on several parts of a database i.e. Table, database, etc.

Solution

In general, the integrity of SQL Server database task includes the complete checking allocation as well as structure of integrity of the entire object in the stated database. It contains various checking database indexes as discussed below.

Determine Requirements

Firstly, check all the databases at high level periodically for the SQL Server database consistency errors. If there, is a large database then, check it table by table. Moreover, running all these on a set of schedule, it is important to run, as there are the chances of system failure, disk drive failures, etc.

If the database is small then, it is easy to run all the checks across the whole database. In SQL Server 2000 and 2005, all the maintenance plans are the only way for performing DBCC CHECKDB that makes easy in checking the complete database. As it is already discussed above that, it is a part of normal database maintenance process. If the user is not running the checks for SQL Server database integrity or if they are not sure then, take a time for implementation of all these procedures.

Useful Commands

There are mainly four different commands, which can be used for checking different levels of consistency that are there in the database as mentioned:

  • DBCC CHECKALLOC– Check the consistency of structures of disk space allocation, which are there for specified database.
  • DBCC CHECKCATALOG– Check for consistency of catalog, which is therein specified database.
  • DBCC CHECKDB– Check for consistency of catalog that is in specified database.
  • DBCC CHECKTABLE– Check for the integrity of all pages as well as structures, which makes indexed and table view.

DBCC CHECKDB command is widely used by almost all of the users as it checks the complete database. Whereas all the other options are useful for the quick spot-checking. All these options can be used for checking the consistency of particular portion of data and repairing any sort of corruption, which is found.

Running

Ad Hoc at the most basic level of all the commands can be executed interactively against a database. It is always suggested to run these commands during the off hours as these commands issue some problem while executing.

Maintenance Plans as discussed above that SQL Server 2000 and 2005 both can set the maintenance plans for running DBCC CHECKDB command. This is the only way to have the base options of maintenance plans.

Custom Jobs can be created on its own SQL Server Agent jobs for running the desired commands against the database.

Scheduling

The most beneficial time to run these commands is during the low usage times. SQL Server Agent is the best way to schedule jobs during any time. If the hardware is stable then, there is not any issue for running these commands. If user is facing regular hardware problems then, it is the best way for running all these checks frequently.

Output

There are many installations where the plans of maintenance are setup and SQL Server database integrity check is run. The problem is that no one checks the output that results in the corruption issues. For this, users need to check for the output from the commands that make sure that there are no issues. This can be done by reporting option with maintenance plans and by checking SQL Server logs error. Every time, one DBCC command is run and an entry level is made in SQL Server error log.

Handling Issues

If there is corruption in the database then, users want to have the correct action for the elimination for these issues. This is done by utilizing the option with DBCC CHECKLOC, DBCC CHECKDB, and DBCC CHECKTABLE.

Conclusion

After understanding, the users issue that they face while checking for the SQL Server data integrity. In the above discussion, we have discussed the way for SQL Server database integrity check that makes easy for users in resolving this issue.

 
Leave a comment

Posted by on January 9, 2017 in General topics

 

Tags:

 
%d bloggers like this: