When your database is big, sometimes just its backup will be around 20GB in size already. Hence, keeping them on disk is always not a solution even though Microsoft Azure provides a data disk with 1TB.
Hence, Chun Siong from Microsoft Singapore suggested my company to try out this service to store our database backups. It turns out that it can be easily done in just 3 steps.
Step 1: Create Azure Storage Account and Retrieve Access Keys
To create a new Azure Storage Account, I simply login to the Azure Management Portal and then choose the Quick Create option of the Storage under Data Services section. I am able to specify the affinity group and replication rule for the Storage Account.
Creating a Storage Account.
After the Storage is created, I can retrieve access keys which will be used later in SQL Server to access the Storage Account.
Retrieve the access keys to the Storage Account.
Finally, I just need to create a Container in the Storage Account. All the database backup files will be put inside the Container later.
Created a container in the Storage Account.
Step 2: Create SQL Server Credentials
I then execute the following T-SQL statement to create credentials so that SQL Server later can connect to the Storage Account.
The Storage Account access key here can be either Primary or Secondary access key retrieved in Step 1 above.
Step 3: Backup Database
I create a scheduled job in SQL Server Agent to do backup of my database daily. The URL is the URL of the container created in Step 1.
BACKUP DATABASE mydatabase
TO URL = 'https://chunlindbbackup.blob.core.windows.net/dbbackup/mydatabase_' + REPLACE(CONVERT(VARCHAR ,GETDATE(),126) ,':','_')+ '.bak'
WITH CREDENTIAL = 'mycredential', INIT, NAME = 'Backup of Database mydatabase'
So yup, now the database backups will be stored on the Storage Account directly.
Restore Database Backup from Azure Storage
To restore a database backup from Storage Account, if the backup file is small, I can just simply execute the following T-SQL statements. The URL of the database backup can be found in the Container in Azure Management Portal.
RESTORE DATABASE mydatabase_test
FROM URL = 'https://chunlindbbackup.blob.core.windows.net/dbbackup/mydatabase_2014-10-14T13_16_01.243.bak'
WITH RECOVERY,
MOVE 'mydatabase_db_Data' TO 'F:\db\mydatabase_test_Data.mdf',
MOVE 'mydatabase_db_Log' TO 'F:\db\mydatabase_test_Log.ldf',
CREDENTIAL = 'mycredential'
GO
Unfortunately, the backup that I have is too big. So, I can only download it from Azure Management Portal to the database server first before restoring the database. The download is quite fast.
Download backup file from Storage Account
In case, you wonder why I do not use tool like Azure Storage Explorer, no, it did not work. It would crash also if the backup file was too big.
Not many people that I know like to try things that they are not familiar with because unfamiliar is scary. However, working in startup, like my current company, basically forces one to always learn more and learn faster. Hence, after getting approval from the top management, my senior and I migrated our web applications to Microsoft Azure.
Just when we thought we did everything beautifully, our instances on Azure went down for 72 minutes on 4 August, one month after the migration. The reason given by Microsoft team is that there was an issue in one of the clusters within the DC. 3 weeks later, our database instance on Azure went down again for 22 minutes because of a scheduled system update.
Fortunately, Microsoft Singapore is willing to guide us to make high availability in our web applications possible. I am very happy to have Chun Siong, Technical Evangelist from Microsoft Singapore, to help us out.
Last month, Chun Siong successfully to have the database mirroring setup for our database instances on Azure. Since he did all of the work himself, in order to learn and to master the database mirroring, I had to do everything myself from the beginning again.
In this post, I will share the mistakes I made when I tried doing database mirroring myself so that I won’t repeat the same mistakes again.
Beginning of the Journey
There is an easy-to-follow tutorial available on MSDN about how to implement database mirroring in Azure. I used it as a reference to setup one principle database server, one mirror database server, and one witness server within the same availability set.
Elements in my simple database mirroring setup.
Mistake #1: Firewall Blocking Remote Access of SQL Server
If I had read the tutorial carefully, I wouldn’t have to make this mistake because it’s mentioned in the beginning of the tutorial.
I found out this mistake only when I tried to connect to the mirror server from the principal database server. It kept throwing me the Error 1418 saying that the mirror server was not reachable. After reading a checklist of the error, I found out that it’s because I never create an inbound rule on Windows Firewall to allow the access of the SQL server.
Thanks Chun Siong for pointing it out also. =)
By the way, on the article about Error 1418 (http://msdn.microsoft.com/en-us/library/aa337361.aspx), there is a checklist to check if everything is done correctly. I copied and pasted it below for quick reference.
Make sure that the mirror database is ready for mirroring.
Make sure that the name and port of the mirror server instance are correct.
Make sure that the destination mirror server instance is not behind a firewall.
Make sure that the principal server instance is not behind a firewall.
Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. If either endpoint is not started, execute an ALTER ENDPOINT statement to start it.
Make sure that the principal server instance is listening on the port assigned to its database mirroring endpoint and that and the mirror server instance is listening on its port. If a partner is not listening on its assigned port, modify the database mirroring endpoint to listen on a different port.
There is a need to allow the access of SQL server in three instances.
Mistake #2: Typo when Creating Certificates
In the tutorial, the recommended way to deploy database mirroring is to use certificates. After certificates of three servers are created, we need to grant login permission on each server to another two servers. That is when we will use the certificates to create a common login account id called DBMirroringLogin.
I had one typo in the password in one of the certificates. only realized it at the very end when I tried to connect to my witness server. So, yup. Be careful during the database mirroring configuration steps. One small mistake can waste us time to find out why.
Grant login permissions to other two servers.
Mistake #3: Mismatch Edition of Principal and Mirror
I only had time to learn database mirroring using my personal account after work. So I screamed in my room at the moment when I realized that it is not allowed to have mirror server using Standard Edition while the principal is not using Standard Edition.
The mirror server instance cannot be Standard Edition if the principal server instance is not Standard Edition.
So in the end, I shut down the mirror instance and created another virtual machine which has Enterprise Edition SQL Server installed. Fortunately, it could be done quite fast on Microsoft Azure. I did not want to use back the old name so I named the new mirror server mydb-01-kagami.
Kagami means “mirror” in Japanese. (Image Credit: Lucky Star)
Mistake #4: Three Virtual Machines Not in Same Availability Set
The principal database, witness, and mirror database instances need to be put inside the same availability set.
When I was deploying the database mirroring, I forgot to have the witness instance in the same availability set as principal and mirror. So end up I couldn’t successfully connect to the witness from the principal.
Three instances need to be in the same available set.
Work and Learn
I spent about three days in Microsoft office to learn from Chun Siong. I then took another one month to do it myself. Wait, what? One month, seriously? Don’t be surprised. As usual, I have only little time (about half an hour per day) after work to do my personal projects. Sometimes, once I reached my room from office, I just jumped into bed and fell asleep within minutes. So, in fact, I only spent about 15 to 20 hours on learning database mirroring myself. Hence, I am really glad that I have colleagues as well as friends from Microsoft to be willing to support me in my learning journey.
Finally, some little notes to myself and readers who want to try out database mirroring (on Azure).
Be very careful during the whole database mirroring configuration process. Don’t have typo or set something wrongly. You may need to delete and create a new instance because of the mistakes;
Witness (but not principal and mirror) can use Express Edition of SQL Server. So, to save cost, please use that;
Set database to full recovery model before backing up the database on principal;
There are two options available in Microsoft Azure to host our SQL database. One is the well-known Azure SQL Database, an implementation of Platform as a Service for a relational database service in the cloud. The other one option is introduced after the new Infrastructure as a Service capabilities of Microsoft Azure. It is now possible to easily deploy instances of MS SQL Server in Azure Virtual Machine.
Azure SQL Database or SQL Server in Azure VM?
Personally, I prefer to directly deploy SQL Server in the virtual machine. At least the entire process looks about the same as what I have already done in our on-premise database server. So, having SQL Server deployed on Azure virtual machine actually means that the developers do not need to make huge changes to our existing applications. In addition, it’s also because migrating existing applications to the cloud normally needs to emulate on-premises behaviour. In short, choosing SQL Server in Azure virtual machine saves the time on migration.
Choose “SQL Server 2012” image to deploy MS SQL Server on the new virtual machine.
The following table shows the pricing of each edition running on Azure VM as well as the disk sizes available. Here I only pay attention to the memory intensive instances, i.e. A5, A6, and A7. They have larger RAM and disk sizes for the virtual machine and they are thus considered optimal for hosting databases and other high-throughput application. The data shown in the table is applicable for virtual machines deployed in Asia Pacific Southeast, i.e. Singapore.
Asia Pacific Southeast (Singapore) VM pricing for each edition of SQL Server (screenshot taken on 20 April 2014)
Connect to SQL Server Database Engine on Azure VM
After the virtual machine is up and running, we can immediately RDP in to the VM. Then in there, we just need to launch Microsoft SQL Server Management Studio to access the database with the Windows Authentication.
Running SQL Server Management Studio on the virtual machine.
Open TCP Port 1433
SQL Server typically uses TCP port 1433 for remote connections to the database. So, we need to add an endpoint as well as to open the port in the virtual machine firewall for this. However, to avoid security attack, it’s recommended to specify a different Public Port when creating the endpoint in Azure.
1433: A TCP port normally used by MS SQL Server for remote connection to the database.
SQL Server Authentication
We need to change the server authentication to “SQL Server and Windows Authentication mode”. This enables us to create logins in SQL Server which are not based on Windows user accounts. Both the login ID and passwords will be stored in the SQL Server. This allows SQL Server to continue supporting our third-party applications that require SQL Server Authentication. After that, we just right-click on the server in Microsoft SQL Server Management Studio Object Explorer to restart the server.
SQL Server and Windows Authentication Mode
Connecting Application to the SQL Server
To connect your ASP .NET web application with the database, in web.config, you can just key in the server name, port number together with login ID and password in the following connection string that is used to connect the instance of the SQL Server running on Azure VM.
Last week after uploading an updated version of my ASP web app to the server, it showed an error at the place where data is inserted into the database using a stored procedure.
The error message is “Procedure or Function ‘A’ expects parameter ‘@B’, which was not supplied”.
I double checked my code. Yes, all the required parameters present in the web service code. Yes, the stored procedure is written correctly. Yes, the code does supply the parameter. So, why is there an error?
I searched on Google and found that there were many people facing this problem. Yet, their solutions are all different from each other. So, I decided to share those I found. Besides, in the end, I will also talk about how I solved it eventually.
publiv void insertData()
{
SqlConnection con = new SqlConnection(str_con);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "InsertInfo";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
string name = Convert.ToString(txtName.Text);
cmd.Parameters.Add("@s_name", SqlDbType.VarChar).Value = name;
cmd.ExecuteNonQuery();
con.Close();
}
Stored Procedure
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[bkg_Insert_Members_FromCompanyID]
@s_name varchar(50)
AS
-- Variable Declaration
DECLARE @iReturn INT
INSERT INTO Members (member_name) Values (@s_name)
SET @iReturn = @@error
RETURN @iReturn
Although both CS code and stored procedure are written correctly, due to the fact that there is one line “exec <stored procedure name>” at the end of the stored procedure, the error happens. To solve the problem, just need to remove the unnecessary line.
The same error will be thrown if the name of the data field used in the CS code is different from the parameter name of the stored procedure or the name of the field in the table. I don’t know what to say besides \bat the programmer.
Yes, there is a value assigned to the parameter of the stored procedure in the CS code. However, the SQLCommand object is later replaced. Oh my… I have no comment about this.
Ah-ha-ha-ha, problem solved!. Image Credits: Little Busters! EX
So, how about my one? It turns out that it is because my web service project is not updated after doing the compilation. After rebuilding the web service project, everything is working again.
cuteprogramming 