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.

KenJ avatar image KenJ commented ·
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 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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 ·
Slick84 avatar image Slick84 commented ·
Okay, should have Googled first. I can install 16 instances of SQL express on a single machine. Even nicer.
2 Likes 2 ·
Slick84 avatar image Slick84 commented ·
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 ·
Oleg avatar image Oleg commented ·
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 ·
Slick84 avatar image Slick84 commented ·
KenJ, its definitely a database limit. I'm not sure but i'll try to de-attach and re-attach my other DB's to instance 1 and see if it lets me do that. I'm at 3 instances at this point & growing so it would definitely be nice just to have all the DB's in one instance.
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
The other question is what is your purpose? If you are doing this for testing/evaluation/development purposes it may be worth your money to buy the developper edition. It runs around $50 roughly and is much more full featured. If this is production and you cannot afford SQL Server Standard edition (well worth it for production), you may want to consider MySQL though I hesitate to suggest that since I find SQL Server to generally be more user friendly and easier to administer.
0 Likes 0 ·
Slick84 avatar image Slick84 commented ·
I downloaded MySQL and it's a bit different. I didn't like it but yeah it would be nice if I was a little well versed with it to use that instead. This SQL Express is actually believe it or not being used for full-blown production reporting with a lot of vbscript hacks to import data automatically from our actually (REAL production OLTP system) which has MLC based fusion-io cards, 12 processors with hyperthreading, 64 gigs of ram and all the good stuff. We unfortunately couldnt get a license approval for this.....this is supposed to be an interim situation until EDW can be setup , etc. Typical corporate stuff..
0 Likes 0 ·

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.