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. ...
46
votes
5answers
50k 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
544 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
110 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
3answers
2k 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
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
1answer
1k 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
965 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
143 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
9k 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 ...
19
votes
5answers
39k 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
516 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
9k 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 ...
13
votes
2answers
2k 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
2k 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
10k 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 ...
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="...
4
votes
2answers
867 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 ...
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 ...
3
votes
2answers
395 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
1answer
2k 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 ...
1
vote
2answers
827 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 ...
23
votes
1answer
2k views

Where do this Constant Scan and Left Outer Join come from in a trivial SELECT query plan?

I have this table: CREATE TABLE [dbo].[Accounts] ( [AccountId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(), -- WHATEVER other columns ); GO CREATE UNIQUE CLUSTERED INDEX [AccountsIndex] ...
16
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 ...
5
votes
2answers
3k views

Linked Server to Azure Error: "Access to the remote server is denied because no login-mapping exists."

I'm setting up a linked server from an on-prem SQL Server to a database on MS Azure (see below). When non-admin users query the Azure database via the linked server they get: Msg 7416, Level 16, ...
4
votes
1answer
1k 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 ...
4
votes
4answers
492 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\...
2
votes
3answers
3k 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
790 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 ...
2
votes
1answer
129 views

Azure SQL Database - Modify cost threshold for parallelism

I attempted to update the value of cost threshold for parallelism but since my database is an Azure SQL database I can't run the command: EXEC sp_configure 'cost threshold for parallelism', 40 ; GO ...
2
votes
3answers
399 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 ...
2
votes
1answer
2k 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 ...
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 ...
1
vote
1answer
658 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
106 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 ...
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 ,...
-1
votes
2answers
792 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
470 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 ...