Remotely accessing SQL server DB
helveticus
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]'s profile photo](https://lh3.googleusercontent.com/a/default-user=s40-c)
Mark Rae [MVP]
news:3d99dd6b-1d32-478b...@34g2000hsf.googlegroups.com...
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
keep on adding extra admin pages for stuff that can easily be handled
by a DB client program.
Mark Rae [MVP]
> 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?
helveticus
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
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" <msch...@gmail.com> wrote in message
news:fb4a340a-e111-4183...@56g2000hsm.googlegroups.com...
NH
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" <msch...@gmail.com> wrote in message
news:3d99dd6b-1d32-478b...@34g2000hsf.googlegroups.com...
Mark Rae [MVP]
> 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-Server-Developer-2005/dp/B000BHQ5JW/ref=pd_bbs_sr_1?ie=UTF8&s=software&qid=1209740298&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/lifecycle/search/?sort=PN&alpha=Office+2000&Filter=FilterNO
helveticus
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.
Paul Shapiro
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" <N...@discussions.microsoft.com> wrote in message
news:ACD5A236-7EE2-4FC7...@microsoft.com...
Mark Rae [MVP]
news:OlBtvYUr...@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.