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
25
votes
1answer
16k views

How can I alter an existing Primary Key on SQL Azure?

I want to modify an existing primary key on a SQL Azure table. It currently has one column, and I want to add another. Now, on SQL Server 2008 this was a piece of cake, just did it in SSMS, poof. ...
44
votes
5answers
48k views

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
2
votes
2answers
539 views

Determine whether there is any blocking while deleting large number of records [closed]

I would like to know whether there is any way to determine why it is taking too much time say 3 minutes for deleting 50K records from a database hosted in Sql Azure. I have clustered index on primary ...
6
votes
1answer
109 views

Why is DistinctSort followed by TopNSort in my query plan?

I have the following definition: CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, [ItemState] INT NOT NULL, [ItemCreationTime] DATETIME ...
4
votes
1answer
3k views

Linked server to SQL Azure very slow

I can use the Management studio to connect directly to my Azure SQL database and execute this query: select * from DB.dbo.DSeries where instrument='name1' and it works really fast. But if I create ...
2
votes
1answer
1k views

Multi-tenant database

We are building a database for a SaaS web application. We have been told to use a multi-tenant approach and to expect over 100,000 tenants to be using the system over the next 5 years, so we're ...
2
votes
3answers
1k views

Undo SQL UPDATE Azure DB

I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table. I've read around and found ApexSQL Log ...
2
votes
1answer
986 views

Migrate on-premise SQL Server databases to Azure SQL Database

We're in the process of a server migration from an on-premise server (Win2008R2) to Azure PaaS. To move the databases, we used the Microsoft Data Migration Assistant (DMA) tool, which worked great ...
3
votes
1answer
919 views

Azure Database Aliases

I saw that you could use the new portal to create an alias for your database instance. I have already created an internal DNS to point to our Azure SQL instance but of course connecting to it wants ...
1
vote
1answer
110 views

sys.system_internals_partition_columns view on Azure SQL

The sys.system_internals_partition_columns view on Azure SQL doesn't seem to exist. Is there another view I can use to examine index modifications on a per column basis against an Azure SQL DB?
9
votes
4answers
8k views

How do DTUs in Standard and Premium performance tiers in SQL Azure compare?

We recently observed a serious performance degradation in a SQL Azure database running in Standard3 performance tier - CPU utilization went from ten percent to fifty percent to almost one hundred ...
18
votes
4answers
35k views

Azure SQL Database “Login failed for user” in application, but works fine in SSMS

I wanted to try out the contained database users feature on Azure SQL Database V12, but I'm having a problem authenticating that seems odd to me. I created a database called Classifier. I added my ...
4
votes
1answer
5k views

Insert to SQL Azure through Linked Server very slow

I have a table on SQL Asure server with no indexes except clustered. And I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes. Although, when I do it ...
1
vote
0answers
455 views

SSIS: Error from ODBC provider when inserting into Always Encrypted table using CMK in Azure Key Store

I'm trying to use SSIS (Visual Studio 2017) to insert data into an Azure SQL table with a column that's encrypted via Always Encrypted, and a column master key stored in Azure Key Vault, but I keep ...
12
votes
1answer
8k views

Slow order by SQL Server

In my application, I have a query which performs a search in "files" table. The table "files" is partitioned by "f"."created" (see the table definition and has ~26 million rows for the client 19 ("f"...
12
votes
5answers
14k views

How do I copy my SQL Azure database to a local SQL Server instance?

I have an OLTP database hosted on a SQL Azure instance. I want to pull a copy of the database down from the cloud so I can run some heavy extracts and OLAP-style queries against it without impacting ...
2
votes
1answer
2k views

Index rebuild log growth

How much log index rebuild will generate.I remebered reading rebuild index should generate same amount of log file as table size.But my tests are showing otherwise.We need this estimate since we are ...
9
votes
2answers
962 views

Should I drop dbo from the database role db_owner?

I did a Vulnerability Assessment for my database today. I found VA2108 and it suggests me to ALTER ROLE [db_owner] DROP MEMBER [dbo] But it's the default setting of any SQL instance. Should I go for ...
7
votes
1answer
1k views

SSIS OLE DB Source Editor Data Access Mode: “SQL command” vs “Table or view”

In BIDS, I'm running a Data Flow Task & I'm wondering what the difference is behind the scenes between using "Table or view" vs "SQL Command" for the Data Access Mode for an OLE DB Source. For ...
7
votes
2answers
3k views

Help understanding why a deadlock occurred on row level index lock

I have the following deadlock xml <deadlock> <victim-list> <victimProcess id="process3340d548c8" /> </victim-list> <process-list> <process id="...
7
votes
2answers
9k views

Will altering order of columns in GROUP BY affect performance?

I have a SQL query like this: SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D) FROM SomeTable GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END) On a huge dataset the actual execution plan ...
4
votes
2answers
8k views

Dramatic decrease in SQL Azure performance after scaling to new edition

Yesterday, we scaled one of our databases in Azure from the old Business edition to the new Standard one (S2). Since then, it has been behaving rather erratically. We have a relatively simple table ...
4
votes
2answers
797 views

Is there any way to detect and get information on a push-subscription on subscriber's side?

I've got a database in Azure SQL. There may be a push-subscription from on-premises database. I have no access to on-premises servers where distribution/publication/subscription is created and ...
3
votes
2answers
283 views

ALTER TABLE command with no default value taking forever?

I'm attempting to add a NOT NULL constraint to a table I have (so I can use it as part of a primary key, so I can create a spatial index...) but it is taking a really long time - 1 hour 31 minutes and ...
1
vote
2answers
819 views

Bi-directional synchronization between local SQL Server 2005 and a SQL Azure using SQL Data Sync

I need to synchronize an Sql Server 2005 database in an SQL Azure database. In other posts I've read that I can do this using Sql Data Sync but I don't know if SQL Data Sync can make the ...
1
vote
1answer
1k views

How do I get Azure SQL query history/statistics/performance?

I'm migrating away from Azure SQL and my new cloud hosting service wants stats like "estimated inserts per hour on average", traffic/bandwidth usage per month, or "average read/writes on table x per ...
15
votes
2answers
2k views

Why is my index not being used in a SELECT TOP?

Here's the run-down: I'm doing a select query. Every column in the WHERE and ORDER BY clauses are in a single non-clustered index IX_MachineryId_DateRecorded, either as part of the key, or as INCLUDE ...
9
votes
3answers
6k views

“Deploy Database to SQL Azure” Task missing

I recently installed SQL Server 2014 Express on a workstation I have and am trying to deploy to SQL Azure. I've done this before on a previous workstation using SQL Server 2012 Express by doing the ...
6
votes
1answer
2k views

Generate an increment ID that is unique for a given value of a foreign key

Disclaimer: This question was asked on SO initially but did not get much traction there, so I am trying here, hoping it will be more interesting to DBAs... We are working on an ecommerce system where ...
4
votes
4answers
464 views

Migrating or exporting a database from azure to SQL Server 2016

I am playing around with SQL Azure and in my learning curve. I Have migrated a database from my on premises instance to the Azure server. Now my question is, how do I move a database from Azure to on ...
4
votes
2answers
4k views

Can't restore Azure bacpac into localdb with SqlPackage

I have made a backup of my SQL Azure in a bacpac file and when I try to import the bacpac in localdb it always fail. Here is the command I use: PS C:\Program Files (x86)\Microsoft SQL Server\110\DAC\...
3
votes
1answer
880 views

install FirstResponderKit in Azure SQL DB?

I've just downloaded the newest version (20190128) of FirstResponderKit from BrentOzar.com, and tried to install it on Azure SQL DB. I'm getting these errors: Msg 40515, Level 15, State 1, Procedure ...
2
votes
1answer
1k views

Azure SQL Data Migration Assistant is Stuck on “Migrate Data”

I am in the process of using the Data Migration Assistant to move a local DB to Aszure SQL. The schema transfer went well, and 243 of the 246 tables all were able to transfer their data with no ...
2
votes
2answers
2k views

Is it possible to log ship from on-premise SQL server to Azure SQL Managed Instance?

From the docs, it would seem that restoring a full database backup file is possible, but can one also restore transaction logs? (the idea is to minimize downtime in a migration from on-prem to MI). ...
2
votes
3answers
376 views

Why does PK constraint require a separate index when I already have a suitable unique clustered index?

This is part of table definition from here: CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, -- lots of other columns CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY ...
1
vote
1answer
377 views

Azure SQL Database and Database Maintenance

If SQL Agent isn't available for Azure SQL Database then is it possible to maintain (indexes, statistics etc) the databases with Ola Hallengren's maintenance scripts, for example?
1
vote
1answer
81 views

Table size in Azure SQL Managed Instance vs On Premise SQL Server

In doing a test, I have observed that the size of the same table with the same exact DDL (created from scratch) with the same exact data (10M rows) varies drastically in our Azure SQL Managed Instance ...
1
vote
0answers
41 views

Domain Alias for Azure Databases [duplicate]

I have 3 databases on Azure's cloud, all with domains names such as xxxxxxxx.database.windows.net. Would it be a bad idea to create domain name aliases for all 3, things such as: database.domain.com ...
1
vote
3answers
565 views

How do you get the default compatibility level in Azure SQL Database from T-SQL?

In on-prem SQL Server you can check the server version and multiply by 10 to get the maximum compatibility level supported by the server, or check the compatibility level of the model database to get ...
1
vote
1answer
1k views

Executing query against Azure SQL db using Linked server takes hours

I have a linked server to Azure SQL database and I am trying to run the below SQL query which takes hours to run and at one point, I then have to cancel the execution. I have researched everywhere and ...
0
votes
1answer
1k views

Determine databases specified Login has access to on SQL Azure

I need to obtain a list of all the databases a specific user has access to. To this end I am using the following ... USE master; GO EXECUTE AS LOGIN = N'Login-I-Want-To-Check'; GO SELECT name ,...
0
votes
1answer
89 views

Should I provide db_owner principal role to the user of the Azure SQL of my Azure Web App?

I have an Azure Web App connected to an Azure SQL database. Let's call the app ACME. The application has it's own Role and User called ACME, and I gave that User db_owner principal roles. The ...
-1
votes
2answers
601 views

SQL Server 2012 log shipping to Azure SQL

Is there any way to take SQL Server 2012 log shipping files and upload them into Azure SQL Database. We just need the data changes and not to bothered about the recovery side of things.
-2
votes
2answers
444 views

Problems with having SQL Server Identity Column in the Cloud

Our company is utilizing Identity(1,1) clustered primary index in SQL Server 2016 Enterprise, and will eventually deploy into the Cloud (Amazon AWS, Google Cloud). Is there any issues utilizing ...