RSS

Create SQL Server HA and DR using FCI and AOAG

One of the most tricky subject for any SQL Server DBA or SQL server consultant How to build HA ( High Availability ) it is not something Complex, but it need to be list down all of the points and the requirements before going for this project and when we talk about High Availability We should consider two technology FCI (SQL Server Failover Cluster) and AOAG (Always on High Availability Group) both technologies will give you the same feature ( High Availability ) to save your DB from any disaster  can be happened in your DB center , As you know AOAG is new technology released with SQL Server 2012 and Microsoft added more features on it in SQL Server 2016, 2017 and 2019 as well . So really I recommend it for HA solution. Also, it will give you the availability to split your transaction between multiple instance one as RW (Read-write) it is called Primary, and the other instance will work as R (Read only), and it is called secondary server.

Now I leave you with the training video to know How you can build SQL Server HA and DR using FCI and AOAG


Training video published by Mr. Musab Umair  Microsoft SQL Server DB Consultant you can follow him on his LinkedIn account (https://www.linkedin.com/in/musabumair/)

Keep following our community to know all of the new technologies and features in SQL Server

 
Leave a comment

Posted by on November 6, 2018 in HA Clustering

 

Tags: , , ,

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: , , , , , , , , , , , , ,

How to Fix Microsoft SQL Server Error 926 Instantly

Microsoft SQL server is the most popular database management system. It provides flexibility to the database administrator for managing the database. But, sometimes it creates numerous errors in different stages while working with the SQL server database. SQL database suspect error 926 is one of them, which creates hurdles in SQL transaction. Sometimes, it also vanish the smooth working of SQL server as it makes database inaccessible for the user.

Hence, this article will tell you the best solution on how to fix Microsoft SQL Server error 926. Before going towards the solution, let us discuss the symptoms and reasons behind SQL Server error 926.

SQL Server Error 926 – Find Out Reasons

  • The SQL database might be marked as suspected
  • When malware defect occurs in the hardware of the machine<
  • If corruption found in the header part of the system file
  • Sudden shut down of the server application also leads the error

How to Fix Microsoft SQL Server Error 926

You can resolve SQL database suspect error 926 with help of any of the solution discussed below:

Solution # 1: Resolve SQL Error 926 Using Server Management Studio

  1. Launch the SQL Server Management Studio on your computer system.
  2. After that, go to the Object Explorer >> Server Instance >> Stop option.
  3. Now, minimize the management studio & open the Control Panel on the system.
  4. After that, change the view screen to Large icon & click on the Administrative Tools.
  5. In this step, select the Services option to proceed further.
  6. Here, search for the SQL Server (MSSQLSERVER), right-click on it & choose Stop option.
  7. Close the control panel window & launch My Computer or Computer on Windows. In the computer, go to the location where SQL data has been saved.
  8. Select MSDBDATA and MSDBlog files and move them to another location.
  9. Now, go to the location where you had moved the file in the previous step and copy them.
  10. In this step, restore the management studio window & go to the Object Explorer section. Now, right-click on the Server Instance & choose Start option.
  11. Finally, click on the Refresh button of the Management studio in order to update the changes.

Limitations of the Manual Approach

The above discussed manual method involves a lot of brainstorming effort and it is a time consuming process.To fix SQL Server error 926, there is a long and complex command to be executed. There might be the possibility that you might make mistake during command execution. It also takes a lot of time to remove the error Microsoft SQL error 926 and to recover the SQL server database. Moreover, the manual procedure is completely unsuitable for the non-technical or less experienced users because this process contains the totally technical term. Thus, to replace the manual approach, an effortless approach is described which overcomes all such consequences of the manuals.

Solution # 2: Fix Microsoft SQL Server Error 926

To overcome all such drawbacks, users are suggested to take help from a third-party solution named as SQL MDF Repair Tool. The tool repairs corrupt MDF / NDF SQL server database file of SQL Server version 2017 & all below version. The software gives you two different options for exporting. One can export recovered database in SQL server database and as .sql script. Moreover, by using this Professional software, Microsoft SQL Server error 926 can be easily fixed without any hassle.

Final Words

In this article, we have discussed about SQL server error 926 and different methods to fix this error. The manual approach is available to fix SQL error 926 Suspect but it has many consequences. Hence, in order to perform a simple, easy and effortless recovery procedure, users can use SQL recovery tool. It is the easiest approach to remove SQL Database suspect error 926

 
Leave a comment

Posted by on September 19, 2018 in DB corruption

 

SQL Server 2017 on Linux

Hello, followers, I need to share with you some tips in SQL Server 2017 on Linux

  • SQL Server 2017 supported Red Hat, SUSE and Ubuntu
  • You need 3.25 GB of memory to run SQL Server Enterprise on the Linux operating system
  • Not like Windows Server you can install multiple instances on the operation system, Linux supported only one SQL Server instance
  • Hostname of the SQL server should be less than 15 Characters
  • File System has a different configuration in Ubuntu and SUSE should be EXT4 but in Red Hat should be EXT4 and XFS Files.
  • Remote databases can’t be stored on NFS system
  • SQL Server Native GUI still not supported in Linux system till this moment
  • Cannot connect to the SQL instance on Linux from the management studio installed on Windows Server
  • The maintenance plan, Management Data Warehouse, distributed transactions, and the Data Collector are not supported in SQL Server /Linux
  • If you have SQL version less than 2017 and you need to migrate it to Linux , Recommended to upgrade it on Windows server to SQL 2017 than you can take backup from DB and restore it on Linux or Attach / De attach and in the restore DB from windows to Linux you need to use “WITH MOVE” T-SQL statement
  • SQL Server 2017 unsupported feature on Linux (transactional replication, merge replication, stretch DB, polybase, distributed queries with third-party connections, system extended stored procedures, file tables, CLR assemblies with external access or unsafe permissions set, and buffer pool extensions. Database mirroring, agent alerts, managed backups, the SQL Server browser and the machine learning services that include support for the R and Python programming languages, are also not supported at the time of this recording)

I know this a lot features in SQL Server 2017 not supported in Linux but personally, I am so proud about this results it is the first time for Microsoft to see SQL Server on another operating system (Red Hat, SUSE or Ubuntu)

References :

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2017?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-security-overview?view=sql-server-2017

https://www.theregister.co.uk/2017/09/27/sql_server_2017_whats_new_and_on_linux_whats_missing/

 

 
Leave a comment

Posted by on September 15, 2018 in SQL Server 2017 on Linux

 

Tags: , , , ,

SQL Server Torn Page Repair: Without Worrying About Data Loss or Corruption

It can often be bugs and errors in fetching data from the server. This can leave system administrators baffled as all work comes to a standstill. Incomplete or incorrect transaction cause confusion among employees working on the different sections of a database.

One such frequent issue is the SQL Server torn page error. It can be quite a hassle for a non-technical user facing such a situation. There are ways to fix it easily without worrying about any data loss. This post covers all there is to know about this topic. Continue reading to find out more.

What is the Torn Page in SQL Server?

It is the inability of the server to fetch a particular data during a transaction. It is caused when an Input/Output header tries to access a page that was written incorrectly to the disk. It reports a message saying ‘I/O error (torn page) detected during read’. The reason for this can be primarily contributed to power failure causing partial writes. Other factors include damaged disk or other hardware to which data is being written to. If a torn page is detected by SQL Server, it will sever all connections as the requested data is inaccessible. During the restore process, detecting a torn page sends the database into SUSPECT mode.

sql server torn page repair

Read the rest of this entry »

 

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: ,

Improve SQL Server Performance with Compression

Improve SQL Server Performance with Compression

SQL Server data compression is now available in all editions of SQL Server, starting with 2016 SP1.

In this Article, you will not only know how data compression will save space, you’ll also find out how compression can sometimes improve performance as well.

Space Savings vs. Performance

When I first heard about compression back in 2008, my first thought is that it would have a big performance penalty. Compression would save disk space, but it would probably decrease performance as the data was compressed and decompressed. It turns out that compression can improve performance instead. Because compressed data fits in a smaller number of data pages, there are decreased I/O requirements. Since I/O is generally the bottleneck in SQL Server, this can improve performance. Compressed data also has a decreased memory requirement. When querying compressed data, a smaller number of pages will be copied to the buffer pool. The one area that is impacted is CPU. You do need to have some CPU headroom because compression will require some additional CPU resources.

a1

The good thing is that if the workload is reasonably tuned, many SQL Server instances have more CPU resources than they need. One note of caution here. Don’t look to compression as the solution to solving major performance issues. You need to look at physical resources, configuration, indexing, and query tuning. The point I’m trying to make is that decreased I/O and better memory utilization will benefit the workload in many cases.

Read the rest of this entry »

 

Tags: , , , ,

 
%d bloggers like this: