Can you link database to another in SQL Server?

Question

I'm logging measurements into a database but with SQL Server 2012 Express, the database size is limited to 10 gb per database and its already full. If I create a new database, will I be able to link the two databases together giving me a 20 gb database?

If you use 2 database then while writing any query or doing any db operation you MIGHT have to do that in both db ! Ofcourse you can use multiple databases and query them or do any operation. But if you are in impression that adding 1 more database will increase the storage size of single database then you are wrong because they are 2 separate dbs — N Subedi, Nov 07 '19 at 17:05
No, you can't turn this into a single 20GB database. But you can have multiple databases. Of course if you are storing that much data then perhaps you need to consider using a paid version so you don't have these limitations. — Sean Lange, Nov 07 '19 at 17:09
If you are limited to use express db only and that's only the choice you have then, whenever your primary db get full migrate those data into another db and clear the primary db to make space and your app will continue using the same db without changing any existing codes/configs related to the db — N Subedi, Nov 07 '19 at 17:12
Don’t use SQL server to store logs. Choose a database built for that purpose. Example: elastic. — theMayer, Nov 07 '19 at 18:02

score 0 · Answer 1 · edited Jun 02 '21 at 07:44

There is a workaround that will work like this, though it isn't necessarily optimal. To do this, you will need to create a stored procedure for inserting your data, a stored procedure for updating your data (if needed), and a view for retrieving your data. In essence, you will be recreating SQL Server's partitioning functionality at a simplified level.

First create the number of databases you want to work with. For this example, let's use four databases called DB1, DB2, DB3, and DB4.

How your INSERT stored procedure will work depends on how fancy you will be making your code. I prefer something emulating the functionality of the actual partitioning logic, but you could create something that just inserts into the first non-full database.

For this example, let's create a SEQUENCE to control which database is inserted into. A SEQUENCE is similar to the idea of an IDENTITY column, but it is not tied to a table. To create a sequence, use code like this:

CREATE SEQUENCE MySequence as bigint
    START AT 1
    INCREMENT BY 1

To perform your insert, use the value of the sequence to determine which database will be inserted into. For this case, I will use the modulo division functionality in SQL Server, but you could divide it up by number ranges as well.

DECLARE @db int,
    @NewSeq bigint
SET @NewSeq = NEXT VALUE FOR dbo.MySequence
SET @db = (@NewSeq % 4) + 1  -- you'll see why in a moment

Using the % can be thought of as returning the remainder after integer division. So, if you calculate 14 % 4, you will get a value of 2, since four goes into 14 three times, leaving a remainder of two. For any integer, modulo division by four will return a value of zero, one, two, or three.

Now, you know which database to insert into. You could use a CASE statement and execute a different bit of code or stored procedure for each database to insert into, but I prefer to use dynamic SQL code to do this, like this:

DECLARE @SQL varchar(1000)
SET @SQL = 'INSERT INTO [DB' + Cast(@db as char(1)) + '].'[dbo].MyTable (a,b,c) VALUES (''' + <logtext> + ''', GETDATE(), 1)'

EXEC (@sql)

This should give you enough to get started with this idea.

Can you link database to another in SQL Server?

1 Answers1