question

Slick84 avatar image
Slick84 asked

Can I install multiple instances of SQL Server Express on my machine and create Linked Servers between them?

I have a desktop on which I have SQL Server 2008 r2 Express installed. I'm about to hit the 10gb limit on it and want to install another instance of SQL Server 2008 r2 Express on the same machine and create linked servers between them. Would that be possible? Thanks, S
sql-server-2008-r2linked-serversql-server-express
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Fatherjack avatar image
Fatherjack answered
You can create Linked Servers in SQL Express. How is that going to get over your database size limit?
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Maybe I'm misunderstanding, but the 10GB limit is per database not per instance. You should be able to create additional databases on the same express instance instead of creating additional instances. This would get rid of the linked server and multi-instance management overhead.
3 Likes 3 ·
He plans on breaking the database up and putting different tables on different express instances. You can do it, but it's going to become a management nightmare.
2 Likes 2 ·
Okay, should have Googled first. I can install 16 instances of SQL express on a single machine. Even nicer.
2 Likes 2 ·
Yeayy.. Awesome, thanks for the answer guys. Grant, you are right on. Additionally is there a limit of how many instance of SQL express can be installed on a single machine?
0 Likes 0 ·
And you don't really have to configure linked servers though they are the most convenient because all you have to do is use 4-part names in your queries. Without linked servers, you can still query data in other instances via openrowset, which will require you to provide the instance name in your connect string, but then use only 3-part names for your objects:
select 
    other.* --into dbo.boloney
    from openrowset
    (
        'SQLNCLI', -- provider
        'Server=.\InstanceName;Trusted_Connection=yes;'
        'select * from db_name.dbo.table_name;' -- 3-part
      ) other;
Using the dot in place of the box name is a real life saver, because if the box is renamed then the queries still run without the need to hunt them all down and make changes to reflect the box name change :)
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.