SQL Server Express Size Limit

[Isaac]

you can create multiple databases within a single instance of SQL Server Express and each of those databases can be up to 10GB in size. This is according to Microsoft's documentation which states: "Each instance of SQL Server Express can contain up to 10 databases with a total combined size of 10GB".

According to these very words, that's not true. "total combined size". not "total individual size" !

You're saying it's Black, then quoting someone who says it's White.
Total combined does not mean individual. It means total.

Now I'm confused. All I know is I never needed to study this because I never came anywhere near it.

 

[GPGeorge]

you can create multiple databases within a single instance of SQL Server Express and each of those databases can be up to 10GB in size. This is according to Microsoft's documentation which states: "Each instance of SQL Server Express can contain up to 10 databases with a total combined size of 10GB".

Please post the link to that document. I'd love to see the full statement in context.

 

[Galaxiom]

A single database can be up to 10 Gb, but you can have up to 32,767 databases per SQL instance.

This is correct. However it would be quite a machine to support that many databases.

The reality is the ability to support databases is limited by the maximum 4 processor cores and 1 GB of memory per instance.

 

[Minty]

This is correct. However it would be quite a machine to support that many databases.

 

[spaLOGICng]

Each database is subject to the 10GB limit.

If your data grows beyond that limit, there are options, including what you suggest, i.e. adding a second database. That would come with most of the same problems you encounter trying to split an accdb BE into multiples, such as loss of Referential Integrity between tables in the different databases.

I can't image most small to medium sized relational database applications growing that large, though. Probably more of a theoretical problem, IMO. On the other hand, if your data grows that dramatically, it probably also indicates a growing operation and one of the licensed versions of SQL Server might be a logical choice as well. There is a cost to those licenses, of course, but in the greater scheme of things, that cost might be worth incurring if you need to handle that data.

And, of course, there are other free databases, such as PostgreSQL which advertise no limits on size. You'd have to investigate them to decide if they would be viable alternatives for you.

On the other hand, it could also mean that data is not properly normalized. This is a bad habit for companies importing flat files, any file for the matter, and not normalizing it, leaving it as it.

 

[The_Doc_Man]

There IS such a thing as making an archival DB so that your older data gracefully "ages out" after a couple of years. Your Access database BE still has its 2 GB limit but Access also allows up to 15 simultaneously open databases (in addition to the FE, which counts as 1.) But you can do something like open and close databases for archiving, and can use date-based queries to move data from your main DB to an archiving DB. And there is nothing wrong with keeping a small table of database names and date ranges to tell you in which file a specific archive date range exists.

 

[tmyers]

The data does need cleaned up as I am currently just doing a data dump and uploaded that file to the database. I need to go through and normalize it for both size and performance.

 

[Isaac]

I'm more on the side of "I can't imagine the size limit being much of an issue", with the notable exception Galaxiom pointed out about hardware (etc) capabilities.

After working in many projects on corporate data warehouses, it's a good reminder that - even though many people consider all their tables to be 'related' inside one specific project-based Access database, the same is not true for an entire business.

Out of thousands of databases and thousands of tables in each database where I currently work, it's helpful to point out that any given table may only be "truly related" to a handful of other tables. I'm thinking of a number of major customer data tables, there are only a few dozen that are hard related to each other in such a constraint-based way that they must be in the same db.

Most of the rest could be separated into different databases (and often are), and it would make no difference at all - any query can refer to any other database's tables on the same server.

Thus, while you may need to keep 10 or 30 tables in the same DB "because they are relational", that has nothing to do with another batch of 10 or 30 tables that 'are also relational', but not related to the first batch, and thus can easily be in a different database.

I wanted to point this out since although we often talk about all tables being relational, that's more applicable to the small, project- or theme-based Access databases that are a focus of AWF. Well, I mean, yes they're relational, but only to x-number of other tables at any given time.

 

[The_Doc_Man]

Isaac, that is true. Often, the trick is deconvoluting those 10 to 30 tables, untangling them from the numerous other tables that you never touch in your project. A true case of "forest vs. trees" whenever someone allows co-mingling of many projects' tables into a single database instance. Which is, in a way, why Access and its 2 GB limit are actually useful - to prevent you from mingling too many things together at once.