none
SQL Server installation using Azure VM role

    Question

  • Hi

    I am trying to use VM role in windows azure. I want to install SQL server and share point on VM and i will use this VHD as a base image to upload to azure as VM role. Lets say my application of VM role is on Azure and the share point web sites hosted are being used by users. Hence user data is also geting added to SQL data files which is installed on C drive of VHD. Now VM role does not have facility of data persistence. So in case of failure, documentation says that, VM role base image will be uploaded again. Hence user added data will be lost; coz after failure azure will load base image having share point server and SQL server installed on it but no data. So what is the solution if I need to achieve data persistance? And in case of failure,will it load SQL server from base image with master database files or those  will be also be lost?

    I want to use VM role only. Thanks is advance.

    Wednesday, February 02, 2011 11:15 AM

Answers

  • Hi All,

    Thanks for all your support. Finally I could install SQL 2008 R2 on VM role and upload it on azure. I created a vhd of 512MB on my target VM VHD using diskmanagement.(diskmgmt.msc). Then while SQL instalation I gave path of this newly created drive for data files. Then uploaded this drive as a Page blob on azure. This solved the problem of data persistance. Then wrote a windows service which will start on VM role initialization. The windows service will mount this page blob as Azure drive in start up of vm role instance.

    These steps solved my purpose.

    Hope this helps.

    Thursday, February 10, 2011 4:41 AM
  • The VM role is not made for this scenario. Apps on Azure must be stateless - either using a pre-defined images (aka web/worker role) or by using a base image you supply.
    Dominick Baier | thinktecture | http://www.leastprivilege.com
    • Marked as answer by Mog Liang Wednesday, February 09, 2011 8:29 AM
    Wednesday, February 02, 2011 9:32 PM

All replies

  • The VM role is not made for this scenario. Apps on Azure must be stateless - either using a pre-defined images (aka web/worker role) or by using a base image you supply.
    Dominick Baier | thinktecture | http://www.leastprivilege.com
    • Marked as answer by Mog Liang Wednesday, February 09, 2011 8:29 AM
    Wednesday, February 02, 2011 9:32 PM
  • Dear Dominick,

    Thanks for the reply. Could you please elaborate more on the above said statement. Any specific reason why VM role is not suitable for this scenario?

    Thursday, February 03, 2011 4:32 AM
  • By describing how it works, you answered the question yourself. Whenever Microsoft sees fit, it can start fresh from the base + differencing images you uploaded at some point. Then all data in between is lost.
    Dominick Baier | thinktecture | http://www.leastprivilege.com
    Thursday, February 03, 2011 5:22 AM
  • you can mount a drive to blob storage, and put your databases there. Requires some work, and you're certainly unsupported scenario, but that's the idea how it could work.
    Thursday, February 03, 2011 5:32 AM
  • Yes - that is unsupported. Just the right environment for your SQL Server data ;)
    Dominick Baier | thinktecture | http://www.leastprivilege.com
    Thursday, February 03, 2011 5:44 AM
  • Hi All,

    Thanks for all your support. Finally I could install SQL 2008 R2 on VM role and upload it on azure. I created a vhd of 512MB on my target VM VHD using diskmanagement.(diskmgmt.msc). Then while SQL instalation I gave path of this newly created drive for data files. Then uploaded this drive as a Page blob on azure. This solved the problem of data persistance. Then wrote a windows service which will start on VM role initialization. The windows service will mount this page blob as Azure drive in start up of vm role instance.

    These steps solved my purpose.

    Hope this helps.

    Thursday, February 10, 2011 4:41 AM
  • Wouldn't SQL Server need to know the drive letter?  With mounting an Azure drive, the drive letter wouldn't be known until after the drive is mounted.  How would you work around that?
    Saturday, February 12, 2011 1:58 AM
  • 'though the drive letter looks arbitrary in first place, you can use DiskPart to change it to one you need or used before when configuring the SQL Server. That could be scripted.

    Another option could be to have a VMRole and a "blank" SQL Server instance with master DB on the system drive, but just attach any user databases on persistent drives at runtime from whatever driveletter. Not sure how reliably that works, maybe better question for some SQL Server forums

    Saturday, February 12, 2011 4:28 PM
  • hi Michael,

    First I created drive with 512 mb on target VM(VM which i will be using for VM role) and named it as H . Then installaed SQL on target vm with path of data files to H drive. Then detached VHD from target VM and uploaded corresponding vhd of sql data files as page blob. Then created a windows service which loads this page blob as Azure drive and installed on target VM. 

    Now when SQL server service and agent starts on azure vm role, it will require data files and those will be loaded as drive. Loading drive on vm role will require some time. Therefore in services.msc of target vm i changed the properties of SQL Server service, SQL agent service and my own windows service to automatic delayed and in recovery tab - "restart the service" option selected  on all failure options. So after mount drive operation completes on azure vm role, SQL services starts normally. And in windows service code is writen in such a way that, drive mounted with exactly same name "H" which was supplied during SQL installation on target vm.

    Hope this helps.

    Let me know if anyone requires more information.

    Monday, February 14, 2011 6:39 AM
  • Hi Azure Factory,

    I'm interested in having such scenario you have mounted... Could you please explain or reference how to create teh service for mounting the Azure Drive? I have tried with this link, but the drive is not being mounted.

    By the other hand, if you change in VM role the properties of SQL Server service once on Azure, there when reimage you'll lost those changes, did you changed the times before loading the vm image to Azure? And last, how do you assure that drive name is "H".

    If you could please provide more detailed info, I'm very interested in this scenario.

    Thanks a lot,

     


    Maick · augurarte :: Albacete · Spain : Guia practica Expression Web, Anaya Multimedia
    Tuesday, May 24, 2011 5:49 PM
  • Yeah - so much work for a completely unsupported installation ;)

    Don't expect anyone to help you when SQL stops working or your data is lost...


    Dominick Baier | thinktecture | http://www.leastprivilege.com
    Tuesday, May 24, 2011 5:53 PM
  • Dominick...

    Azure unsoported? Azure Drive unsoported? Worker roles and Sync Framework unsoported? Please, would you mind to provide some link about the lack of Microsoft support of these scenarios? It would help, of course... Is there something wrong in using Azure Compute Hours -you pay for it- in let's say... "compute"... ?


    Maick · augurarte :: Albacete · Spain : Guia practica Expression Web, Anaya Multimedia
    Tuesday, May 24, 2011 9:08 PM
  • not that Azure is unsupported, but the deployment of products such as SQL Server is not supported. You can still do that, no doubt. But there wouldn't be product support for your SQL Server installation if this is running in Azure. As Dominick is mentioning, if your SQL stops working for whatever reason, you can't expect MS taking responsibility. 

    On your questions, for mounting a drive you should have a look on this: How to Develop an Adapter for a VM Role in Windows Azure http://msdn.microsoft.com/en-us/library/gg466226.aspx

    Once you have a drive mounted, you can use DiskPart to assign a driveletter of your choice. And yes you're correct, all configuration needs either be backed in your base image, or you need to configure these things in the startup script.

    Tuesday, May 24, 2011 9:20 PM
  • Hi Miguel,

    You will have to create a windows service in VS 2010. Use "Microsoft.WindowsAzure, StorageClient and ServiceRuntime" namespaces. Add code to Create Cloud Drive. you can use methods like -

    //first initialize blob and container and then mount drive

    CloudDrive drive = this.storageAccount.CreateCloudDrive(string.Format("{0}/{1}", this.ContainerName, driveName));

    drive.Mount(cachesize, DriveMountOption.Force);

    Then required container name and drive name like "H" can be read from app.config file. This is how you can make sure that mounted drive name will be H.

    Your App.config will have values for example as follows -   <appSettings>
        <add key="ContainerName" value=""/>
        <add key="CachePath" value=""/>
        <add key="CacheSize" value="20;30;"/>
        <add key="AccountName" value=""/>
        <add key="AccountKey" value=""/>
        <add key="DriveNames" value="drive1.vhd;"/>
        <add key="DriveLetters" value="H"/>
        <add key="DriveSizes" value="11000"/>   
      </appSettings>

    Install this windows service on Base VHD. Make sure that you set it to Automatic delayed in Base VHD itself. Yes, I never tried to make any changes by taking RDC to VM role. All the changes that you want needs to be carried out on Base VHD iteself. Or as stated by PerpetualKid, carry it on startup.

    Hope this helps.


    Mark As Answer if it helps you | My Blog
    Wednesday, May 25, 2011 5:58 AM
  • Wouldn't SQL Server need to know the drive letter?  With mounting an Azure drive, the drive letter wouldn't be known until after the drive is mounted.  How would you work around that?

    there is a post detailing how to use diskpart for changing drive letter of azure drive : Changing Drive Letter of an Azure Drive



    If this post answers your question, please mark it as an answer. If this post is helpful to you, then vote it as helpful.
    TechyFreak | Mobile Development Resources
    Wednesday, May 25, 2011 9:43 AM
  • Hi Azure Factory,

    I'm interested in having such scenario you have mounted... Could you please explain or reference how to create teh service for mounting the Azure Drive? I have tried with this link, but the drive is not being mounted.

    By the other hand, if you change in VM role the properties of SQL Server service once on Azure, there when reimage you'll lost those changes, did you changed the times before loading the vm image to Azure? And last, how do you assure that drive name is "H".

    If you could please provide more detailed info, I'm very interested in this scenario.

    Thanks a lot,

     


    Maick · augurarte :: Albacete · Spain : Guia practica Expression Web, Anaya Multimedia
    check this link for moutnign azure drives in VM ROle : http://techyfreak.blogspot.com/2011/02/mounting-azure-drive-in-azure-virtual.html
    If this post answers your question, please mark it as an answer. If this post is helpful to you, then vote it as helpful.
    TechyFreak | Mobile Development Resources
    Wednesday, May 25, 2011 9:45 AM
  • Hi TechyFreak,

    This is the process I followed, but the service is never starting due an error, so the azure drive was never mounted :-( The error initializing the service (LogDriveService) is
        " Windows could not start the LogDriveService service on Local Computer.

        Error 1067: The process terminated unexpectedly. "

    I have followed this tutorial online: http://msdn.microsoft.com/en-us/library/gg466226.aspx

    And I have tried to use this sample is available online: http://code.msdn.microsoft.com/Windows-Azure-LogDrive-9e76211a

    I followed scropoulosy the rules and steps, but, in both two cases I couldn't start the service with the same result :-( Any suggestion ?


    Maick · augurarte :: Albacete · Spain : Guia practica Expression Web, Anaya Multimedia
    Thursday, June 02, 2011 8:19 AM
  • Are you trying it on a VM Role already deployed on Azure or you are running it locally as of now?

    The service will not mount the drive locally, until you deploy it to azure, this is because azure drive needs Windows Azure Environment to be mounted.


    If this post answers your question, please mark it as an answer. If this post is helpful to you, then vote it as helpful.
    TechyFreak | Mobile Development Resources
    Thursday, June 02, 2011 8:22 AM
  • I followed the documentation, so I created the service on VS2010, create the installer, and installed the services into the Hyper-V Image, BEFORE uploading, anyway, the service didn't started neither before uploading nor after uploading.

    As I mentioned before, at the end, after running the setup, the service appear in the services list, but stopped and is not automatically starting (nor manual). Any thoughts?


    Maick · augurarte :: Albacete · Spain : Guia practica Expression Web, Anaya Multimedia
    Friday, June 03, 2011 2:19 PM
  • If service starts and stops it means an error has occured. Could you please check your event view's Application Logs, and see what error message it display
    If this post answers your question, please mark it as an answer. If this post is helpful to you, then vote it as helpful.
    TechyFreak | Mobile Development Resources
    Friday, June 03, 2011 3:19 PM