Neebie Q: .MDFs and SQL Server

I'm just starting out with VWD 2005...wow this product and language has come along way, but I have a couple of simple questions of confusion. It seems that Microsoft is really pushing SQL via SQL Express over Access...and depending on the answers to these questions below...it seems like it has been accomplished.

Q

1. When I create a .mdf via VWD 2005, why doesn't this database show up in SQL Server Management Express?

2. Are you able now to simple publish the .mdf and .ldf (/appdata) folder up to any webserver and be able to access that SQL database like you normally would an Access database via SQL express on the server? And, if this is the case, doesn't this imply that an ISP will be providing SQL support via the Express edition without even explicitly allowing SQL through their actual SQL database server?

3. Besides VWD2005, how can you manage the .mdf's within the web site?

I think you almost answered yourself because the MDF (Microsoft data file) and LDF ( log data file ) are not stored in the database they are stored in the data subfolder in Microsoft SQL Server folder in programs by the SQL Server engine not you the user. So you see you cannot manage the MDF in a web server, because SQL Server from version 7.0 uses ODS(on disk) file system. Now to the ISP question you need the full version of SQL Server the developer edition cost a few dollars, it is Enterprise edition with no deployment restrictions and ask the ISP to give you management studio access and you just back up and restore your database on their server. Hope this helps.|||

Thanks Caddre:

Yeh, I'm not totally understanding this. The VWD gives you a great interface to work on modifying an SQL database on the fly while writing code. How can you get an existing SQL database already in the SQL server proper into VWD so that you can add fields and manage tables on the fly from this interface as opposed to having another Enterprise Manager or SQL client window up to do that separately. Its confusing to me.

I don't understand how SQL server knows about this database yet doesn't know about it via the Management Studio and visa versa, why can't VWD see existing databases that Management Studio can see. I see Add Exsting Item in the VWD App_Data but it doesn't pull up the SQL Management Studio or anything to let me choose an existing database.

If a Connection String can find this database via just two files within a web folder locally, why wouldn't it be able to find it on a web server in the same fashion?

|||

You can do what you want in server explorer but I don't know if you have server explorer in VDW. The quick answer is people should not be allowed to move RDBMS (relational database management systems) file as local operating system file because you risk corrupting the database. Management studio is SQL Server and VWD is an IDE (integrated development environment). So it is a choice, use inproc session which is file system or do some extra work to use SQL Server as out of proc session. The difference is algebraically processed data in SQL Server or data that is just stored in the file system of the operation system.

If a Connection String can find this database via just two files within a web folder locally, why wouldn't it be able to find it on a web server in the same fashion?

That will take Microsoft's SQL Server the new kid on the block out of the RDBMS business for serious application.

Hope this helps.

|||

double post

|||

Let's see if I can answer a few of these:

1. Because the database isn't attached to the same instance of SQL Server Management Express at the time it's running.

2. Sort of, you can if it has SQL Express installed. No.

3. Depends on what you mean by "within the web site". You can attach the .mdf/.ldf files to a non-user instance of SQL Express (The one the management studio normally sees), then you can use management studio to do whatever you want. Just don't forget to unattach it so that when you are debugging or want to use it within VWD that it'll be able to instantiate a user instance of SQL Express, and attach the files correctly.

|||

How can I get an existing SQL database into the VWD environment?

NDF file

I have a database and trying to put couple of the tables into a ndf
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.
Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegro ups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>
|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegro ups.com...
>
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,
|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A
|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...
|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
Aaron Bertrand
SQL Server MVP

NDF file

I have a database and trying to put couple of the tables into a ndf
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.1. Create a filegroup a put the NDF file in it.
2. Create/Alter the tables and point them to the new filegroup.
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
> >I have a database and trying to put couple of the tables into a ndf
> > file instead of mdf file, any ideas how can I achieve it?
> > Thanks a lot.
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > Is there a way in the enterprise manager I can manually specify tables
> > into secondary ndf filegroups?
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
--
Aaron Bertrand
SQL Server MVP

NDF file

I have a database and trying to put couple of the tables into a ndf
file instead of mdf file, any ideas how can I achieve it?
Thanks a lot.1. Create a filegroup a put the NDF file in it.
2. Create/Alter the tables and point them to the new filegroup.
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||Hi
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
<huohaodian@.gmail.com> wrote in message
news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>I have a database and trying to put couple of the tables into a ndf
> file instead of mdf file, any ideas how can I achieve it?
> Thanks a lot.
>|||On Jul 18, 10:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> <huohaod...@.gmail.com> wrote in message
> news:1184769009.426259.96790@.e16g2000pri.googlegroups.com...
>
>
Is there a way in the enterprise manager I can manually specify tables
into secondary ndf filegroups?
Thanks,|||> Is there a way in the enterprise manager I can manually specify tables
> into secondary ndf filegroups?
You need to manually either create the table on a specific filegroup or
drop/re-create the clustered index there. See CREATE TABLE and CREATE INDEX
in Books Online.
A|||On Jul 19, 12:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You need to manually either create the table on a specific filegroup or
> drop/re-create the clustered index there. See CREATE TABLE and CREATE IND
EX
> in Books Online.
> A
I am still trying to find out where I can specific filegroup for the
table I manually created within enterprise manager.
The way I created table is right click Tables -> New Tables ...|||> I am still trying to find out where I can specific filegroup for the
> table I manually created within enterprise manager.
> The way I created table is right click Tables -> New Tables ...
Well, when you create a new table, do you see the properties window on the
right? Under "Table designer" there is a property called "Regular Data
Space Specification."
Anyway, don't do that. Open a query window and say:
CREATE TABLE dbo.foo (id INT) ON filegroup_name;
I guarantee you that you will not be worse off knowing and understanding the
CREATE TABLE syntax vs. being able to point and click around in a GUI.
Aaron Bertrand
SQL Server MVP