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.

 

[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.

 

[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.

 

[NH]

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

 

[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]

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.aspx?scid=kb;EN-US;914277
Peter

 

[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.