[dba-SQLServer] Advantages to separate databases

jwcolby jwcolby at colbyconsulting.com
Thu Jul 19 15:00:07 CDT 2007


OK.  Thanks for that.  Now, do I get any processing power from the remote
server if the two tables are on separate machines?  I often run queries on
my laptop.  I build a database (if none exists) into which I build views
(just for ease of use and storage of the SQL).  I currently have all of the
databases on a single machine but I have considered trying to place various
databases on different servers simply to see if I could get each server to
perform some part of the processing of the query.  Distribution of
processing load. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Thursday, July 19, 2007 3:53 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Advantages to separate databases

If you set up the database to be on two different machines or instances of
SQL server you will need to use linked servers and will need to reference
objects in this manner...

Server.database.dbo.object

You won't be able to use nolock hints when you use linked servers. If you
use separate databases on the same SQL server instance then you can
reference objects in this manner...

Database.dbo.object

You will be able to use nolock hints in your queries, sprocs, udfs, etc...


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, July 19, 2007 12:16 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Advantages to separate databases

I have a situation where I import tens of millions of records of raw data
(as I call it) into SQL Server.  I turn right around and send the name /
address out for processing and when I get that back I import that back into
SQL Server in a separate table.  Is there any advantage to bringing that
second table (addresses) back in to the same database that I have the raw
data in?

The way I use the data is that I add a PK long autoincrement to the raw data
table and export that with the name / address.  Thus when the data comes
back in from address processing each record coming back has a PKID that
matches a record in the raw data table.  When I build queries I often do a
join on PKID between raw and address and then select fields from both.  

It appears to me that having them in separate database files is actually an
advantage in many ways.  For one, the data in the raw data file can be
archived once and never touched again as far as backup is concerned.  The
data in that table never changes.  Also it seems that I might be able to
place the two files (raw and address) on separate machines and get some
parallelism going in processing where clauses etc.  It may in fact be
exactly the opposite and I would NOT want to set the two databases on
separate machines though, I am certainly no expert on anything SQL Server
related.

Any thoughts on the pros and cons here?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list