SQL Server Admin Project

[tokoloshi]

With XAMPP in the PHP world, you can install PHP, Apache, MySQL and all of the tools needed to manage a complete solution from a single installer.

All of the ADMIN for this environment is then run from a PHP driven front-end, which I reckon is pretty cool. So, I want to recreate that kind of environment as an "open source" project in a way.

I would be happy to upload the code & share it, as well as use this forum as a repository for the project.

Maybe it is already done and I don't have to beat myself up trying to re-invent the wheel, but I haven't been able to find such a project anywhere else yet.

So, I am including a simple app with this post as a starting point.

The app consists of a single form, a module that has Doug Steele's DSN-Less connection with my amendments to it and a set of linked tables to an SQL Server.

The form has a place to enter a username & password that will refresh the links to your server. I did not put a text box for SERVERNAME or DBName on the form because I am lazy.

Also on the form is a tab control with 2 tabs which will display all the DBs on the server selected, all the tables in the default database that you are currently logged into and all of the logins defined on your server as a starting point.

I would like to be able to eventually get to the point where this app can be used to fully administer an SQL Server DB, whether that DB is Oracle, MySQL, SQL Server or any other.

I don't know if this would be of interest to everyone else, but it definitely is a tool that I will find very valuable in my life and am willing to contribute to it on an ongoing basis.

I am going to post a couple of other questions related to this app afterwards, so that the individual threads can stay discrete from this broad topic, but they will realy be part of this project in my mind.

 

[georgedwilkinson]

I use Toad (for Oracle) and Toad (for SQL Server) in addition to the built in tools. Seems there is a nice tool for just about every environment but there is not one all-encompassing tool.

 

[Banana]

Personally, I use Navicat for MySQL (and they have Navicat for Oracle & postgre as well.)

Just one thing I must be missing... exactly what do you think is missing from all available manager?

 

[tokoloshi]

exactly what do you think is missing from all available manager?

Well, it is not so much a question of what is missing from existing managers as much as it is a question of what is missing from my applications.

It would be nice, when building a large solution, to have a completely integrated management interface dropped into the app, you know - like an admin option on the Access 2007 Ribbon, or an Admin menu option on earlier versions of Access.

When you go into that interface - and after you have authenticated the user, you should be able to perform all of the admin functions that you would normally swap out of the application and into NavCat, Toad or SSMS for.

At some level you do need to swap into a development environment, no question, but if the issue is the creation of a user, the creation of a table or view in SQL Server, the reseting of a user's password, etc - then one should be able to provide that as a function from within your own application.

Also, when you distribute your application then the installer should be able to install SQL Express/Oracle XE/MySQl, then recreate the entire DB, create the users, set the permissions all from within your app - something along the lines of how one would set up a DOTNETNUKE or XAMPP or any of the other environments.

I realise that there are several tools like that, but none that i know of specifically for ACCESS, written within ACCESS using VBA that can be imported into my own ACCESS application and shipped as part of the admin portion of my own app.

 

[Banana]

What I would really find interesting is a mean to hook into existing 3rd party apps, saving me from re-inventing the wheel. Normally, they don't provide such APIs, so that's a nonstart, but I can conceive with open source software, one could create the needed library to provide the hooks...

BTW, someone wrote something like that but for MySQL only; Here's the page. I haven't used that product as I was quite satisfied with flipping between NaviCat and Access.

Also, to throw out some possibility, take look at this nifty tool (this is for Mac OS X only...) which handles the ERD diagram and generates a .sql file that you can then dump into the server. Again, if we could somehow implement this functionality, this would provide the basic functionality for just about any RDBMS without too much work... (In case this isn't clear, you can select which SQL dialect to write in, and tailor the .sql file to accommodate the specific RDBMS's quirks and particulars.) If such similar open source software can be found and an API written, I'd bet that would be very useful™ for intra-Access management.

 

[SQL_Hell]

Have a look at this:

http://www.databasejournal.com/features/mssql/article.php/3730686

Looks great to me, a more expansive version of spot light for sql server that is free