Solved

LocalDB over 10GB

Posted on 2014-01-11
8
2,268 Views
Last Modified: 2014-01-17
I have a LocalDB database that is currently at 9GB, and the application will ultimately consume 20-30GB. Moving to Standard SQL is not an option.

My original plan was to move the largest table (5GB) into its own LocalDB database so that my Access 2010 front end talks to 2 LocalDB back ends. However, the 10GB limit is based on the size of the MDF file. Is it possible to move the big table into a secondary file group stored in an NDF file without having to split the database? If so, is there any limit to the total size of a LocalDB database with MDF file + NDF file(s), each <10GB?
0
Comment
Question by:chrisezard
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39773191
No that is not possible...
MS will not allow you to use the express edition to go more than 10 GB of database for free.

incase moving standard edition is not an option.. you can find the below alternatives

1) install MYSQL server (free edition) and migrate all your databases to it as well the code, i.e. the sp's and all..

2) install MYSQL server (free edition) and migrate all your data... keep your SQL server and inplace of the tables use Views in localDB... point these views to MYSQL server.... it might hamper your performance a little bit.. but it should be fine..
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39773870
Moving to Standard SQL is not an option.
What about the Web Edition?

However, the 10GB limit is based on the size of the MDF file.
Actually, I suspect you will find that the limit is based on the size of the database and not on the allocated size of the data file.  In other words, you should be able to create a file that is 100 GB, you just cannot use more than 10GB.
0
 
LVL 1

Author Comment

by:chrisezard
ID: 39774057
Surendra
Thanks for the MySQL suggestion, I had a look at that. For the time being I just want to get what I have working. However, it may be worth learning for future projects.

Anthony
Microsoft site not too helpful on the Web Edition.

@ the limit is based on the size of the database and not on the allocated size of the data file.
I want to know whether actual NDF file sizes are added to the actual MDF file size to see whether the total is >10GB. I know log files are excluded from the database size calculation.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39774068
Microsoft site not too helpful on the Web Edition.
I am not sure what that means, but I will take your word for it.

Again it does not have to do with the file size of either the mdf, ndf or whatever you decide to name it, but rather the actual size used.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39776538
AFAIK there are no restrictions on using synonyms in Express edition.

I think maybe you could move some tables to another db and then use synonyms to allow all tables to still be referenced/processed from the original db, but with them physically residing in another db..
0
 
LVL 1

Author Comment

by:chrisezard
ID: 39777852
@Synonyms
This looks really interesting - Synonyms are available in LocalDB.
I plan to move '5GB_Table' to a new database and call it '5GB_Table1', then create a Synonym called '5GB_Table' in the original database. That way no code in the original database needs to change. If, as is possible, I need to create a third database later on I'll use the same method. So my Access front-end will just point to the original back-end as if it held all the data. I'll need to test that everything works before closing the question, but thanks for the suggestion.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39778059
You're welcome.  That was the easiest, cleanest method I could think of :-) .
0
 
LVL 1

Author Comment

by:chrisezard
ID: 39789981
Sorry about the delay in closing the question - disk space problems held me up.

Yes, it works nicely - I'll add Synonyms to my armoury :)
Thanks for the help, Scott
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now