none
Create Sql Database using Elastic Database Agent

    Question

  • I have a portal written in ASP.NET Core 2.2 that is hosted in Azure.  This portal is used by our customers to manage their own data.  Each Customer's data must be stored in its own separate database.  

    So, from the portal I need to initiate the creation of a new Copy of a database I use as a template (also hosted in Azure).  Obviously, I can't have any heavy lifting on the Web Site but I would like to have the Elastic Database Agent create the database then apply whatever seed data is required.

    Can someone tell me the best approach to accomplish this?  Do I attempt Fluent?  Do I go with the REST approach?  I am comfortable with TSQL as well as C#.  I want this automated... not a list of powershell commands.

    A good working example or something close would be awesome.

    Thanks in advance for your help,

    Joel

    Tuesday, January 8, 2019 11:16 PM

All replies

  • I have a portal written in ASP.NET Core 2.2 that is hosted in Azure.  This portal is used by our customers to manage their own data.  Each Customer's data must be stored in its own separate database.  

    So, from the portal I need to initiate the creation of a new Copy of a database I use as a template (also hosted in Azure).  Obviously, I can't have any heavy lifting on the Web Site but I would like to have the Elastic Database Agent create the database then apply whatever seed data is required.

    Can someone tell me the best approach to accomplish this?  Do I attempt Fluent?  Do I go with the REST approach?  I am comfortable with TSQL as well as C#.  I want this automated... not a list of powershell commands.

    A good working example or something close would be awesome.

    Thanks in advance for your help,

    Joel

    Good day,

    >> Each Customer's data must be stored in its own separate database. 

    are you sure that you use the right solution for your case?
    Just to clarify, using Azure Database you basically talking about creating new service for each customer

    With that being said using Azure Elastic Jobs sound perfect for this case. You can put all the databases under Elastic Pool or simply under a specific logical server (which will be your Job Target) and this way, using Elastic Job you can manege all the databases in the target without the need to manually add/remove databases. Any new DB in the target will automatically be managed by the job :-)

    >> I can't have any heavy lifting on the Web Site

    I don't see how this is relevant. Since you are using Azure Database as a service which is totally separate services from your website.

    >> I would like to have the Elastic Database Agent create the database

    Are you sure that you understand the meaning of Elastic Job and Elastic Agent? I cannot elaborate in one message in th forum and maybe it is a good time to re-think about your architecture. I will try to give some direction on the problematic of your request:

    Azure Elastic Agent does not create anything, but only manages Elastic jobs. What you do in the hobs is a different story.

    Unfortunately, the jobs executed on the databases level. Since Azure does not support three parts names, and Elastic Job meant to be executed on multiple user's database and/or schedule execution, it does not sound like something that have any relation to Elastic Jobs.

    Can you elaborate what is the relation to Elastic Job?
    * CREATE DATABASE is executed on the master database only, and it does not sound like you need to schedule the execution sine it make no sense that you get exactly one new customer every X time. According to your description you need to execute the CREATE DATABASE once for each new customer.

    >> I want this automated.

    What exactly you want to automated ?!?
    THIS IS THE MOST IMPORTANT POINT TO UNDERSTAND WHAT YOU NEED

    Do you need to schedule the task for specific time,or for every interval of time (for example every day). If the answer is no, then what is the trigger that will execute this automated task?

    >> Can someone tell me the best approach to accomplish this?... I am comfortable with TSQL as well as C#.

    Once I will, get more information (especially the point above regarding the meaning of the automated the task), I will probably be able to point you better to a solution, but in the mean time using the description here, in first glance I don't see any relation to Elastic Agent (for the creation of the new database) and since you familiar with C# and you come from the developing side, It sound to me in first glance that you should think about using Azure Function and maybe combine it with Azure Automation.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, January 9, 2019 12:33 AM
    Moderator
  • This is my admin use-case:  A customer is registered in the portal. For legal (and other) reasons these customers cannot share a database.  Therefore, after a customer record is created I need to have a new database (in Azure) created.  The new database does not need to be created within milliseconds of when the Customer record is created.  However, my admin will expect to create the customer record, have the database created and "seed" the new database with customer-related data in one sitting.  So, the new database should be created within minutes and the non-technical admin should not need to care about how or what creates the database.

    This is my user use-case:  A user signs up for our service.  The admin sets up the infrastructure (outlined above).  The user registers with our site and, depending on their identity and the relationships we set up during registration, gives them access to the appropriate data store.  Data is uploaded from proprietary systems either using parsed data from BLOB storage in a Azure Function or by using the MVC Service Endpoint; but thats not relavant to this question.

    It is unclear what you mean when you say "create a new service for each customer".  SqlServer hosted in Azure is a data store, it is not a service (unless you're referring to SaaS or PaaS).  Azure Database Agent is a service that performs actions against Sql Server databases in Azure.  So, back to the original question.  Can a new Sql Database be created from an Elastic Job?  If so, I would use the admin's action of creating a new customer to also then kick off a new job.  However, all examples I have found for the Elastic Job show them running against EXISTING databases.  I have never found an example that shows an Elastic Job used to Create a database inside of an Elastic Pool. If not, what is my next best option?  

    --No heavy lifting on the Web Site.  What is unclear about this?  A web site should not be used to perform long-running tasks like creating a databases.  That kind of task needs to be handed off to an external service.  Hopefully, a Elastic Job in this case.  

    --Elastic Database Agent.  Semantics.  What good is an Elastic Job unless something kicks it off?  That's done by the Agent.  To get the job done you need a definition and an actor and neither are useful without the other.  Original question, can a Sql Database be created as part of an Elastic Job by executing custom TSQL like:
    CREATE DATABASE NewCustomerDb as COPY OF CustomerDb

    Yes, this is part of an Elastic Pool.  I plan to use the Elastic Database Agent in order to keep all of these databases updated (schema changes, etc) and, when appropriate, harvest some metric data from each database.  I believe it will be a good solution.  

    Wednesday, January 9, 2019 4:19 PM
  • To keep this simple, no elastic database jobs cannot be used to create a new database.

    Elastic database jobs operate on a database, at the database level, so they are intended to run SQL commands against a specific database. They do not operate at the server level (yes you can target a server, but that is just running against all DB's on a server), which is where a create database operation would be undertaken.

    You would instead be better off looking at using Azure Automation to run PowerShell tasks that create Azure SQL databases using Azure cmdlets. You could still trigger this from your web app by calling it over a webhook.


    Sam Cogan Microsoft Azure MVP
    Blog | Twitter

    Wednesday, January 9, 2019 7:24 PM
  • This is my admin use-case:  A customer is registered in the portal. For legal (and other) reasons these customers cannot share a database.  Therefore, after a customer record is created I need to have a new database (in Azure) created.  The new database does not need to be created within milliseconds of when the Customer record is created.  However, my admin will expect to create the customer record, have the database created and "seed" the new database with customer-related data in one sitting.  So, the new database should be created within minutes and the non-technical admin should not need to care about how or what creates the database.

    This is my user use-case:  A user signs up for our service.  The admin sets up the infrastructure (outlined above).  The user registers with our site and, depending on their identity and the relationships we set up during registration, gives them access to the appropriate data store.  Data is uploaded from proprietary systems either using parsed data from BLOB storage in a Azure Function or by using the MVC Service Endpoint; but thats not relavant to this question.

    It is unclear what you mean when you say "create a new service for each customer".  SqlServer hosted in Azure is a data store, it is not a service (unless you're referring to SaaS or PaaS).  Azure Database Agent is a service that performs actions against Sql Server databases in Azure.  So, back to the original question.  Can a new Sql Database be created from an Elastic Job?  If so, I would use the admin's action of creating a new customer to also then kick off a new job.  However, all examples I have found for the Elastic Job show them running against EXISTING databases.  I have never found an example that shows an Elastic Job used to Create a database inside of an Elastic Pool. If not, what is my next best option?  

    --No heavy lifting on the Web Site.  What is unclear about this?  A web site should not be used to perform long-running tasks like creating a databases.  That kind of task needs to be handed off to an external service.  Hopefully, a Elastic Job in this case.  

    --Elastic Database Agent.  Semantics.  What good is an Elastic Job unless something kicks it off?  That's done by the Agent.  To get the job done you need a definition and an actor and neither are useful without the other.  Original question, can a Sql Database be created as part of an Elastic Job by executing custom TSQL like:
    CREATE DATABASE NewCustomerDb as COPY OF CustomerDb

    Yes, this is part of an Elastic Pool.  I plan to use the Elastic Database Agent in order to keep all of these databases updated (schema changes, etc) and, when appropriate, harvest some metric data from each database.  I believe it will be a good solution.  

    hi Joel :-)

    let me go over your massage and add some insights, but I must say that in general I already think that I gave you all the inportant main points which include Elastic Job works in the database level, You can use Azure automation to schedule tasks and Azure functions for the task)

    >> A customer is registered in the portal... Therefore, after a customer record is created I need to have a new database (in Azure) created.

    How and where the record is created? Do you mean for any user that login the Portal? In this case you can trigger execution of Azure functions using the Azure Alerts service. In the task you can create the database immediately or (better probably) schedule it.

    * This make no sense to me that for each user that login you will create a new database but this is a different discussion.

    >> The user registers with our site...

    This mean that you can execute the create of the database using your application (the site)! this make sense. You should manage everything in your side and not give control to the user (directly or indirectly).

    >> It is unclear what you mean when you say "create a new service for each customer".

    Azure SQL Database is a service and your request was to create a database for each user, which mean you create a new service for each user. This sound like a bad idea for most cases. 

    * You should remember that if you chose to use Azure SQL Database then you do not control the server and the "server" which you have is only a virtual element created in the Azure application and not in the real server! The master database which you use is not a real master database like on-premises SQL Server master database.

    >> Sql Server hosted in Azure is a data store

    There is a server behind the scenes but you do not have any direct relation to it! You only use application (Azure Engine) which provide you a service of virtual server (in the real server there can be multiple virtual servers and hundreds of databases which serve as "master" for the user).

    * I ASSUME THAT YOU USE AZURE SQL DATABASE since this is the forum we are in. If you are using Azure Managed Instance or Azure SQL Server on Virtual Machine, then this is a different story.

    >> Can a new Sql Database be created from an Elastic Job?

    Again! Elastic Job works in the database level and not in the server level. It uses to manage multiple databases by executing the same queries (jobs) on each database in the target SEPARATELY. It does not work on the server level and it is pretty clear that it is not relevant to your needs.

    >> I have never found an example that shows an Elastic Job used to Create a database

    Obviously...since CREATE DATABASE is a server level operation and... (please read above)

    >> A web site should not be used to perform long-running tasks like creating a databases.

    TRUE (in most cases)!

    A website should be the trigger for such task, and the task should be done behind the scenes, for example using Azure Functions.

    >> Yes, this is part of an Elastic Pool. 

    If the Elastic Pool includes a database or multiple databases and the Pool is configured as the target, then the job is executes against each one of these databases! if you have 100 databases in the POOL, Do you want to execute CREATE DATABASE 100 times?!? Moreover, do you exceute CREATE DATABASE on a user DATABASE?!? A CREATE DATABASE is a server level task which is execute on the real "master" database only. Using the Azure services we use a service of a "master" database which is not the real master, and the Azure Engine (not the SQL Server) know how to use the queries sent to our master to the real master - this is part of the service which we get.

    >> Hopefully, a Elastic Job in this case.

    Totally not relevant to your description :-) 


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Thursday, January 10, 2019 4:05 PM
    Moderator
  • Follow Sam's advice. You can now use: New-AzureRmSqlDatabase to create an elastic database per the cmdlet description.

    Here are all the AzureRM.Sql cmdlets. Through Azure Automation and WebHooks, you can automate all of this: Azure Automation or Azure Function when creating an Azure SQL database

    I hope this information is useful.

    Regards,

    Mike

    Friday, January 11, 2019 7:05 PM
    Moderator