How to connect to Azure VM SQL Server from SSMS?

In one of my previous posts, I’ve shown how to connect to Azure SQL DB(DaaS) from your local SSMS. In this post, let’s see how you can connect to SQL Server Instance which is running inside your Azure Virtual machine.

I’ve created a Azure VM(sreetestvm.cloudapp.net) with SQL Server 2014. I RDP’d onto the server and see below for how it looks.

1

But at this point I wasn’t able to connect to this SQL Server Instance remotely from my local SSMS on my laptop. So, what should we do to be able to connect to this instance?

1. Create an End Point from your VM dashboard to allow TCP port 1433.

2

2. Enable Mixed mode authentication on your SQL Server Instance and make sure TCP/IP protocol is enabled from SQL Server Config manager.

3. Create a firewall rule(Inbound) to allow port 1433 on your VM.

3

I’ve created a SQL login named “sreeremote” on this instance and voilaaa…now am able to connect to this Instance remotely from my SSMS :)

4

Hope this helps…

Azure SQL DB using SSMS – Things you should know.

Hey folks…In this post let’s see few things which you should be aware of, to avoid any surprises when connecting to your Microsoft Azure SQL DB from your SSMS.

First thing you might notice is there are no msdb,model and tempdb databases under system databases category, No Agent and pretty much nothing what you would see on your On-Premise SQL Server.

. 1

Now, let’s try opening a new query and see how database context works with Azure SQL DB. As you can see I got connected to master database which is my default database.

2

Nothing strange right? Let’s see what happens when I try to change my database context.

3

Ahaaaa….so, What if I change my database context from GUI?

4

Am able to change the database context for the first time. Well, but you can’t switch it back from GUI. It will be greyed out(See below).

4

So…Basically you have to establish a new connection to desired database as needed. In other words, you are directly connecting to your Database(literally). After all…this is Azure SQL DB, not Azure SQL Server :D

Getting started with Microsoft Azure – Creating Azure SQL Database(DaaS)

In this blog post, Let’s see how to create a Microsoft Azure SQL database and how to connect to it from your SSMS client tool remotely.

Pre-Req: you need to have an Azure subscription. You can sign up for free (I guess, you will get 30 days for trail).

Once you login to your Azure management portal, it looks similar to this…

1

As you can see I’ve one Virtual machine and a storage account and “Zero” SQL Databases at this point. So, do you need a virtual machine or a storage account to create Azure SQL DB? The answer is “No”. I created them for exploring Azure IaaS(Infrastructure as a Service). Anyways…let’s start creating Azure SQL DB.

Step1: select SQL Databases and select “create a SQL Database”

2

Step 2: Give your database a name and choose your tier and DTUs (Data throughput Unit). I will explain what they are in another short blog post. Now…focus on the server in the below screenshot. Remember, I already have a VM but it’s not listed here and I’ve selected “create new SQL database server”. If you are wondering why, here am trying to create a Azure SQL Database as DaaS(Database as a Service), In which I’ve no control over the underlying server and hardware. All I get is to provide DTUs and a location to pick, I can’t use Virtual machines which I’ve created under IaaS for this service.

3

Step 3: Provide a SQL Server authenticated admin account to connect to this database(Think this as “sa” account on your traditional SQL Server Instance) and pick the server location where you want the server to be created.

4

Click on the “Check” button and you should have your server and SQL database up and running in matter of few seconds.

You can see I’ve got a new SQL Server database and when I go to servers tab, see the new server as well which got created.

5

6

So, how would you connect to this SQL Server from your SSMS?

You should go to servers tab and click on your server and click on dashboard. On the bottom right you will see an URL. That’s your server name which you should be using to connect from SSMS without https://.

7

Let’s give it a shot and see how it goes from my SSMS.

8

Oops! The fix? Simple, I have to go to azure management portal and select the server and go to configure and create a rule to allow my IP address where I’m trying to connect from using SSMS as shown below and click save.

9

Once I did that, I was able to successfully connect to my Azure SQL DB :)

10

I hope this will help setting up your first Azure SQL DB as DaaS. In an upcoming post, I will show few “GOTCHAS and limitations” you should be aware of while connecting to your Azure SQL DB from your SSMS.

Cannot obtain the required Interface from OLE DB Provider “OraOLEDB.Oracle”

Today in the morning I was contacted by my fellow DBA to look into an issue with a linked Server he setup from SQL Server to Oracle. He did setup a linked server from SQL Server 2005 to an Oracle database and tested successfully(From SSMS). Well, everything appeared to be okay, but the app teams are unable to use this Linked server.

a

The DBA tried expanding catalogs->default and Oops…he is receiving this weird message.

b

This looks like missing permissions issue at Oracle end, but wait……..he was able to test the linked server successfully! So what did he miss?

The answer is “Allow Inprocess” for OraOLEDB.Oracle provider. Once I asked him to check that box, Voilaaaa….it works :)

c

For more information on the properties of these providers, please see http://technet.microsoft.com/en-US/library/ms188095%28v=sql.90%29.aspx

Happy Friday all!

AlwaysON Secondary database going to “Not Synchronizing/ Suspect” State!

In this blog post I will share an issue we had with a database which is configured with AlwaysON. Before proceeding any further, the environment which we’ve got is:

Each node has Windows Server 2008R2(With all the service packs and hot fixes recommended for AlwaysON)
Running on top of VMware VShpere 5.1
SQL Server 2012(SP1) Enterprise Edition
RAM: 10 GB (8 GB assigned to SQL Server).
2 VCPU’s.
Availability Mode- Synchronous Commit

Issue: Daily around 5 AM, the secondary database is going to “Not Synchronizing/Suspect” state and until we fix this the T-Log on primary grows and all that normal jazz once the AlwaysON databases get out of Sync…(See below)

1
So, what’s happening?
The App team is performing data load daily around 4.30 AM. Okay…So what’s bad about that? They are loading ~30 Million Records daily, in a single transaction. Oops!!!…
From SQL Server error logs, we see the below message:
Message

AlwaysOn Availability Groups data movement for database ‘Test_DB’ has been suspended for the following reason: “system” (Source ID 2; Source string: ‘SUSPEND_FROM_REDO‘). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.
This message is always accommodated with another message(Shown below):
Message
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

Ummm…This doesn’t looks good. If you are thinking, what Locks have to do with AlwaysON secondaries, let me tell you this. With Database Mirroring and AlwaysON Rollbacks/Redo thread will also take a lock on the secondary side to avoid any other transaction to interrupt REDO process, thus guaranteeing consistency. If for some reason SQL Server is not able to acquire locks for redo thread it won’t synchronize the database starting that point. (It’s by design).

In our case what’s happening was SQL Server was running out of memory and was not able to acquire any further locks(Remember, each lock structure in SQL Server will need certain amount of memory).Basically, it says “Since I wasn’t able to acquire a lock during the REDO, I don’t know what else happened at that time and I can’t guarantee the database to be consistent. So…am not going to synchronize from this point and I will suspend the data movement and also take the database to Suspect state”).

From AlwaysON standpoint, Suspending Synchronization when the REDO thread encounters any error is by design and is done on purpose by SQL Server.

To avoid this, all they(App team) have to do is optimize their load process to better manage lock acquisition.(We are not being granted any more memory on these boxes unfortunately).

Bottom Line: Avoid huge transactions on tiny SQL Servers. Try to split the transactions into multiple chunks especially when dealing with millions/billions of rows.  That helps in general many ways, not just in this particular scenario.

Have a safe and happy long weekend guys!

 

This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)

This morning one of our developers contacted me regarding this error message he got when he tried to design a table from his SSMS.

1

Also, he reported that his SSMS goes to “Not Responding” state when he tries to edit rows using his SSMS. So…What’s happening?

He was trying to edit a table within a database which is running on SQL Server 2014 version using his SSMS 2012.  Now, all he needs is upgrading his client tools to 2014. Remember, you can manage/manipulate older versions of your SQL Servers using your 2014 SSMS, However be prepared to face these annoying issues if you are trying the other way.

Happy weekend folks!

 

Physical Server or a Virtual Machine?

Lot of times, when stepping up for supporting a new SQL Server Instance, we as a DBA need to know whether it’s running on a Physical Machine or a VM. In this post, I will provide you guys with a simple T-SQL Script which you can run from your client machine(SSMS) connecting to the respective SQL Server, which lets you know if it’s a Physical machine or a VM.

Note: This Script works only on 2008R2 SP1 and above.

SELECT SERVERPROPERTY('computernamephysicalnetbios') AS ServerName
,dosi.virtual_machine_type_desc
,Server_type = CASE 
WHEN dosi.virtual_machine_type = 1
THEN 'Virtual' 
ELSE 'Physical'
END
FROM sys.dm_os_sys_info dosi

/* If you have a CMS configured, run the below Script from your CMS against multiple servers*/
SELECT dosi.virtual_machine_type_desc
,Server_type = CASE 
WHEN dosi.virtual_machine_type = 1
THEN 'Virtual' 
ELSE 'Physical'
END
FROM sys.dm_os_sys_info dosi

Additional Comments:
“virtual_machine_type_desc” Output value description
NONE – SQL Server is not running inside a virtual machine.
HYPERVISOR – SQL Server is running inside a hypervisor, which implies a hardware-assisted virtualization. If the instance is running on the host OS, the description will still return HYPERVISOR.