2

I want to create a database within Azure SQL server I have gone through the following link : https://learn.microsoft.com/en-us/sql/relational-databases/databases/sql-server-data-files-in-microsoft-azure

but this didn't solve my problem and it took me to another end.

I have the following script which I have to execute on SQL server using management studio I can execute it on my local machine but I don't know what path should I use here ('D:\Databases\xxxxx.ldf') for Azure

CREATE DATABASE [xxxxx] ON  PRIMARY 
( NAME = N'xxxxx_Config', FILENAME = N'D:\Databases\xxxxx.mdf' , SIZE = 14336KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [SECONDARY] 
( NAME = N'xxxxx_Content', FILENAME = N'D:\Databases\xxxxx_1.ndf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'xxxxx_log', FILENAME = N'D:\Databases\xxxxx.ldf' , SIZE = 2377088KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
Sracanis
  • 490
  • 5
  • 25
  • I believe your paths would be something like : h t t p s: / / testdb.blob.core.windows.net/data/TestData.mdf which requires an Azure Storage account and a Container within the account. (note that there will be no spaces in the H T T P S) What error are you receiving? – DanielG Nov 09 '17 at 14:25
  • Is there any prerequisite for this as I tried this earlier but didn't work for me or is there any syntax issue? – Sracanis Nov 09 '17 at 14:29
  • Have you checked this out dE? https://msdn.microsoft.com/en-us/library/dn466438(v=sql.120).aspx I believe this article, coupled with the statements in the link you posted, should be correct. should be the same for Azure SQL as on-prem SQL. – DanielG Nov 09 '17 at 14:33
  • Yeah, I did but I think I have to read it again in detail. shouldn't there be a simple solution rather than creating all this? – Sracanis Nov 09 '17 at 14:37
  • Yea, seems like it. Q: Do you need to keep the MDF, LDF, etc. in Azure storage? Azure SQL will just handle this without a FILENAME specified, but it will maintain the location of the files for you as part of a PaaS offering. You can always send backups to a BLOB container if you need it. I am not sure of your situation, though. FileGroups are not supported either to my knowledge. You can also just connect SSMS to your Azure SQL instance and use SSMS as a management tool, to create databases as well. – DanielG Nov 09 '17 at 14:40
  • Well, I can skip that part but for the content, I need some location. If I am not wrong while creating the DB on Azure it must be saving MDF file somewhere, can't I access that location? – Sracanis Nov 09 '17 at 14:48
  • 2
    No, I don't think so. But I am not 100% sure. Azure SQL is designed as a PaaS service, and that feature is designed to be "out of sight out of mind". Whatever DTU, Elastic Pool, SLA, etc. you select in the Azure Portal, it just delivers on it as part of your pricing. The data is accessible as normal, backups, etc. but controlling the primary file and log file locations is handled by Azure SQL. It will internally store things proportional to the SLA (i.e. the monthly price you pay) and deliver the expected level of performance, scale, concurrency, reliability, DTU's, etc. that you pay for. – DanielG Nov 09 '17 at 14:53
  • You could always stand up a VM, install SQL, and control your own destiny. I personally enjoy the PaaS approach. It works quite well. Again, your situation may be different. – DanielG Nov 09 '17 at 14:58
  • Thanks Daniel information is really useful.. me too enjoy PaaS but i will get back here after some research may be azure storage can solve my problem – Sracanis Nov 10 '17 at 11:16

1 Answers1

2

@DanielG helped me to find a way. Actually if you are using PaaS then there is no way you can get the physical path but yes if you have setup your own VM then this can be achieved. I solved this issue by changing the my deployment server from azure.

Update:

thanks @DanielG

Recently, I did this by creating the container in the azure storage and blobs in the container. following are the steps: 1. First you have to create the azure storage account 2. Create a container. 3. Save the connection string. 4. Create a blob.

Here is the code to add an image file to the blob.

string _fileName = "";
string _uri = "";
string _localPath = "";
string _size = "";
MemoryStream ms = null;
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(CloudConfigurationManager.GetSetting("StorageConnectionString"));//defined in webconfig
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
CloudBlobContainer container = blobClient.GetContainerReference("imagecontainer");//your containner name in azure storage
_fileName = DateTime.UtcNow.ToString("HHmmss") + "_" + images.FileName + ".jpg";//set the file name as you like
CloudBlockBlob blockBlob = container.GetBlockBlobReference(_fileName);
blockBlob.Properties.ContentType = "image/jpg";
ImageHelper _ih = new ImageHelper();
ms = new MemoryStream(_ih.Resize2Max50Kbytes(images.Image1).ToArray());//its just some additional work
ms.Seek(0, SeekOrigin.Begin);
string imageFullPath = blockBlob.Uri.ToString(); 
using (var fs = ms)
{
    await blockBlob.UploadFromStreamAsync(fs);
}
_uri = blockBlob.Uri.AbsolutePath; //here you will get the path which you can keep in database.
_size = blockBlob.StreamWriteSizeInBytes.ToString();
_localPath = blockBlob.Uri.LocalPath;
var result = _entities.Images.SingleOrDefault(b => b.Id == images.Id);
_mainLog.Info("Image added: " + images.FileName);
if (result != null)
{
    result.FileName = _uri;
    result.Image1 = null;
    _entities.SaveChanges();
}
_processedCounter++;

Following image will help to create the container in azure.

enter image description here

Sracanis
  • 490
  • 5
  • 25