Remotely accessing SQL server DB

[helveticus]

I'm in the process of finalizing my site. I spent quite a bit of time
designing content pages and would like to cut on development time by
simply managing the DB (SQL server, evt. MySQL) via MS Access. This
works well in my local environment. Can this be extended to a host
provider environment as well? Aside from interaction delays, what are
the pros/cons? Thanks for sharing some insights.

[Mark Rae [MVP]]

"helveticus" <ms******@gmail.comwrote in message
news:3d**********************************@34g2000h sf.googlegroups.com...

I'm in the process of finalizing my site. I spent quite a bit of time
designing content pages and would like to cut on development time by
simply managing the DB (SQL server, evt. MySQL) via MS Access. This
works well in my local environment. Can this be extended to a host
provider environment as well? Aside from interaction delays, what are
the pros/cons? Thanks for sharing some insights.

The Jet database is not really designed for use with websites, especially
those with heavy traffic.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[helveticus]

Thanks. What alternative would you suggest? I'm really not inclined to
keep on adding extra admin pages for stuff that can easily be handled
by a DB client program.

[Mark Rae [MVP]]

"helveticus" <ms******@gmail.comwrote in message
news:30**********************************@t54g2000 hsg.googlegroups.com...

Thanks. What alternative would you suggest? I'm really not inclined to
keep on adding extra admin pages for stuff that can easily be handled
by a DB client program.

I'm not sure I understand the problem. Why can't you just use SSMS?
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[helveticus]

Thanks again for your feedback. Do you mean by SSMS SQL Server
Management Studio? I currently have the Express version 2008 which
unfortunately does not support connections beyond the host it is
installed at.

Does that mean that I need to purchase the commercial version to do DB
management? I plan on carrying out fairly simple operations
(retrieving data from the remote DB for additional processing via
Access/Excel, and editing existing DB content tables). No need to
create specialized SPROCS, etc. (I'll use pass-through queries.)
Before embarking on remotely linking to SQL Server via MS Access, I
just want to ascertain that MS Access can link up to a remote ISP DB w/
o too many problems. Is this feasible or do I have to factor in
potential vulnerabilities (security, data integrity over the wire,
etc. ) that would defeat the purpose of remotely managing the DB?
Currently, I still use Office 2K (still working fine for my
purposes ;=) )

[Paul Shapiro]

You can link an Access mdb or adp to a SQL Server db and do at least some of
the management that way IF your Access version is more recent than your SQL
Server version. But SQL Server Management Studio is more capable. You can
buy SQL Server Developer Edition for about $50, and that includes SSMS. You
can probably also find free downloadable SQL management programs but I
haven't used any.

"helveticus" <ms******@gmail.comwrote in message
news:fb**********************************@56g2000h sm.googlegroups.com...

Thanks again for your feedback. Do you mean by SSMS SQL Server
Management Studio? I currently have the Express version 2008 which
unfortunately does not support connections beyond the host it is
installed at.

Does that mean that I need to purchase the commercial version to do DB
management? I plan on carrying out fairly simple operations
(retrieving data from the remote DB for additional processing via
Access/Excel, and editing existing DB content tables). No need to
create specialized SPROCS, etc. (I'll use pass-through queries.)
Before embarking on remotely linking to SQL Server via MS Access, I
just want to ascertain that MS Access can link up to a remote ISP DB w/
o too many problems. Is this feasible or do I have to factor in
potential vulnerabilities (security, data integrity over the wire,
etc. ) that would defeat the purpose of remotely managing the DB?
Currently, I still use Office 2K (still working fine for my
purposes ;=) )

[=?Utf-8?B?Tkg=?=]

I thought the Developer Edition is purely for testing and R&D and that kind
of thing. You cannot use it in Production.

"Paul Shapiro" wrote:

You can link an Access mdb or adp to a SQL Server db and do at least some of
the management that way IF your Access version is more recent than your SQL
Server version. But SQL Server Management Studio is more capable. You can
buy SQL Server Developer Edition for about $50, and that includes SSMS. You
can probably also find free downloadable SQL management programs but I
haven't used any.

"helveticus" <ms******@gmail.comwrote in message
news:fb**********************************@56g2000h sm.googlegroups.com...

Thanks again for your feedback. Do you mean by SSMS SQL Server
Management Studio? I currently have the Express version 2008 which
unfortunately does not support connections beyond the host it is
installed at.

Does that mean that I need to purchase the commercial version to do DB
management? I plan on carrying out fairly simple operations
(retrieving data from the remote DB for additional processing via
Access/Excel, and editing existing DB content tables). No need to
create specialized SPROCS, etc. (I'll use pass-through queries.)
Before embarking on remotely linking to SQL Server via MS Access, I
just want to ascertain that MS Access can link up to a remote ISP DB w/
o too many problems. Is this feasible or do I have to factor in
potential vulnerabilities (security, data integrity over the wire,
etc. ) that would defeat the purpose of remotely managing the DB?
Currently, I still use Office 2K (still working fine for my
purposes ;=) )

[sloan]

Sql Server Management Studio Express (the free one) will connect to remote
Sql Server's.

The remote Sql Server NEEDS TO CONFIGURED TO HANDLE/ACCEPT REMOTE REQUESTS.

...

But I use the freebie tool all the time to connect to remote sql servers.

I have licenses for the developer edition for 2 boxes (work and laptop).
Everything else I put that freebie tool on.

...

The freebie tool does not do JOBS, FYI. Outside of that, I don't really
miss anything.

...

The $50 for developer edition is well spent as well.

"helveticus" <ms******@gmail.comwrote in message
news:3d**********************************@34g2000h sf.googlegroups.com...

I'm in the process of finalizing my site. I spent quite a bit of time
designing content pages and would like to cut on development time by
simply managing the DB (SQL server, evt. MySQL) via MS Access. This
works well in my local environment. Can this be extended to a host
provider environment as well? Aside from interaction delays, what are
the pros/cons? Thanks for sharing some insights.

[Mark Rae [MVP]]

"helveticus" <ms******@gmail.comwrote in message
news:fb**********************************@56g2000h sm.googlegroups.com...

Do you mean by SSMS SQL Server Management Studio?

Yes.

I currently have the Express version 2008 which unfortunately
does not support connections beyond the host it is installed at.

I wasn't aware of that...

Does that mean that I need to purchase the commercial version to do DB
management?

I'd certainly recommend it:
http://www.amazon.com/Microsoft-SQL-...9740298&sr=8-1

I take it your ISP doesn't have a web-based RDBMS management option...?

Before embarking on remotely linking to SQL Server via MS Access, I
just want to ascertain that MS Access can link up to a remote ISP DB w/
o too many problems.

There should be no *technical* reason why you can't do this, assuming your
ISP allows this sort of connectivity. At the risk of sounding trite, have
you tried...?

Is this feasible or do I have to factor in potential vulnerabilities
(security,
data integrity over the wire, etc. ) that would defeat the purpose of
remotely
managing the DB?

It largely depends on what you're doing. However, bear in mind that the Jet
engine is a desktop engine which means that, unless you always use
pass-through queries, it will bring all tables referenced in a query in
their entirety over the wire to your desktop every time you run a query
against them...

Currently, I still use Office 2K (still working fine for my purposes ;=) )

FYI:
http://support.microsoft.com/lifecyc...ilter=FilterNO
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[helveticus]

Thanks for your response. You seem to imply that there should not be
too many hassles. Currently, I use SQL Server (because of MS built-in
support), but I'm considering switching over to MySQL in the future
(alas, LINQ is not supported). To me, flexibility is key.

MS Access provides this versatility. As a front-end client, I am able
via ODBC to access w/o too much trouble - at least in my local
environment - either SQL Server or MySQL and retrieve/edit data on the
fly. From what I have read and the wording of your post, the
performance penalties appear to be acceptable.

PS: Don't quite understand your first instance: "..You can link an
Access mdb or adp to a SQL Server db and do at least some of the
management that way IF your Access version is more recent than your
SQL Server version". What restrictions may arise? I link up to SQL
Server Express 2005 via Access 2000. Editing/Retrieving work ok.

[Peter Bromberg [C# MVP]]

Somebody correct me if I am wrong, but I seem to remember using the EXPRESS
version of SQL Server with remote connections very easily. Have you checked
your settings? Check this:
http://support.microsoft.com/default...b;EN-US;914277
Peter
"helveticus" <ms******@gmail.comwrote in message
news:fb**********************************@56g2000h sm.googlegroups.com...

Thanks again for your feedback. Do you mean by SSMS SQL Server
Management Studio? I currently have the Express version 2008 which
unfortunately does not support connections beyond the host it is
installed at.

Does that mean that I need to purchase the commercial version to do DB
management? I plan on carrying out fairly simple operations
(retrieving data from the remote DB for additional processing via
Access/Excel, and editing existing DB content tables). No need to
create specialized SPROCS, etc. (I'll use pass-through queries.)
Before embarking on remotely linking to SQL Server via MS Access, I
just want to ascertain that MS Access can link up to a remote ISP DB w/
o too many problems. Is this feasible or do I have to factor in
potential vulnerabilities (security, data integrity over the wire,
etc. ) that would defeat the purpose of remotely managing the DB?
Currently, I still use Office 2K (still working fine for my
purposes ;=) )

[Paul Shapiro]

That's correct, you cannot use SQL Server Developer Edition in production.
The SQL Server is licensed for a single connection. But the Management
Studio works fine for also managing a production server, and seems to me to
at least be within the spirit of a developer license. I haven't looked at
the license text to verify that it's legal.

"NH" <[e-mail] in message
news:AC**********************************@microsof t.com...

>I thought the Developer Edition is purely for testing and R&D and that kind
of thing. You cannot use it in Production.

"Paul Shapiro" wrote:

>You can link an Access mdb or adp to a SQL Server db and do at least some
of
the management that way IF your Access version is more recent than your
SQL
Server version. But SQL Server Management Studio is more capable. You can
buy SQL Server Developer Edition for about $50, and that includes SSMS.
You
can probably also find free downloadable SQL management programs but I
haven't used any.

"helveticus" <ms******@gmail.comwrote in message
news:fb**********************************@56g2000 hsm.googlegroups.com...

Thanks again for your feedback. Do you mean by SSMS SQL Server
Management Studio? I currently have the Express version 2008 which
unfortunately does not support connections beyond the host it is
installed at.

Does that mean that I need to purchase the commercial version to do DB
management? I plan on carrying out fairly simple operations
(retrieving data from the remote DB for additional processing via
Access/Excel, and editing existing DB content tables). No need to
create specialized SPROCS, etc. (I'll use pass-through queries.)
Before embarking on remotely linking to SQL Server via MS Access, I
just want to ascertain that MS Access can link up to a remote ISP DB w/
o too many problems. Is this feasible or do I have to factor in
potential vulnerabilities (security, data integrity over the wire,
etc. ) that would defeat the purpose of remotely managing the DB?
Currently, I still use Office 2K (still working fine for my
purposes ;=) )

[Mark Rae [MVP]]

"Paul Shapiro" <pa**@hideme.broadwayData.comwrote in message
news:Ol**************@TK2MSFTNGP06.phx.gbl...

>>I thought the Developer Edition is purely for testing and R&D and that
kind
of thing. You cannot use it in Production.

That's correct, you cannot use SQL Server Developer Edition in production.
The SQL Server is licensed for a single connection. But the Management
Studio works fine for also managing a production server, and seems to me
to at least be within the spirit of a developer license. I haven't looked
at the license text to verify that it's legal.

Perfectly legal. If an ISP provides SQL Server for its customers, it is up
to them to ensure that it is correctly licensed.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net