SQL Server help ??

Question

Can someone please explain to me how SQL Server works. I ask for the following reason: I have developed a Access database that is very useful for the company at which I work. I have always wondered how SQL server works and wondered what upscaling my Access DB to SQL would achieve. Am i right in believing that it would take control of all queries and tables then it would be down to me to program the forms and reports etc using something like VB ?? Hope you understand my query, any help appreciated

Answer 1

Yes, that is true. SQL Server provides a management console for administering the database and running occasional queries, and it also supplies reporting services for generating "canned" reports that can be run whenever and then viewed from (for instance) a web browser. But if you want any kind of interactive forms-based front-end, you'll have to build it in another development environment. Visual Basic is probably the simplest language to use, but there are many other good choices.

SQL Server does have many advantages over Access, especially for projects that may grow in size and complexity over time. To try it out, you can download SQL Server Express for free from Microsoft. This will give you most of the useful features of the full version, and you can use it as long as you want. Anything that you do in Express will be tranferrable to the full version if you choose to upgrade in the future.

I believe that there are also limited versions of Visual Basic and the other MS .Net programming languages available for free, but I haven't tried them.

Answer 2

A SQL server is as you have said above, 'would take control of all queries and tables'. It is where all the data is stored. You write code in a language of your choice to connect to the database server, and execute SQL queries. SQL stands for Structured Query Language and is a standised language to access data in databases. MS Access uses a subset of SQL which I think is slightly different than ANSI SQL (the international standard). You would have to write the code to create your tables, add primary and foreign keys, declare the type of data each column will hold etc. Once you have designed your database you then load this into the server and with your code you have written you can perform select, insert, update and delete queries to view, add, change and remove data.

This is the way to go if you want to scale up your database, because the database is hosted on a server many different people can access the database through network connections (which your code will provide).

There are many different SQL servers available, some free and some very expensive. A MySQL module running on an Apache webserver accessed via Java or PHP is a popular opensource, free environment which a lot of the internets large websites are powered by. Alternatively Microsoft, Oracle and IBM all have there own propritary servers which a lot of large businesses use.

If you want to play around with MySQL, Apache, and PHP you can install XAMPP on your computer which is an extremely easy way to get a full opensource environment set up to develop your database and web access software with.

Answer 3

SQL server fits the definition of a relational Database management system. Access on the other hand is a jet engine.

SQL Server scales much more than Access and better controls concurrency for multiuser applications.

SQL Server will server as the Data tier of your application. It will contain objects such as tables, stored procedures (objects similar to saved queries), Views.

As for how SQL Server works, it consists mainly of a database engine (responsible for storing and querying data), in addition to other services (such as SQL Server Integration Services, Reporting Services and so on).

Yes, you'll be responsible for developing your own forms which will access your data.

Hope this helps.

Answer 4

Adding to what the other answers have said, it is actually possible to move your database to SQL Server but keep your forms in Access, using it as a client. This would allow you to reap the benefits of a server-based solution, but reduce the amount of work you had to do in the short term.