Any thoughts on SQL Server... 6

[Elliott3]

Hello Everyone,

I was thinking about using SQL Server and have never even seen it. Many people say that it is just like Access, which I have worked with, but much better.

How similar to Access is it really?

Is it a very big jump from MS Access to MS SQL Server?

Can you learn it on the go?

The more opinions the better. I would like to hear opinions from anyone experienced or just new to SQL Server. All opinions welcome!

Thank you all!!

Craig

 

[tlbroadbent]

Here is the start of a comparison list. I'm others can help flesh it out.[ul][li]Access and SQL Server are both made by Microsoft.
[li]Both use a version of the SQL langauge with many simlarities and some significant differences.
[li]Access is a primarily desktop and workgroup database.
[li]SQL Server is enterprise, web and XML enabled.
[li]SQL databases can be much larger than Access DBs.
[li]Access include an Integrated Development Environment (IDE). SQL Server does not but has several management tools.
[li]Access and SQL Server integrate fairly well so you don't have to discard Access or your existing knowledgebase. Check out the following link.

Access and SQL Server Integration Resources

http://msdn.microsoft.com/library/en-us/dnacc97/html/acsqlres.asp

[li]Several development tools can be used for SQL Server enabled applications. Visual Basic, C++, ASP, Delphi, ColdFusion, PowerBuilder, PHP, etc. all integrate well.[/ul] If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[dhqube]

In a nutshell Access is a combined Client & Server product -the client is the user interface (forms - reports etc) and
the server is the sql database. Other similar products ( though older) include dbase and foxpro.

SQL Server is similar to one half of this - the server. (though a much more powerful and professional product)

In other words it is possible to upgrade from Access to SQL Server, but only your data. Any forms,reports etc will NOT be part of SQL Server.

Thus if you move upgrade your database to SQL server, you will still need a client application. This could still be
Access if you want ( this is simplest as your forms and reports, with a bit of tweaking, will still work). Most professionals however will use one of the client tools Terry has listed, especially VB.

 

[SQLSister]

Other things you may not be aware of coming from an Access environment are:

You will need to do more in the way of dba work. In Access you can pretty much get away with no dba work by relying on network backups and not setting ssecurity on your databases. In SQL Server you need to learn about backup and restore and set up a backup plan. Not only is this needed to be able to get your data again in the case of a problem, but your transaction log will grow until it takes up your whole hard drive if you don't back it up.

You also can't escape without security as you can in Access, so you'll need to understand how SQL handles this. From a maintenance perspective it is better to set up roles and set permissions in the roles than to set individual permissions. But you need to read about security and really understand it.

In Access, you can get away with doing everything from the GUI and not even know what the SQL code is for a query. You need to learn T-SQL which is somewhat different from Access SQL. You have stored procedures and triggers to learn about.

You'll also probably want to learn about the different data types in SQL Server and how they relate to the data types you were using in Access. They are simliar but not the same, so you should look at them.

Something else you have that Access did not are tools to optimize queries and make them more efficient. This becomes very important as your database gets larger. You also have tools to monitor performance of the server and see things like who is logged in and what stored procedures were run, etc. that you don't have in Access. You probably won't learn this stuff right away, but be aware that it is there when the time comes you need to use it.

 

[paron]

I think one of the biggest differences that I expected and didn't see was a speed increase -- until I learned about stored procedures, which, if used with some wisdom, significantly reduce network traffic. Now we're flying!!

One thing I did see -- the security in Access is "wide open by default," so you are constantly chasing leaks. The security in SQL is "closed by default," so you spend a lot of time granting permissions. Better, of course, if you need security at all. And, since it integrates with Windows, SQL knows who everyone is when they log in, without administering a separate security system.

Ron

 

[SQLSister]

I meant to say that, paron, and forgot by the time I got through the other points. You can still use your Access front end, but you will need to convert your slow running queries to Stored procedures and then call the stored procedures to get a speed improvement. One thing you will find odd is that you can't directly put in the value of a field on a form to a stored procedure. Usually you have to use the user interface to store that information in a variable, then use the variable as the input for the variable in the stored procedure. Took me a while to get used to this, but it seems the normal way to do business now. You also probably need to index the tables.

 

[paron]

SQLSister, you said:

<clip>

You will need to do more in the way of dba work.

</clip>

and you said a mouthfull.

It's not as easy to juggle files around to solve problems. In Access, if you want a sandbox to play in, just copy the backend file. Not in SQLServer!! That's what I was searching for today when your response came in -- how to set up a development/production or development/staging/production environment.

BTW, I put the value of a field to a storedproc a little differently. I write a passthrough query in Access, then fiddle the content of the query to pass my parameters. I like it because then I can look at the resultset just by opening the query in Access. Ron

 

[SQLSister]

Setting up production/staging/development isn't too bad. If you are not in production, just detach the database and copyt he files to the new location and then reattach. If you are in production, restore your backup at the other location. Now managing the change once you have this set up is another problem. I use a tool called SQLCompare which compares the structure of all the database objects between two databases and then shows you the differneces and will write scripts to make them the same. They also have a tool for data comparing too which is handy for lookup tables, but I don't usually bother with making the data match between the databases. So you make changes in development and test. Then you move them to staging and test, then when everything is shiny, you move them to production. Youc an also move objects manually through your own scripts, but often you'll lose track of everything that was changed. If you do all changes through scripts and stored procedures, you can set up source control and move things that way. But that didn't seem to work very well for us because we were using the source control in visual studio .net and it still would allow people to directly change the database without using the items in source control. And I don't think there is anyway to prevent people from amkignchanges in Enterprise Manager and again these aren't in source control.

 

[tlbroadbent]

Often, developers don't see an increase in speed moving from Access to SQL Server. They link SQL Server tables to an Access database and expect it to be faster. Then the hard reality hits that in many cases it is slower because linked SL Server tyables in an Access database may actually decrease performance from what was experienced using Access tables.

To get the speed gains, it is important to think differently, to code for SQL Server, and take advantage of the technology such as stored procedures, views and functions. It is also important to learn how to optimize SQL Queries and procedures. Sometimes old technology has to be thrown out. That DAO record set that you updated in Access using the AddNew, Edit, Delete and Update methods just won't cut it in a client server environment. You'll need to learn about Access projects, pass through queries, executing Inserts, Updates and Deletes, etc. You may eventually toss Access altogether in favor of another development tool. If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[sguslan]

I quote our guru, none other than Terry:
"To get the speed gains, it is important to think differently, to code for SQL Server, and take advantage of the technology such as stored procedures, views and functions. It is also important to learn how to optimize SQL Queries and procedures. Sometimes old technology has to be thrown out. "

To that end, I read an interesting statement some where on the internet:

"if you do not like to change, get out of the IT business"

 

[Elliott3]

Hi,

Thank you all for all the information! I appreciate all the feedback I have received. I will be learning SQL Server as I write an application and I am sure you'll see me in here again. Thanks again!

Have A Great Day!

CES

 

[Elliott3]

Meant to give each person one star for their input. Thanks again, I have a good start now!

CES

 

[mwolf00]

I usually work in sql server, but on occasion I work in access. It's tough to go to access from sql server once you get used to nested subqueries and transaction controls - you can't "rollback" in access - if you screw up, oh well! Overall sql server compared to Access is like a modern computer versus an old Atari... Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com