Questions tagged [azure-sql-database]

Azure SQL Database is a relational database-as-a-service from Microsoft. This tag is for questions about Azure SQL Database, NOT SQL Server hosted in an Azure VM (use azure-vm).

Filter by
Sorted by
Tagged with
0 votes
0 answers
7 views

How to find statement causing "The user does not have permission to perform this action." in Azure SQL Database?

I ran a Kusto query: AzureDiagnostics | where TimeGenerated > ago(1d) | where Category == "Errors" and error_number_d == 297 | summarize count(), min(TimeGenerated), max(TimeGenerated) ...
user avatar
  • 2,028
2 votes
0 answers
60 views

Is this the correct way to select with updlock and then conditionally insert?

I have a table which tracks 'reservations', defined as follows: CREATE TABLE [dbo].[Reservation] ( [OrderId] nvarchar(40) CONSTRAINT [PK_Reservation_OrderId] PRIMARY KEY CLUSTERED, [GroupId] ...
user avatar
0 votes
1 answer
8 views

AZURE SQL Database Dynamic Ip address

I have an Azure SQL Database and my clients want to access it. They belong to a place where they are assigned new IP every time whenever they turn on PC/Internet. I have tried to assign a range to ...
user avatar
26 votes
1 answer
2k views

Why MERGE doesn't insert more than 277 records into a table which is configured with temporal table and a non-clustered index on history table

I found again an issue with SQL Server and MERGE statement and need some confirmation. I can reproduce my issue constantly on a Azure Database (but not on a on premise SQL Server 2017/2019). Please ...
user avatar
  • 363
0 votes
0 answers
18 views

Azure SQL Server grant read only access to all databases, including those created later

There is one Azure SQL Server which has 100+ databases. For auditing purposes a login is needed, that can read data (do SELECT) from all the database tables in that Azure SQL Server. Databases are ...
user avatar
0 votes
1 answer
35 views

Create NonClustered online vs Create NonClustered offline

I understand that Nonclustered index uses bigger space than clustered index. Azure SQL provides option to create Nonclustered index using both ONLINE = { ON | OFF }. How is the NCI creation different ...
user avatar
0 votes
1 answer
9 views

Different DTU - Service tier between Master and Replica azure database

I've got an azure db with replica. the master has: Standard S6- 400 DTUs the replica has: Standard S3- 100 DTUs Is it a problem for a replica to be in a different service tier and dtu than master?
user avatar
  • 1
-2 votes
1 answer
15 views

Do we have to implement syspolicy_purge_history job in case of azure database, as there is no msdb

I migrated from sql server to Azure db. While migrating I found a job named syspolicy_purge_history (sp_syspolicy_purge_history), do I also need to implement it on Azure db. As I found the ...
user avatar
0 votes
1 answer
42 views

Can we delete data using SQL job and insert data into the same table at the same time from another application

I have created one SQL job and it is executing a stored procedure to delete data from the table at a specific time. But at the same time, if anyone will try to insert a record in that same table from ...
user avatar
-2 votes
0 answers
93 views

Can we get deleted records back in SQL Server without backup

I have deleted some records accidentally. and I tried to retrieve it with the below query, SELECT [Current LSN],[Transaction ID], Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE ...
user avatar
1 vote
2 answers
51 views

Azure SQL - altering MAX DOP value at Database scope level is not taking place

I am trying to change the MAX DOP value for my Azure Managed SQL which currently is set to ZERO. Here is the script I am running: USE [my-sql-db] GO ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP =5 ;...
user avatar
  • 221
0 votes
0 answers
15 views

High usage on Azure log analytics

I have a relatively simple web app set up with a SQL database, and we have log analytics. We have "Auditing" turned on for the SQL server, and for some reason I'm seeing 2.8GB of usage a day ...
user avatar
  • 1
0 votes
0 answers
32 views

Is it an option to deploy specific schema with objects - procedures, funtions, views to multiples databases?

We have schema utils which we would like to deploy with database objects (procedures, functions, views) to multiple databases on different servers via Azure Devops Pipeline. Is there any option to ...
user avatar
  • 307
2 votes
1 answer
164 views

Azure SQL Database - contained users password

Where is the password saved when creating a contained user for the Azure SQL database (PaaS)? For example: CREATE USER taiobtest WITH password='PVHz3U4A$LNytQF^'; GO For server logins I can get it ...
user avatar
  • 12.3k
0 votes
1 answer
63 views

In ADF, are copy data activities wrapped in transactions?

I have a copy data activity that is moving data from a managed instance to a sql database. The flow of the process is: truncate a staging table on the sql database as a distinct activity call a ...
user avatar
  • 101
0 votes
0 answers
24 views

Know which specific rows are locked in a KEY X lock? [duplicate]

I'm investigating a lock issue on a query I have and I already can see some KEY locks. By joining with sys.partitions and sys.indexes I know what's the index it's doing the lock but what I wanted now ...
user avatar
  • 147
0 votes
1 answer
90 views

SQL Server Timeout only on production

I have an API endpoint that runs a query. For some reason, when I hit the production endpoint the query times out (after 2 minutes) but when I hit the same endpoint locally with my local environment ...
user avatar
  • 59
1 vote
1 answer
32 views

Azure SQL Average lock wait time logs?

I'm running an Azure SQL database with a webapp connected to it and we are now getting into some situations where requests timeout, namely some delete / insert operations that I'm already pretty sure ...
user avatar
  • 147
3 votes
2 answers
403 views

In SQL Server / Azure SQL is there a way to see the locks a query produced or wants to produce?

I'm trying to investigate a lock situation. I know how to see the current locks on DB by querying sys.dm_tran_locks but what I want is to take a specific SQL Query and having a kind of 'analysis' on ...
user avatar
  • 147
0 votes
0 answers
42 views

How can I see the queries executed on day x? (So I can find the DB change that is impacting performance.)

Since Day X (which is inconveniently just outside my DB recovery plan), I have been experiencing a significant performance degredation on an overnight process that runs Stored Procedure A, executed ...
user avatar
1 vote
1 answer
50 views

Azure Database Performance Throttling - Use Replication or?

We keep a "rather large" azure database where we load all of our company data into every night. It uses temporal tables to keep track of changes to the data Now we want to start reading this ...
user avatar
  • 113
2 votes
1 answer
35 views

Error trying to add a login to Azure SQL Special Role 'dbmanager'

Upon running these commands on an Azure SQL instance: CREATE LOGIN test WITH PASSWORD=N'Passw0rd' GO ALTER ROLE dbmanager ADD MEMBER test GO ... I'm getting an error: Cannot add the server principal ...
user avatar
  • 429
0 votes
2 answers
209 views

How to get queries executed in Synapse SQL?

I need to find all the query executed on my DB in last 1Hr in Synapse workspace with SQL pool. Will apply my logic on top of the result set. What's the right table/view to look for? sys....
user avatar
1 vote
2 answers
79 views

How can one authenticate with AzAD while using Read-SqlTableData to read AzSQL tables

How should one specify the User ID while creating the $sqlcc object when using Read-SqlTableData to read a table in AzSQL with an Azure account? $sqlcc = new-object ('System.Data.SqlClient....
user avatar
-2 votes
1 answer
75 views

SQL Azure simple update fails (or takes many minutes) not due to blocking

We have a table PropertyDefinition with 900 rows. A very simple update statement fails (or sometimes succeeds after 5 minutes). We checked for blocking SPIDs and there weren't any. We are in SQL Azure ...
user avatar
  • 145
-1 votes
1 answer
72 views

Azure SQL Server automatic tuning - Drop index

If I allow Azure to automatically drop indexes, will they only drop indexes that they auto-created or even on ones that I created?
user avatar
  • 59
1 vote
1 answer
30 views

Can one log manual data changes to an Azure SQL table using Set-AzSqlDatabaseAudit?

This is a follow-on question to Logging just one table in an Azure SQL Database It is possible to connect to my Azure SQL Server database using SSMS (Microsoft SQL Server Management Studio), and an ...
user avatar
  • 205
0 votes
0 answers
144 views

Azure SQL BACPAC import Failed

I have created a SQL Server in Azure and now I'm trying to import a database from a BACPAC file. After about 10 minutes, the Azure portal logs to the import/export history: An unexpected error was ...
user avatar
0 votes
1 answer
135 views

Azure SQL - Hyperscale service tier - are table partitions supported

In the Azure SQL DB, running in the Hyperscale service tier, are table level partitions supported? It does not seem to be called out in the known limitations page. While trying to create a filegroup ...
user avatar
1 vote
1 answer
71 views

Logging just one table in an Azure SQL Database

Our company is ISO27001 certified. As such, it is required for us to log database events. We are taking an incremental approach, and first starting with logging events just on one table. This ...
user avatar
  • 205
2 votes
0 answers
53 views

How to create logins in sql server based on the Azure Active Directory?

In my Azure environment I have many resources, and I have a virtual machine that contains my sql server database This is my virtual machine: What I would like to achieve is to create logins inside my ...
user avatar
0 votes
2 answers
300 views

Grant View Definition for All Stored Procedures

What is the approach to granting a user the ability to view all stored procedures across all schemas? The command such as GRANT VIEW DEFINITION ON [dbo].[{SprocNameHere}] TO [{UserNameHere}] is a ...
user avatar
  • 377
0 votes
1 answer
102 views

Reclaim unused allocated space in Azure Elastic pool data storage (sql)

In Azure I have SQL Database elastic pool on a DTU based purchasing model (Standard). In the Pool I have 7 SQL databases These are my storage stats: Used Space 50GB Allocated Space 105GB Maximum ...
user avatar
0 votes
1 answer
28 views

Unique identifier per database "copy" on Azure?

Every few weeks I delete our Test database and make a new copy from Production. I do want to run some scripts (from our application code) the first time one of these "freshly" created test ...
user avatar
  • 333
6 votes
2 answers
490 views

Non parallel plan only in Azure when SESSION_CONTEXT is used

I'm observing strange difference between query plans that I'm getting on my local machine and on Azure SQL. I'm trying to implement row level security, where I read user identifier from ...
user avatar
8 votes
1 answer
690 views

Why does OPTION RECOMPILE cause a predicate pushdown?

I have a SQL query that is made out of a nested mess of views and table-valued functions going on for at least 4 levels deep (I didn't have the time or patience to go through it all, it's hundreds of ...
user avatar
0 votes
1 answer
42 views

Is there a way to make Azure SQL Database report a newer version number?

We are trying to get Moodle installed in Azure, and the most cost-effective way to do this for our purposes is to use Azure SQL database. Azure SQL database is always running the latest stable build ...
user avatar
  • 21
0 votes
0 answers
43 views

Azure SQL truncate table schema locking

Related to this question but about Azure SQL and causing other issues. We have data factory pipelines that replace all data in certain tables. The pipelines can take several minute to run and to ...
user avatar
1 vote
2 answers
116 views

What is most efficient design pattern for a sql database containing billions of rows per user in a single table?

I work on a relatively large system where have started to run into performance problems scaling for multiple users. The system is a .NET application, so query's are written using an ORM (entity ...
user avatar
  • 13
4 votes
1 answer
113 views

TSQL Query to match different lengths of strings with each other

I'm writing a TVF to query a large table (tens of millions of rows) where a string (Postcode) in that table matches a string (a partial postcode (outcode/sector portion)) from another table. I'm ...
user avatar
  • 259
2 votes
1 answer
41 views

Is there any harm in storing other rows' int PKs in a hierarchyID instead of an arbitrary number?

I'm looking at common uses of hierarchyID, and most examples use some sort of arbitrary numbering for the HID nodes: HID, Name /, World /1/, USA /1/1/, Texas /1/1/1/, Houston /1/1/2/, San Antonio /1/2/...
user avatar
0 votes
0 answers
20 views

Azure SQL Column encryption and backups

I am starting a project and for this project I need to encrypt columns containing PII Data in an Azure SQL database. As a developer, I have often handled this step in my code, but moving forward I ...
user avatar
1 vote
1 answer
43 views

What facilities of SQLServer would help achieve a goal of tracking a hierarchy over time?

I've a requirement to create a system that tracks what is essentially a hierarchy of containers over a period of time, modelling a supply chain. Everything in the chain is a container, so the process ...
user avatar
2 votes
2 answers
275 views

Why dbcc's shrinkdatabase and shrinkfile aren't working?

Ok, I got it. Shrinking your database is wrong. You hate it. But let me explain. I have a 1TB P1 Azure SQL Database in production with ~50 tables, where ~5 of them are JSON containers. This was the ...
user avatar
0 votes
1 answer
32 views

Database Backup/Restore exists in Azure Vs Traditional on-prem SQL Server database?

Just wondering if someone can fill me in on how Azure Native database works in terms of database backup and restore. I cannot see a way to have the DBA options to do a backup and restore using a GUI, ...
user avatar
0 votes
1 answer
73 views

SqlPackage Import - How to chose vCore purchase model?

I read the following documentation. Properties specific to the Import action Looking at the switch description for DatabaseEdition, I do not see a choice for vCore Azure SQL Database purchase model. ...
user avatar
  • 12.3k
0 votes
1 answer
55 views

How can I prompt SQL server to use more suitable index in query?

I have a query that somtimes runs longer than the application calling it allows. I've traced it down a clustered index seek on rather larger table. I've created a new index, that better supports this ...
user avatar
0 votes
1 answer
46 views

understanding Azure Sql Databse Billed VCores

trying to migrate from sql server on-prem to azure sql database and i cant understand Duration and CPU Used and Memory Used of Billed vCores
user avatar
0 votes
0 answers
61 views

Error when replicating to Azure SQL Database

I've configured transactional replication between my on-prem SQL 2017 instance and an Azure SQL Database. The publisher, distributor, subscriber have all been set up. The snapshot agent has generated ...
user avatar
2 votes
1 answer
55 views

Azure SQL Database - What is the setting for 'cost threshold for parallelism'

In SQL server the default setting for cost threshold for parallelism is 5. I know that this setting can't be modified in Azure SQL server (as mentioned here). My question is whether Azure SQL server ...
user avatar
  • 59

1
2 3 4 5
17