Distributed databases?

Hi everybody.

I have a question about the database performance.

Suppose we have 15 tables distributed to 3 databases. So if we want to to load some related data from diffrent tables of diffrent databases, we have to make more connections to the other databases, and it takes some time to establish the connection. but if we use 1 database with 15 tables, then we wont have this problem and we wont make more connections. but using 1 database, will grow the size of database file (*.mdf) and sure this couses to take more time of file operation like record-seek-time, record-insertion-time and etc.

which of these solutions have better performance? Distributed databases or single one?

Thanks

Even if you put them all in one database, you dont have to put them all in the same mdf file, or even on the same drive, and then even as the tables grow very large, you can partition them, which further helps the optimization.

The driving factor though, for me, is logical separation.

Are these tables all part of the same application? In this case I would put them in the same database. Are some of them shared between applications? In this case, I would put the shared tables in a database, and the application specific tables in another.|||Are you talking about "Databases" on the same server, or different servers?

Databases on the same server would not require multiple connections to be made for each database, you connect to the server, not the database.

|||

The main reason for having multiple databases on different machines would be if you are trying to scale out instead of scale up on a single server. Unless a single server cannot handle the workload, you probably don't need/want to scale out yet.

The size of the data file(s) in a single database does not have nearly as much impact on performance as other factors like having the proper indexes in place for your workload.

|||

Hi, thanks for the replyes.

I can seperate my data in 3 sets. first, the data which are stattic, like the list of countries, second, the memberships data of the members, like member's general information, membership information and etc. and third, the data of the members' posts.

the 1st set, is static, so never (or seldom) grows. but the 2nd and 3rd are dynamic and would grow alot.

data of 2nd and 3rd sets can be related, but also can not. I mean, as you know, the posts of the members are related to the members, but they are POSTS, so it may be logical to use a seperate database for them and a seperate database for members.

but from another view, they are related, they are the posts of the members! so it would be logical again, to put both the sets in one database.

and by this time, i'm going to use just one server, not many servers (may be in future).

and I dont know, putting all the data in one database, would reduce the performance of my database (and so my application) or not.

by the way, the data of the members, will be used by seperate applications, but the posts are for a single application. infact all of my applications will use the membership information, but the each of these, have their own application-specific data.

Distributed databases in a cluster

Hello gurus!
I know that SQL 2000 and very probably Yukon SQL do not support a shared
disk cluster.
So, in the shared nothing schema is possible to (in a Active/Active):
having one SQL 2000 instance running in the Active1 Server with on Database
(like pubs) and in the Active2 Server another database (like northwind)
I want that the virtual Server redirect the query automatically to one
instance or to another and in case of failure of the Active1 Server, the
Avtive2 take the Active1 works and in case of failure of the Active2 Server,
the Active1 take the work of the Active2

Thanks a lot again
AA
The concept of data partitioning across servers is attractive on teh
surface, but has a lot of complexities. SQL 2000 has a distributed
partitioned view feature that applies this technology in a limited fashion.
SQL generally scales up, not out. If you want a faster system, purchase a
bigger box. There are very few real-world applications that cannot be done
with scale-up, even with current technology.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AA" <aa@.personal.net.py> wrote in message
news:eXm5cOXIEHA.2480@.tk2msftngp13.phx.gbl...
> Hello gurus!
> I know that SQL 2000 and very probably Yukon SQL do not support a shared
> disk cluster.
> So, in the shared nothing schema is possible to (in a Active/Active):
> having one SQL 2000 instance running in the Active1 Server with on
Database
> (like pubs) and in the Active2 Server another database (like northwind)
> I want that the virtual Server redirect the query automatically to one
> instance or to another and in case of failure of the Active1 Server, the
> Avtive2 take the Active1 works and in case of failure of the Active2
Server,
> the Active1 take the work of the Active2
>
> Thanks a lot again
>
> AA
>
|||Actually, I'd disagree on the scale-up part. There are apps that would
bring even the largest commerically available servers to their knees if you
tried to run all of the transactions on a single machine.
The biggest part in here is that there is no technology inside SQL Server to
allow a transaction to automatically be continued on another node in the
event of a failover. This is possible, but it has to be coded into your
applications.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I did qualify the statement about scale up. There are some environments
that simply cannot be scaled up to given current technology. However, I
consider those to be the exception, not the rule.
I also agree on the transaction failover issue. Smarter client tools and
faster failover on large systems will help, but scale out will need to be
addressed at some point. When and how is probably a marketing decision.
Unfortunately, none of those will help the original poster now. For his
application, he needs to start thinking about scale up if he wants to use
the current tool kit.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:OBcGddeIEHA.3276@.TK2MSFTNGP09.phx.gbl...
> Actually, I'd disagree on the scale-up part. There are apps that would
> bring even the largest commerically available servers to their knees if
you
> tried to run all of the transactions on a single machine.
> The biggest part in here is that there is no technology inside SQL Server
to
> allow a transaction to automatically be continued on another node in the
> event of a failover. This is possible, but it has to be coded into your
> applications.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
sql

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
ReshmaHi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/d...r />
_2z4x.asp
http://www.microsoft.com/sql/evalua...es/distpart.asp
http://msdn.microsoft.com/library/d...r />
_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
ReshmaHi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/d...r />
_2z4x.asp
http://www.microsoft.com/sql/evalua...es/distpart.asp
http://msdn.microsoft.com/library/d...r />
_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
Reshma
Hi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/de...qd_10_2z4x.asp
http://www.microsoft.com/sql/evaluat...s/distpart.asp
http://msdn.microsoft.com/library/de...qd_12_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

Distributed data synchronization - Comparing timestamp

Hello,

I am designing a distributed application where a central SQL Server 2005 database will need to be synchronized with remote SQLExpress databases via a WebService. Data can be edited at the cental db (by our connected applications) or on the local SQLExpress dbs running on the users machines (by this disconnected application).

Now, how can I use the timestamp column to determine the most recent update. The most recent update to me is not the user that most recently invoked the syncing WebService, but the most recent time when the change was made to the data locally vs the change time at the central server. Because a user could make a change on his laptop on Monday but not synchronize it till Friday. I dont want the Mon data to overwrite data of Tues-Fri being overwritten simply based on assumption that a late sync is infact the most recent change.

The initial data will be downloaded to the local SQLExpress db via the WebService so the timestamp data in the table will come along with it.

When a user modifies data in this disconnected SQLExpress db, can that be compared to modifications on the central db using timestamps to determine if the users data being synced is older or newer than the data on the server? I understand timestamps are incremental values, but are they still sensitive to the users timezone since the central server will be a diff timezone then the user.

Timestamps are instance-level unique numbers. So you can't really compare those across instances. Why don't you use merge replication for instance? It is designed to solve problems like these. If you assistance on the replicaiton part you should post in the replication forum.|||

For ISVs that are building installable components, replication isn't too attractive. The components are meant to be quickly deployable whereas replication requires configuration to be done to create the publishers, distributers, etc.

So, basically, I would have to write a home-grown solution for conflict resolution?

Does anyone know how Microsoft is handling conflict resolution for the remote synchronization in its Project Elixir, which I believe also synchonizes disconnected data caches using webservices?

|||Then you will have to use a combination of binary_checksum or checksum along with comparison of columns or generate hash for each row using hashbytes function. You can compare rows efficiently this way and then perform the conflict resolution. This of course is no easy matter since each technique has it's own pros and cons.|||

hi,

Can I know is there anyway to compare 2 timestamp in .NET?

For eg. I got 2 timestamp, I want to know which 1 is the most recent changed.

Thanks.

Distincts databases on the same stored procedure

Hi!

I need to use two distincts databases on the same stored procedure. One, database1, is where I want to place the procedure, and where the table the procedure populates is (table1). The other, database2, hosts the table (table2) from where I select some data to put into table1 in database1.
Database1 name is fixed, while database2 name may change. So, I would like to pass database2 name as a parameter to the procedure. In this simple example, it works fine:

use database1
go
create procedure teste @.db_name varchar(10) as
exec ('select * from ' + @.db_name + '.dbo.table2')

But the problem is that in my procedure, database2 is used in a cursor, something like this:

create procedure myProcedure @.year int, @.pDatabase2 varchar(30) AS

declare ...

WHILE ...
BEGIN
...
declare cursor1 cursor for
select column1, column2 from @.pDatabase2.dbo.table2
where ...

open cursor1
fetch next from cursor1 into ...

close cursor1
deallocate cursor1

-- insert data into table
INSERT INTO table1
VALUES...

....

And I get an error if I try to use the exec! How can I do it? Can anyone help me please?

Thank you!

Try to rebuild your query; either refrain from using cursor, or from using variable for a database.

Also, you can EXEC('select column1, column2 from ' + @.pDatabase2 + '.dbo.table2 INTO ##TempTable'), then run a cursor over TempTable.

|||

You can execute the declare cursor dynamically if you use a global cursor like:

set @.dbname = quotename(@.pDatabase2)

exec ('declare cursor1 cursor global for select column1, column2 from ' + @.dbname + '.dbo.table2')

open cursor1

...

But it seems like what you are doing is unnecessary and complicated. You should instead do the following which is easier to manage.

1. Create a stored procedure in database2 that returns the expected results from table2 like:

create procedure gettable2

as

select col1, col2 from table2

2. And now do insert..exec from database1 like:

set @.sp = quotename(@.pDatabase2) + '.sys.sp_executesql'

insert into table1

exec @.sp 'gettable2'

Alternatively, you can just use approach in step #2 and issue the SELECT directly like:

set @.sp = quotename(@.pDatabase2) + '.sys.sp_executesql'

insert into table1

exec @.sp 'select col1, col2 from table2'

The SP is approach is slightly better from a security standpoint since you don't have to give SELECT permission on the table(s). With either approach, you will have to watch out for ownership chaining issues for example. See Books Online for more details on how ownership chaining works and db ownership chaining option.

|||Thank you a lot, it's much easier now!

displaying the list of databases faster in enterprise manager console

Hello,
We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
On this server we have 59 databases.
Different user's pc connect to it using enterprise manager. Client PC are
mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
Each time one of these clients connect to the server to display the list of
databases, it takes about 45sec to 1.15 min. I beleive this behaviour comes
from the increasing number of DB (it was much faster when we had about 5 to
10 db). Is there a way to "cache" this catalog (or any other way) in order
to displays this list faster?
thanks
Have you considered using Query Analyzer to look at the list?
http://www.aspfaq.com/2455
Also, make sure you don't have auto-close set for any of the databases.
This forces EM to take a lot longer than it should to show the list...
http://www.aspfaq.com/2469
http://www.aspfaq.com/
(Reverse address to reply.)
<grille11@.yahoo.com> wrote in message
news:cjjpqo$aeh$1@.reader1.imaginet.fr...
> Hello,
> We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
> On this server we have 59 databases.
> Different user's pc connect to it using enterprise manager. Client PC are
> mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
> Each time one of these clients connect to the server to display the list
of
> databases, it takes about 45sec to 1.15 min. I beleive this behaviour
comes
> from the increasing number of DB (it was much faster when we had about 5
to
> 10 db). Is there a way to "cache" this catalog (or any other way) in
order
> to displays this list faster?
> thanks
>

displaying the list of databases faster in enterprise manager console

Hello,
We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
On this server we have 59 databases.
Different user's pc connect to it using enterprise manager. Client PC are
mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
Each time one of these clients connect to the server to display the list of
databases, it takes about 45sec to 1.15 min. I beleive this behaviour comes
from the increasing number of DB (it was much faster when we had about 5 to
10 db). Is there a way to "cache" this catalog (or any other way) in order
to displays this list faster?
thanksHave you considered using Query Analyzer to look at the list?
http://www.aspfaq.com/2455
Also, make sure you don't have auto-close set for any of the databases.
This forces EM to take a lot longer than it should to show the list...
http://www.aspfaq.com/2469
--
http://www.aspfaq.com/
(Reverse address to reply.)
<grille11@.yahoo.com> wrote in message
news:cjjpqo$aeh$1@.reader1.imaginet.fr...
> Hello,
> We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
> On this server we have 59 databases.
> Different user's pc connect to it using enterprise manager. Client PC are
> mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
> Each time one of these clients connect to the server to display the list
of
> databases, it takes about 45sec to 1.15 min. I beleive this behaviour
comes
> from the increasing number of DB (it was much faster when we had about 5
to
> 10 db). Is there a way to "cache" this catalog (or any other way) in
order
> to displays this list faster?
> thanks
>

Displaying data from multiple databases

I am using a subreport to lookup up a name from a code value since the lookup
table is in a separate database from the main report. How do I limit the
white space on the name field? I have defined a single textbox in the
subreport, but when I preview the subreport it displays a complete line.
Similarly, when the main report previews the subreport displays the complete
line. I have tried placing the textbox in a rectangle, but it doesn't appear
to make a difference. Any suggestions?If you have a table for the data of the main report, add another column to
the table and put the subreport in that column. If it is a single value they
it will not go to another row.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:7C1E0D63-E86D-4A54-A7BE-7EBB2BD7F4F1@.microsoft.com...
>I am using a subreport to lookup up a name from a code value since the
>lookup
> table is in a separate database from the main report. How do I limit the
> white space on the name field? I have defined a single textbox in the
> subreport, but when I preview the subreport it displays a complete line.
> Similarly, when the main report previews the subreport displays the
> complete
> line. I have tried placing the textbox in a rectangle, but it doesn't
> appear
> to make a difference. Any suggestions?|||You could also try putting the subreport in a rectangle. (Can you guess,
I'm stuck on rectangles for solving white space problems?)
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OZux6OM3EHA.3596@.TK2MSFTNGP12.phx.gbl...
> If you have a table for the data of the main report, add another column to
> the table and put the subreport in that column. If it is a single value
> they it will not go to another row.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> news:7C1E0D63-E86D-4A54-A7BE-7EBB2BD7F4F1@.microsoft.com...
>>I am using a subreport to lookup up a name from a code value since the
>>lookup
>> table is in a separate database from the main report. How do I limit the
>> white space on the name field? I have defined a single textbox in the
>> subreport, but when I preview the subreport it displays a complete line.
>> Similarly, when the main report previews the subreport displays the
>> complete
>> line. I have tried placing the textbox in a rectangle, but it doesn't
>> appear
>> to make a difference. Any suggestions?
>

Display obsolete database names in restore panel

There are still a few obsolete databases showing in the SQL restore panel in
the field "Show backups of database".
Properly when someone drop the obsolete databases without selecting "delete
backup history".
How can I get rid of the database names in the backup history ?
SQL : SQL Server 2000/SP3aHi
Do you want to delete the backup history for a specific database? Then use:
EXECUTE msdb.dbo.sp_delete_database_backuphistory N'Test'
John
"Johnny" wrote:
> There are still a few obsolete databases showing in the SQL restore panel in
> the field "Show backups of database".
> Properly when someone drop the obsolete databases without selecting "delete
> backup history".
> How can I get rid of the database names in the backup history ?
> SQL : SQL Server 2000/SP3a

Display obsolete database names in restore panel

There are still a few obsolete databases showing in the SQL restore panel in
the field "Show backups of database".
Properly when someone drop the obsolete databases without selecting "delete
backup history".
How can I get rid of the database names in the backup history ?
SQL : SQL Server 2000/SP3a
Hi
Do you want to delete the backup history for a specific database? Then use:
EXECUTE msdb.dbo.sp_delete_database_backuphistory N'Test'
John
"Johnny" wrote:

> There are still a few obsolete databases showing in the SQL restore panel in
> the field "Show backups of database".
> Properly when someone drop the obsolete databases without selecting "delete
> backup history".
> How can I get rid of the database names in the backup history ?
> SQL : SQL Server 2000/SP3a

Display obsolete database names in restore panel

There are still a few obsolete databases showing in the SQL restore panel in
the field "Show backups of database".
Properly when someone drop the obsolete databases without selecting "delete
backup history".
How can I get rid of the database names in the backup history ?
SQL : SQL Server 2000/SP3aHi
Do you want to delete the backup history for a specific database? Then use:
EXECUTE msdb.dbo.sp_delete_database_backuphistory N'Test'
John
"Johnny" wrote:

> There are still a few obsolete databases showing in the SQL restore panel
in
> the field "Show backups of database".
> Properly when someone drop the obsolete databases without selecting "delet
e
> backup history".
> How can I get rid of the database names in the backup history ?
> SQL : SQL Server 2000/SP3a

Display list of databases in App_Data folder

Hi All,

The web site I'm working on will allow admin users to create a new database for each new client. I'm hoping to be able to keep all of the databases in the local App_Data folder, but I'm finding that I can't even find a way of generating a list of the databases in the App_Data folder.

The code I have is:

Dim serverConnAs New ServerConnection(".\SQLEXPRESS")serverConn.LoginSecure =TrueserverConn.ConnectTimeout = 30serverConn.Connect()Dim sqlServerAs Server =New Server(serverConn)For Each dbAs DatabaseIn sqlServer.Databases ListView1.Items.Add(db.Name)Next

This does populate a list of databases, but not the ones in the App_Data folder - just those attached directly to the server.

Any help on this will be greatly appreciated as it's starting to drive me nuts!

Thanks,

Paul

Hi Paul,

What you are getting is correct. You will get a list of databases running on the server.

If you want to create databases on a particular directory you have to override the default directory

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data or something similar depending on where is your default data directory.

You have to specify that in your create database script

USE MASTER

CREATE DATABASE DataBaseName
ON
(
NAME = LogicalName_Data,
FILENAME = 'c:\YourPath\App_Data\DataBaseName_Data.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1
)
LOG ON
(
NAME = 'LogicalName_Log',
FILENAME = 'c:\YourPath\App_Data\DataBaseName_Log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)

Use

Server server = new Server(new ServerConnection(cnnDb));
server.ConnectionContext.ExecuteNonQuery(sqlString);

to create the new database.

If want to find all databases that are present in the directory.

You have to do a naming convention trick of naming the database starting with

GeneratedDb_ and filter the names based on it or

You can use DirectoryInfo class and read the App_Data folder and get all files having extension .mdf

Or loop through the filegroups in Database and check for App_Data folder

Happy programming,
Anton

|||

Hello,

this may be helpful to u

http://www.codeproject.com/aspnet/dbadmin.asp

http://www.codeproject.com/aspnet/MyDbViewerSite.asp

|||

Hi guys,

Just wanted to say a big thank you for your replies. It looks like this is tougher than I expected as I don't think I can get away with updating the master database to force the creation of the databases (ultimately, the web app won't be loaded on my server), and I had loved the idea of using '|DataDirectory|' so that I didn't need to explicitly give the server name for the connection strings already created. However, I think I've read enough now to believe that I am better off setting things to work directly with the server rather than locally - at least I seem to be able to create new databases and copy data as I need to.

Thanks again,

Paul