82

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,000 databases. You cannot use SQL Server Management Studio to reliably locate a database in the Object Explorer. Backups are a nightmare, since for ...


19

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 my thoughts on the matter. My case for why you should use 1 Database for all clients. Pros Easy maintenance. Having one DB means that you only have to do your ...


17

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 whether it would be substantial, would depend on a myriad of factors. I would go with the one database unless there is a good reason to split it out to 10,000 ...


15

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 unfortunate) that CHECK constraints aren’t honored by MySQL. In MySQL, you have to use triggers instead. Another option is to switch to MariaDB.


8

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 and if you just plug "multi-tenant architecture" into Google, you will find a wealth of resources and discussion about it, entire books have been written on it. ...


8

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-attached clustering/cluster at all is used) and what type of pipe you'll need, region and latency, load balancer setup, etc., but it's not a hard requirements to have ...


7

Is the TCP port SQL Server is listening on open globally? If so, yes I'd be concerned. If there is a password that can be brute-forced or guessed, or an exploit that allows someone to bypass authentication, eventually your database could be compromised. You'll also be vulnerable to attacks that don't require access, such as someone filling up the drive that ...


7

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 particular point in time). If they are all in one database, this becomes much harder (and much more prone to error as it would likely need to be done via INSERT/...


6

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 countervailing points in favor of the sharded architecture, and addressing some of the concerns that other people have mentioned. Motivation for sharding As mentioned in ...


6

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 SSD scratch drive is now available out-of-the-box for the SQL Server on Azure VM, along with separate storage pools for data and log. Here's a screenshot of the ...


5

Download the Google Drive desktop application. When backing up SQL Server, set the destination to the "My Drive" folder created by Google Drive (something like C:\Users\<user>\Google Drive by default) Once a backup is placed in that folder, it will automatically start to sync to Google Drive. Also worth nothing, as per Shawn Melton's comment: You ...


5

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, creates indexes, and automatically verifies that performance of queries has improved. You can view these settings here: You mentioned that you are on SQL ...


4

wBob is right on the money, you need a service restart. I wanted to enhance his answer and give a script that will also verify that IFI is indeed enabled. This will determine whether or not you need to kick the service to allow the local policy to take. USE MASTER; SET NOCOUNT ON -- *** WARNING: Undocumented commands used in this script !!! *** -- --...


4

In my extensive testing of cloud SQL on cloud servers (rackspace cloud, to be specific), I found that splitting the ldf and mdf to distinct block storage volumes made a significant improvement in performance. I achieved really hot performance by putting the ldf on an SSD based block storage volume and the mdf on a standard block storage volume. Ultimately, ...


4

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 columns. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For example, ...


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 ...


4

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 storage, with 172GB reserved for disk caching. The IOPS are similiarly shared between the Temp storage (D:) and the disk cache. The total IOPS is 16,000, but your ...


3

basic things to check: new firewall inbound rule inside the VM enabling sqlservr.exe. You can make it as restrictive as you want but start with allowing all inbound connections first to verify it works then lockdown one thing at a time new firewall inbound rule inside the VM enabling sqlbrowser.exe. Needed if running named instance. new endpoint for the ...


3

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://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql -- Elastic Database query only: a shard map manager as data source -- (only on Azure SQL Database v12 ...


3

Couple of points of clarification. Nodes and replicas are not the same thing. Nodes refer to the Windows Failover cluster and replicas to AvailabilityGroup instances. So assuming you are running a 2 node WSFC, each hosting a SQL instance in an AvailabilityGroup, then yes, you will need another server to achieve quorum and avoid split brain behavior. The ...


3

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 script for Capturing wait statistics for a period of time, or sp_BlitzFirst from the open source First Responder's Kit. Or you could just poll sp_WhoIsActive ...


2

Depends on the cloud environment you're using but typically it makes sense to put them on the same logical drive. If you need additional IOPS you can stripe across multiple volumes but still present a single drive. In a cloud environment you are not the sole consumer of the storage you are allocated. You get a small slice of a very large pie and as such all ...


2

Yes, all the storage that you can attach to a single Azure VM is 16TB so that's the practical limit on storage for a single server. Now that said, there isn't a problem that can't be worked around. If you needed say a 100TB database, you could spin up 7 VMs, attach 16TB of storage to each one. Then spin up an 8th VM which has SQL Server on it, then create ...


2

That does not matter. When you upgrade an existing instance with a database attached SQL Server automatically upgrades the database too. If you attach the database after the upgrade, it goes through the same process. In either case make sure that the database is at the right compatibility level. While usually nothing happens, you might run into problems if ...


2

For what is worth, I ended changing the VM in Azure from type A to type D and then attaching another disk and moving the TEMPDB to that disk. So my final VM now is a D2 Standard with 7 GB RAM and three data disks, one for the MDF files, another one for LDF files and the new TEMPDB disk. I gave up trying to understand with the A3 some things you mentioned ...


2

Did you restart the service? Instant file initialisation only takes effect after a SQL Server service restart.


2

Azure IaaS is a different animal than traditional on-premise servers (virtual with a SAN or purely physical). You will get at best 500 IOPS per drive attached. General recommendation is to use storage pools to stripe multiple drives to increase throughput. With only four drives available on a D series, I would do the following. Put TempDB on the D:\, ...


2

Web edition looks like Standard edition except "It is only available through an SPLA" going by the specs listed in the article you linked to. With regard to a 2Gb limit, are you thinking about Express edition (or MSDE as it was called pre 2005)? That has had a per database size limit of 10Gb since SQL2008R2 (it was 4Gb in 2008 and 2005 and 2Gb in 2000 & ...


2

I do not believe your extracted quote is relevant to your problem. Your issue could be that your listener has multiple IP addresses. Under the default configuration, the listener will have multiple A records in DNS and your client will cache only one of them. Each time the cache expires it will randomly grab one of the IP addresses and sometimes it will get ...


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