windows - is using Hosts for resolving a sql-server more performant?
2014-07
Ice
we have a legacy application which uses a access.mdb with hundreds of ODBC-connected tables on a sql-server. the access.mdb contains nothing else than these odbc-connections.
Now we consider to use a virtual sql-servername for these odbc connections and resolve it in the local hosts-file with the ip-address of the real sql-server.
Like this we can easy switch between a test-sql-database server and the the server for production in changing one single entry in the hosts.
EVERYTHING works fine and now comes the question:
Could it be that this is more performant because there is one single point on resolving the sql-server (name or ip-address)? Is there something like a network-cache / DNS-Cache?
peace
Ice
Snark
If the DNS server is local to your LAN, I doubt you'll see a noticeable improvement from having the entry in the hosts file. However, if you want to be able to switch easily from one SQL Server to another, it's a very convenient and fast solution, especially if the people managing the DNS are not very quick or eager to help.
Windows caches DNS records. In a Command prompt, run
ipconfig -displaydns
to see the content of the DNS cache.
If you want to empty it, to force Windows to request a resolution to the DNS server, run the command:
ipconfig -flushdns
You'll find more information about the DNS cache, how to disable it, and its settings in this article of the Microsoft Knowledge Base.
Martin
We currently have a few users who have been using Access very succesfully to throw around large amounts of data.
We've now got to the point where the data is just too large to be held in Access, as well as wanting to hold it in a single place where multiple users can access it.
We have therefore moved the data over to SQL Server.
I want to provide a general tool that they can use to view the data on the server and do some simple things like run queries and filters and export the data for offline manipulation.
I don't want the support headaches that might come with rolling out SQL Management Studio, and neither do I want to have to create an Access database with links for each current database or ones that are created in the future.
Can anyone recommend a simple tool that will connect to a server, list all the databases and allow a user to drill into a table and look at the data.
Many thanks.
SitWalkStand
I use this program: Database .NET It doesn't require any installation, just run the .exe and you get most of the functionality of SQL Management Studio.
fretje
I have to ask, why hundreds of databases and only three users? I am curious if these databases are all similar to each other and if so might you not be better off consolidating the data into a smaller number of databases (with some modification to implement segregating the data from each of the original datasets). Then you could easily use Access as the front end. If I were in your position I would certainly want to leverage the users comfort level with Access rather than having to retrain the three users in some new tool.
Gary
CodeByMoonlight
How about an Access Data Project? We've had a couple of these running for years just as a front-end a couple of users can use for custom queries. The learning curve should be minimal, although the logins should have as limited rights as possible.
8088
Try Query Express. It's a Query Analyzer clone that's packaged as a single 100KB .exe download.
Bryan Swan
Microsoft LightSwitch is [INCREDIBLE] at developing rapid UIs based on SQL Server databases for both General CRUD Operations as searching an analyzing tables.
Download the trial here, $199 worth every cent, and if your firm has VS Professional or enterprise, it's free.
http://www.microsoft.com/visualstudio/en-us/lightswitch
http://www.youtube.com/user/swanbryan?feature=mhee#p/u/9/559j-9HVPyU
ahsteele
I'd recommend using Excel. I believe this works in versions prior to 2007 but I'll give a 2007 how to.
- On the Data tab select From Other Sources
- Select From SQL Server
- Fill in connection information
- Select the table you'd like to view
A more through answer can be found in the Office Help Article: Use Microsoft Query to retrieve external data
Craig
I would definitely go with MS Access as a front end. Setup multiple databases with logical groupings of linked tables.
This means that it relatively transparent to the users, they shouldn't notice any major change. The skills they have learned using Access are still being used.
amarcy
You could set up a front end to SQL Server using ASP.NET.
I currently do this with a few clients who want to have data entry capability as well as running reports, queries, etc.
You could have a page with a drop down box to select which database to gather data from and a Gridview to display the data. You could also set up some parameterized queries where they can select date/values ranges for the various data that would come up.
Host it on the server and multiple people could access it from their machines.
JP Alioto
Check out LINQPad. It's light-weight, inexpensive and you can do everything from T-SQL to .NET 3.5.
Aaron - Solution Evangelist
I also recommend LINQpad. It's been a relatively new addition to my toolkit. It is exceptionally powerful. I use it for SQL fairly frequently (despite its name it doesn't over-deprecate SQL), but also for writing small C# programs and making use of LINQ. The free version is superb, but the Auto Completion license is well worth it. If possible use the latest v4 Beta which supports the .NET Framework 4.0 and also includes other syntax outlining.
If your users are potentially capable of working with SQL Query Analyzer they could be very at home with LINQPad and this would enable you to roll out .linq (LINQpad) files with sample queries for working with the data.
However, on further reading of your question, perhaps a basic (even 'out of the box') ASP.NET Dynamic Data deployment would work well for you. It has some great scaffolding support, shields users from complexity but gives you a lot of extensibility.