85
votes
Accepted
Is there any limit to the number of databases you can put on one SQL server?
I've worked on SQL Servers with 8 to 10 thousand databases on a single instance. It's not pretty.
Restarting the server can take as long as an hour or more. Think about the recovery process for 10,...
- 70.5k
19
votes
Is there any limit to the number of databases you can put on one SQL server?
So there are Pros and Cons to both methods. Without knowing more about your application or the services you're looking to provide I won't be able to give a definitive answer but I'll throw out some of ...
- 3,531
17
votes
Is there any limit to the number of databases you can put on one SQL server?
Maximum Capacity Specifications for SQL Server states that there is a limit of 32,767.
As for whether it will affect performance, the answer is yes, but the ways it will affect performance, and ...
- 7,984
15
votes
Accepted
Set column to only accept negative values
CREATE TABLE t ( n INTEGER NOT NULL CHECK (n < 0) );
works in most RDBMS I know.
Edit: A comment by @IMSop prompts me to specify why I wrote “most RDMS I know:” it is well known (and very ...
- 748
8
votes
Is there any limit to the number of databases you can put on one SQL server?
One of the downsides I can see to the single-database suggestion is to do with rolling back data - if you have a database per tenant set-up, you can restore each client's data independently (and to a ...
- 81
8
votes
Is there any limit to the number of databases you can put on one SQL server?
What you are talking about here is multi-tenant vs multi-instance architecture. I'm just bringing up these terms as you don't use them in your question but this is what you are discussing is called ...
- 303
8
votes
Accepted
Always On Primary Replica On Prem, Secondary Replica On Azure
We only want to have 2 replicas, one on-prem and one on azure. Is this possible?
Yes, it's possible. There are extra items you'll want to think about such as having a domain controller (if ad-...
- 35.6k
6
votes
Is there any limit to the number of databases you can put on one SQL server?
Thanks to all who answered - really appreciate the points you've given me to think about. The general feeling I got was that a single database is preferable, but I would like to add some ...
- 2,943
6
votes
Accepted
Azure IaaS VM - Use D:\ Drive for tempdb - NTFS Formatting 64k
Does anyone have anything better or see any holes in this process?
As for holes, the blog post contains several, but your script appears to plug them.
As for something better, TempDB on the local ...
- 47.6k
6
votes
Accepted
Database corruption on VM shutdown
As far as I know database corruption can happen if the machine restart in the middle of a transaction
No. Database corruption is only caused by faults in the storage system or bugs. SQL Server will ...
- 47.6k
6
votes
Does Azure VM backup also backup SQL DBs?
Is the Azure VM managed backup also backing up the SQL DBs?
Yes it does takes "disk level backup" but not SQL Server specific backup which would mimic one taken by TSQL or Maintenance plan. ...
- 19.1k
6
votes
Accepted
Why significantly worse Column Store performance on Read-only AG secondary DB?
My blog post below details an issue which can cause a considerable increase in total CPU and total elapsed time for a query against a CCI with deleted rows when in Snapshot Isolation or RCSI, compared ...
- 1,526
5
votes
Background Index Creation on SQL Server
This sounds a lot like the Automatic tuning features of Azure SQL Database. In particular, one of the options is:
CREATE INDEX - Identifies indexes that may improve performance of your workload, ...
- 29.8k
5
votes
Accepted
Is DB mail supported on SQL Server hosted on Azure VM (IaaS)?
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 ...
- 1,817
4
votes
Set column to only accept negative values
This can be achieved (at least in Sql Server) by using a CHECK constraint.
From the above post:
CHECK constraints enforce domain integrity by limiting the values that
are accepted by one or more ...
4
votes
Sql Server 2016 errror Incorrect syntax near 'EXTERNAL'
External data source type SHARD_MAP_MANAGER and RDBMS is only supported only on Azure SQL Database v12 or later. It is not supported on SQL Server 2016.
Ref:
https://learn.microsoft.com/en-us/sql/t-...
- 13.6k
4
votes
Accepted
Azure Virtual Machine - local temp storage (D: drive) - how much IOPS it can handle?
For VM Series with premium storage a portion of the local SSD is reserved for caching of premium disks. That's why the D3 has 200GB of Temp Storage, but the DS3 has only 28GB of available Temp ...
- 47.6k
3
votes
SQL Server CPU Issues
Rather than focusing on the hardware first, or the availability group even, it would be good to get a handle on what this "query" (test case) is waiting on.
To do that, you could run Paul Randal's ...
- 29.8k
3
votes
Accepted
SQL Server on azure virtual machines default instance
Is there a way to provision a SQL Server virtual machine on azure with a named instance or does it always install the default?
Not with the pre-built images.
If you provision your own VM and use IaaS ...
- 13.6k
3
votes
What is the benefit of multiple subnet (FCI/AG) in a virtualized setup?
In Azure the big benefit of this configuration is that you don't need an Azure Load Balancer.
"Simplify your deployment and eliminate the need for an Azure Load Balancer or distributed network ...
- 47.6k
2
votes
Accepted
Unable to access SSRS web URL outside Azure VM
Your URL is wrong and also you can configure the firewall. Try below URL. Configure port as describe below if you have not already.
http://jarvissqlvm.cloudapp.net/ReportServer/PageTo Connect to ...
- 13.6k
2
votes
Backup to Azure Blob Failing with Hallengren Framework
I have added support for BLOCKSIZE, and MAXTRANSFERSIZE, when backing up to Azure Blob Storage block blobs (using Shared Access Signature).
https://ola.hallengren.com/versions.html
- 1,415
2
votes
Accepted
SQL Server CPU Issues
OK, we've got to the bottom of it. There was an extended event, DYNPERF_AX_CONTEXTINFO, seems part of "Performance Analyzer for Dynamics". Disabling that event and the performance of this artificial ...
- 256
2
votes
Advice on diagnosing a "sometimes" slow query
I really don't think using the OPTION (RECOMPILE) is an effective way to eliminate the possibility of parameter sniffing.
Parameter sniffing happens when SQL is confused about a particular query and ...
- 877
2
votes
Accepted
Storage pool configuration for SQL Server
Is [using a single pool] applicable to Always-On or just applicable to shared storage FCI.
FCI's in Azure use storage spaces direct for disk replication, which I imagine is the source of this ...
- 47.6k
2
votes
Accepted
Azure VM disk expansion online
I believe the answer is you cannot. I tried doing so but you get below message on Azure portal which rules out the possibility
Disks can be resized or account type changed only when they are ...
- 19.1k
2
votes
Availability Group - Deleting old backup files
Since you mentioned this is in an AG, are you sure you're running the command and checking the backup folder on the same server?
If you are connecting to the Availability Group primary through the AG ...
- 29.8k
2
votes
Is DB mail supported on SQL Server hosted on Azure VM (IaaS)?
Yes It does with one restriction of SMTP Server. Please read these two articles to get around. Other than SMTP server I am not aware of any difference between on-premise and SQL Server on Azure Cloud.
...
- 13.6k
Only top scored, non community-wiki answers of a minimum length are eligible