Hot answers tagged

11

What you're basically trying to do is measure storage speeds in the cloud. Unfortunately, that's not necessarily repeatable or reliable - it can suffer from noisy neighbors, other people doing development on the same database, varies with your storage type, etc. Rather than clearing the buffers or measuring query runtime, try starting with measuring logical ...


6

From BOL : The AS COPY OF argument does not support the fully qualified unique domain names. In other words, if your server's fully qualified domain name is serverName.database.windows.net, use only serverName during database copy. So your command should be CREATE DATABASE Database1_copy AS COPY OF oldserver.olddatabasenamehere;


6

The two managed types of SQL Server in Azure (SQL Database and Managed Instance) are version 12.0.2000.8. Don't be fooled by the low version number, it is always higher than on-prem meaning you can never restore an "Azure SQL" backup to on-prem. You cannot perform a backup from SQL Database, but you can do a copy only backup from Managed Instance. ...


5

In general, FCI is more beneficial compared to AG because of various reasons already mentioned above. Of course, 2-node FCI is completely great but should not be built on top of storage spaces direct since S2D is known to be problematic in less than 4 nodes deployments (source). For Azure, I would rather stick to a VSAN that is designed to work in 2 or 3 ...


5

Please read: Enhanced Azure Security for sending Emails – November 2017 Update which outlines the recommendations, options and restrictions for sending emails in Azure. In particular: For Pay-As-You-Go or Microsoft Partner Network subscriptions created after November 15, 2017, there will be technical restrictions blocking e-mail sent directly from ...


5

It really does come down to what you need and why you need it. Managed Instance has a single, huge, strength over Azure SQL Database, cross-database queries. If you need to query between databases and you don't want to rewrite the code, then Managed Instance is the way to go. There are some other advantages such as having SQL Agent (easily replaced with ...


5

Shared Disks are currently in preview. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disks-shared But if you have no choice but to run old, unsupported software, you shouldn't attempt anything as complicated to implement and operate as cluster. Just install the software on a single VM with premium disks, and live with the 99.9% uptime SLA ...


4

When provisioning users from external Azure Active Directory instances that are federated with your Azure subscription, you need to use the underlying "guest" email address created for the Azure subscription, not the "actual" email address. i.e. The Microsoft scenario mentioned here as "Imported members from other Azure AD’s who are native or federated ...


4

TL;DR: It wasn't finished importing yet. I found out - it was impatience. As soon as I could access the DB and see the structure I thought the import process was done. But it turned out it was still importing. So good to know that you can access it before it's ready. (In hindsight there was sort of an indicator, while it was still importing it showed 1 ...


4

I think the key to understanding that sentence is what I have marked in bold: Restore a database to another geographical region. This allows you to recover from a geographic disaster when you cannot access your server and database. It creates a new database in any existing server anywhere in the world. See the documentation on Geo restore: Geo-...


4

My Question is that in the Long Run and from management and scalability point of view which option is better For "management and scalability" Azure SQL Database is clearly better. Azure SQL Database was designed for this scenario. You can spread out your databases across multiple pools, as needed, or move specific databases to non-pooled DTUs. And Azure ...


4

It depends on every client's requirement and need as to which is a better option out of the two (options 1 and 2) for HA purposes. Various criteria that is generally checked is provided below: If it's a standard edition then you'll need to go with FCI (Option 1). Go with AG (Synchronous - Option 2), if all of the below conditions satisfy: Have Enterprise ...


4

We have been using the approach in your posted link since first reading about it in Sept 2017. Here is a link with comments from MS SQL Architect Conor Cunningham about the need to re-index in Azure SQL Database Link to Conor Cunningham comments.


4

To stop Windows users from logging in, don't create logins for them in SQL Server. By default, just because someone has a Windows login doesn't mean they have permissions in SQL Server. You have to use the CREATE LOGIN command to map Windows users to SQL Server permissions. If you don't do that, they can't log in. Your organization may also be asking for ...


4

You need to ensure the login is added on all the levels ---- ON SERVER LEVEL CREATE LOGIN TestUser WITH PASSWORD = 'ThisIsAStrongPassword!' GO ---- ON Master Level CREATE USER TestUser FOR LOGIN TestUser WITH DEFAULT_SCHEMA = dbo ---- ON SQL DB LEVEL CREATE USER TestUser FOR LOGIN TestUser WITH DEFAULT_SCHEMA = dbo GO GRANT SELECT ON ...


4

If auto update statistics is not enough for your DB, try a couple of things: Manually update statistics - recommend Ola Hallengren's scripts that can be configured to maintain indexes and/ or statistics as required. Test different parameters (all/ indexes only/ sample rate etc) and test running daily/ weekly/ at start of proc and the like, and see what ...


4

Check out the Docs on Hyperscale: Hyperscale only supports non persistent In-Memory objects (table types, native SPs and functions). Persistent In-Memory tables and other objects must be dropped and recreated as non-In-Memory objects before migrating a database to the Hyperscale service tier. As for rollback, it depends what you mean. Can it ...


4

This is an issue with the picker for selecting accounts when it cannot connect to the SMB port (or if NetBIOS isn't enabled, but you state that it works if you disable the firewall). You can either try to open port 445 on the firewall or create the login using T-SQL. There used to be a Microsoft KB Article for older versions of Windows but I believe this ...


4

Database Mail is available on SQL Server on Azure IaaS and my suggestion is to configure it using SendGrid email delivery service (available on Azure Marketplace) that supplies your virtual machine with an SMTP relay. Leverage this service to setup database mail for your alerting or reporting needs. Here you will find how to setup the SendGrid service on ...


3

You can use AZCopy to move files from blob to Filesystems or eitherwise.. Below is the syntax.. AzCopy /Source:https://myaccount.blob.core.windows.net/mycontainer /Dest:C:\myfolder /SourceKey:key /Pattern:a /S use the pattern option to specify name (this will work like SQL like query) From SQLServer 2014,you can also restore from Blob directly.. ...


3

This has a scent of a parameter sniffing problem. See what I did there? Punny, right? Anyway, Paul White, wrote up a great article on the various approaches available to resolve this issue, so I'm not going to go into detail when you should just read his article instead. What you can try quickly though, because this is an ad-hoc statement, is to force a ...


3

A Microsoft Engineer examined the logs you shared with us and found the following error: Dma.exe Information: 0 : 'Exception type: System.IO.IOException Message: There is not enough space on the disk. It seems like the workstation that DMA is running on doesn’t have enough disk space to do schema extract. The following is the ...


3

In general most DB as a service providers make it difficult to do replication to/from them to your own box so replication, at least native postgres replication, likely won't work. That leaves you with going through replication solutions that are not so tighly integrated such as Slony, Bucardo, pgpool. My advice would be to first describe your needs and ...


3

Notes There are several things to go over here: Just to get this out of the way: gz[ip] and 7zip.exe have nothing to do with any of this. Compression does not alter encoding or any of the original bytes (else compression / decompression wouldn't be reliable). Well, theoretically there can be a bug in one of them such that the decompressed output is somehow ...


3

This is not something you should ever see on an Azure SQL Database. You should engage support on this. There are a couple things you could do yourself, if you want. First just try to CHECKPOINT the database. Second if you are sure you have no active transactions, change the database SLO. Taking it out of the Elastic Pool, then putting it back in.


3

AWS RDS has multiple options in a default parameter group that are calculated from DBInstanceClassMemory variable, for example max_connections parameter which might be {DBInstanceClassMemory/31457280}, which for 8GB server would calculate to 8*1024*1024*1024/31457280=273. This allows the parameters to scale up and down while changing the database instance ...


3

Permissions for keys are at the vault level. In order to get the granularity that you want, you would have to create another key vault. From the documentation (emphasis added): Key vault access policies grant permissions to keys, secrets and certificates separately. For example, you can give a user access to only keys, but no permissions for secrets. ...


3

Have you considered using the Resumable Online Index Rebuild feature so you can schedule X executions of one minute of those indexes? ALTER INDEX [ix_CustomerIDs] ON [ContosoSales].[ConstosoTransactionData] REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1 MINUTES); GO ALTER INDEX ix_CustomerIDs ON [ContosoSales].[ConstosoTransactionData] PAUSE ...


3

In case someone runs into a similar problem... After 1.5 hours on the phone with MSFT, and reinstalling both SSMS and VS with no resolution. I reformatted my machine, reinstalled, and now everything works the way it should. I don't know what got into my system but now it's gone. Not a great solution, but that's what worked. UPDATE: When the problem ...


3

Ola Hallengren's maintenance solution has several parts: Database backups - you don't usually want to use these in Managed Instances because you can't restore the backups to another location, like on-premises. The only use for database backups is to restore them into another managed instance - like if you're doing it for disaster recovery, backing them up ...


Only top voted, non community-wiki answers of a minimum length are eligible