Azure – Creating My First Elastic Database Job

azureazure-sql-database

On https://docs.microsoft.com/en-us/azure/sql-database/sql-database-job-automation-overview it says that there is a new replacement for SQL Agent. However, I cannot get it to work.

I had problems with accepting the "Beta software" terms, but my manager accepted the terms on his account, and I could carry on.

On the portal I get this error message:

Deployment failed
Deployment to resource group 'MyRG' failed.
Additional details from the underlying API that might be helpful: At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-debug for usage details.

I have also tried the Powershell interface, and here I get stuck on

New-AzSqlElasticJobAgent : No tenant found in the context.  Please ensure that the credentials you provided are authorized to access an Azure subscription, then run Connect-AzAccount to login.
At line:4 char:28
+ $JobAgent = $JobDatabase | New-AzSqlElasticJobAgent -Name $AgentName
+                            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [New-AzSqlElasticJobAgent], ArgumentException
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.ElasticJobs.Cmdlet.NewAzureSqlElasticJobAgent

It is probably something simple, but I cannot figure out what…

Best Answer

The error on the portal is not clear, but you should be able to find details in deployment summary.

On portal go to your resource group and navigate to Deployment tab. There you can find history of the deployments with details about progress and errors.

The problem with Powershell looks like you don't set subscription context.

You should set the context to subscription which contains Reasource Group to which you want to deploy content.

To make this work use this example: Example 1: Set the current subscription

After apply current subscription you should be able to see what is the real error during deployment.

Related Solutions

Create user on Azure elastic database and deny view to other databases i same setting

Reading you comment I see now what you are looking for.

Here is how you can do this.

Create 2 empty database:

USE [master]
GO
CREATE DATABASE [TestPerm1]
GO
ALTER DATABASE [TestPerm1] SET COMPATIBILITY_LEVEL = 130
GO


USE [master]
GO    
CREATE DATABASE [TestPerm2]
GO    
ALTER DATABASE [TestPerm2] SET COMPATIBILITY_LEVEL = 130
GO

Create 2 login

USE [master]
GO    
CREATE LOGIN [testPerm1] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [master]
GO    
CREATE LOGIN [testPerm2] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Create a user in master using one of the login

USE [master]
GO
CREATE USER [testPerm2] FOR LOGIN [testPerm2] WITH DEFAULT_SCHEMA=[dbo]
GO

Using SSMS connect with user testPerm2 using default setting. You will be able to see all database ( I know you do not want this, but hang on)

Create a user with testPerm1 in testPerm1 database that we created earlier.

USE [TestPerm1]
GO
CREATE USER [testPerm1] FOR LOGIN [testPerm1] WITH DEFAULT_SCHEMA=[dbo]
GO

Now using SSMS connect to the server by changing default database, this user will only see testPerm1 but not testPerm2.

Is the database in the elastic pool

Looks like the database is not in the pool. Go to the Elastic Pool in the portal and click on Configure Pool, and Add Databases.

Best Answer

Related Solutions

Create user on Azure elastic database and deny view to other databases i same setting

Is the database in the elastic pool

Related Question