Administration – DB Cloud TECH

October 15, 2018 Mirza Husain Administration, General topics, SQl server Administration

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:

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.

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.

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.

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

Looking forward your likes & comments!

Mirza Husain

November 24, 2017September 15, 2018 Ayman Elnory Administration, Configuration, Performance MSSQL, problems, SQL Server 2014, SQL Server 2016, SQl server Administration

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.

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.

Continue reading “Improve SQL Server Performance with Compression” →

November 20, 2015 Mohit Administration

sys.foreign_keys does not have matching row in sys.indexes

Running DBCC CHECKDB you are getting following error message:

Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=194099732,key_index_id=7) of row (object_id=2040565179) in sys.foreign_keys does not have a matching row (object_id=194099732,index_id=7) in sys.indexes

This error means, that Unique key constraint (index_id 7) in the primary table (object_id 194099732) is missing, which was referenced by child table’s FK constraint (FK object_id 2040565179). This should not happen, SQL Server will not allow you to drop a constraint that is referenced by FK. If attempted should get following error message:

Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index ‘dbo.a.NonClusteredIndex-20151119-085219’. It is being used for FOREIGN KEY constraint enforcement.

So if we are suppose to get errors? Why do we have corruption; simple answer, someone be making updates to system tables directly, which is not allowed or supported!

Actually we are not able to update system tables in SQL Server 2005+ (ref), however in SQL 2000 days, we had setting called allow updates in sp_configure options. Also supported by the KB2787112.

So question is how do you fix it?

First, identify the child table name from sys.foreign_keys:

SELECT object_name(parent_object_id) AS TableName
FROM sys.foreign_keys
WHERE name = ‘FK_b_a’

Second, script our constraint definition:

Find the table, we got in SQL Statement above.
Go to Keys.
Right click on FK constraint name.
Script Key As.
Create To.
New Query Window.

Third, drop the FK constraint:

ALTER TABLE [schema].[tablename] DROP CONSTRAINT [fk_constraint_name]

Fourth, Re-create the constraint, with script generated in Step 2.

If it was issue of someone playing around in system tables, this should resolve it. However, if you get error similar to below:

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table ‘dbo.a’ that match the referencing column list in the foreign key ‘FK_b_a’.

This means, that the key is missing in parent table and appropriate index needs be created before FK constraint can be created. Since SQL doesn’t allow the index to be dropped there most likely are other corruption issues that have gone unnoticed. If that is an issue, you will have to rely on your backups for recovery.

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

November 16, 2015 Mustafa EL-Masry Administration

Change DB Owner on all Databases one time

this one of wonderful scripts and very important

—Change DB Owner on All Databases
EXEC sp_MSforeachdb ‘IF ”?” NOT IN (”Master”,”tempDB”,”model”,”msdb”)
BEGIN
EXEC [?]..sp_changedbowner ”User Name”
END
‘

To know More about new feature in SQL Server 2016 you can check it from HERE

Follow Us :

LinkedIn , Slideshare ,Youtube Channel.MSDN POSTS ,Facebook , WHO WE ARE

June 14, 2015 Mustafa EL-Masry Administration, Performance MSSQL

Check and Enable Automatic Statistic Update on Database Level

AS SQL Server Best Practices: Auto-Create and Auto-Update Statistics Should Be On – Most of the Time in general they are a very good thing for performance. You could try to figure out which columns need statistics, but it’s often better to let SQL Server do that for you.You can turn on the AUTO_CREATE_STATISTICS database option and SQL Server will automatically determine when it needs the Statistics and create them for you.

Check And Enable Automatic Statistic Update on all databases have this option is disable

Select ‘ALTER DATABASE [‘+Name+’] SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE [‘+Name+’] SET AUTO_UPDATE_STATISTICS ON;
GO
‘ from Sys.Databases where is_auto_update_stats_on = 0 or is_auto_create_stats_on = 0

Take the Result and Execute it in another session