current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite
8

I am a 100% self taught MS Access programmer (main part of my job is programming), I am now building larger databases; still using MS Access as the UI but SQL Server to store all data and do more of the 'work'.

In essence my question is; what subject matters do I need to know for SQL Server that I probably didn't learn or need when using Access?

Not looking for you to tell me how to do anything, more what you think are the most important things I should go an research - there's a lot of subjects and a hell of a lot of detail, don't want to find myself a long way down a less valuable path...

Brain dump:

  • Maintenance: what are the most important (check database, reduce database, update statistics, rebuild etc)
  • Indexes - I don't know as much as I should, is there a good book/ blog etc that can teach me the basics upwards?
  • Anything else I have missed (there's probably lots, as I said I am new to SQL Server...)

If it helps I work for a mid sized retailer and the databases I predominantly work on cover such things as

  • Reporting platform (summarises sales/ receipts/ inventory etc from main system and provides fast reporting)
  • A reconciling tool between a third part and what our stores put through registers (imports data from third party and cross references the transaction logs)
  • Stores all data to do with our promotions; product, prices, projections, actual results etc
share|improve this question
    
Why SQl Server 2005? It's a bit old, with 3 main versions behind. –  Marian Apr 10 '13 at 7:16
    
It's what we currently have installed at my work and as I'm the only one really using it I'll have to demonstrate what value I can add with it before they will upgrade. –  Simon1979 Apr 10 '13 at 7:23
    
@Simon1979 one of the value is having a simple upgrade path. In SQL Server 2012 you can restore DB backup from SQL Server 2005, but not from earlier version (SQL Server 2000). Maybe that the next version of SQL Server won't allow you to restore DB backup from SQL Server 2005. And restoring a backup is one of the easiest way to transfer data from different servers –  Max Apr 12 '13 at 18:44

7 Answers 7

up vote 4 down vote accepted

As you learn more about SQL Server you will discover (to your delight) a number of things you can do in SQL Server at the database level that you previously had to do in Access at the application level. Some examples include:

Triggers: Procedures defined at the table-level to make stuff automatically happen whenever there is an INSERT/UPDATE/DELETE on the table.

Stored Procedures: Somewhat similar to Access macros and those little VBA procedures (Subs) you built in Access to do "useful stuff", except that in SQL Server they are part of the database itself. So, you can write a Stored Procedure (SP) once and use it (almost) anywhere, even across applications that share the same database.

Functions: These are somewhat analogous to the little VBA Functions you wrote in Access to incorporate into queries, except that SQL Server Functions, like SPs, are more tightly bound to the database. Also SQL Server Functions can be Scalar-valued (return a single scalar value) or Table-valued (return a rowset).

Fun SQL tricks: There are lots of SQL features available in SQL Server that are not supported in Access (Jet/ACE) SQL. "Common Table Expressions" (CTEs) and "RANK and PARTITION" are the ones that gave me "'aha' moments" as I was getting started.

I could go on, but these are the things I remember discovering early on that got me "thinking in SQL Server".

Don't get me wrong, I still think Access is an excellent tool when used appropriately, and should be given serious consideration as a way to

  • build (or just mock up) front-end applications against a SQL Server back-end, and

  • do data cleanup, create ad-hoc queries and reports, and perform similar tasks on your SQL Server data.

Finally, even though you will be working with SQL Server 2005 I highly recommend that you get a copy of SQL Server 2008 (or 2012) Express for learning, mainly because of the IDE enhancements in SQL Server Management Studio (namely, auto-completion and interactive debugging). Just be aware of any newer (2008+) features you may encounter and don't rely on them for your production code. (Or, use them as bullet points for your pitch to upgrade from SQL Server 2005....)

share|improve this answer
up vote 2 down vote

For anyone starting with SQL Server, I would recommend to start with learning the basic building blocks first, before you get into the DBA type things.

  1. Basic types of and functions of different SQL Objects (tables, keys, views, stored procedures, user defined functions, triggers)
  2. Writing Basic T-SQL, DML & DDL

Once you have a handle on these, and you can work on SQL Server without having to use MS Access, using mainly types T-SQL, then you could move onto learning more about the basic DBA type tasks, such as:

  1. Backup & Restore

  2. Maintenance

  3. Administration

Microsoft's MSDN & Learning sites will have very useful lessons and tutorials for you. I understand that it is not always clear where to begin, but if you roughly follow the above lists, you should be SQL-ing in no time.

Note: While most of the SQL 2005 Microsoft Certification courses have probably been discontinued, you can still use the available resources for SQL 2008 R2. I might get shot for saying this, but SQL 2008 R2 is similar enough to SQL 2005 (in the context of what you want to learn) so that you could study 2008 R2 and still be OK.

Lastly: Welcome to dba.stackexchange.com and SQL Server

share|improve this answer
up vote 4 down vote

There is a lot to learn... either online or through books. Here are a few first links to get you started (very incomplete list, but should keep you busy for a while) :

Forums

Blogs

Books

share|improve this answer
up vote 2 down vote

I am currently in exactly the same situation as you my friend and I can completely understand. So I will recommend the book that has solved many of the questions I had with regards to moving into SQL server.

Book: Microsoft Access Developer's Guide to SQL Server by Mary Chipman and Any Baron

The book explain everything you need to know from security benefits to user permissions etc and best of all, it is explained in a very readable and straightforward way.

There is probably a more up-to-date version of this book but this is the current one I use and it full fills all my needs!

Hope it helps you as much as it helped me.

Good Luck!

share|improve this answer
up vote 3 down vote

Having used SQL server for a few years now (and before that Access), I would suggest the following.

  1. Backup and restore - you are no longer taking a copy of a file to back up the database, depending on your needs (size, do you need up to the minute restores etc) have a go at backing up and restoring a database. You are nothing without your data!
  2. Watch Brent Ozar's videos: http://www.youtube.com/brentozar - the guy is a SQL Server legend. It's aimed more at the technical side, but there are some good beginner videos on there as well. I have learnt a lot from them (even if the subject matter is quite dry!)
  3. Maintenance - I'm guessing that the needs are minimal if Access has been okay until now: Unless you have lots of processing, lots of data (10GB+) or complex queries you could quite happily run SQL Server without doing maintenance for months without issue. Index reindex/rebuilds (there's a difference) are probably the first thing you will want to use though if you have lots of records.
  4. I really recommended the Stairways on SQL Server Central http://www.sqlservercentral.com/stairway/ - they are great introductions to the types of index, the data types (these differ from Access quite a bit!)
  5. If you are ever worried that your database isn't in tip-top condition try Brent Ozar's sp_blitz stored procedure (Google for it - I need more reputation to post an extra link!) - it checks lots of database statistics/parameters, provides a prioritised list of what needs doing with links to the website to offer more in-depth discussion.

I have started working with SQL Server Reporting recently, but it can be a pain to set up - for most business cases, getting Excel to retrieve data from a SQL Server Stored Procedure is often more flexible for managers.

Hope this helps, welcome aboard :)

share|improve this answer
up vote 0 down vote

One very interesting thing you can study on SQL Server is the Query Execution Plan, and the SQL Query Hint you can use when the query execution plan is not what you are expecting.

The Query Execution Plan, is an advanced topic, but it can help you understanding the performance of SQL Server.

Naturally you shouldn't misuse the Query Hint, in my experience they are the last resort when a query is not working with the expected performance.

share|improve this answer
up vote 2 down vote

To answer your questions --

Maintenance: what are the most important (check database, reduce database, update statistics, rebuild etc)

One of the best soluton for Maintenance is SQL Server Backup, Integrity Check, and Index and Statistics Maintenance Just read-up on it and deploy. Tested and used widely by SQLServer community.

Indexes - I don't know as much as I should, is there a good book/ blog etc that can teach me the basics upwards?

Stairway to SQL Server Indexes -- Excellent series from basic to advance level.

Anything else I have missed (there's probably lots, as I said I am new to SQL Server...)

There is a lot more than just maintenance and Indexing in SQL Server. My suggestion would be to read up on any content posted by below sites ... (They are the best !!)

SQLSkills.com

brentozar.com

SQLServerCentral.com

By working with SQLServer on a regular basis, you will feel the need to get more knowledge in different areas like Disaster Recovery, Performance Tuning, High Availability, Database Corruption, etc.

Note: There is a plethora of information out there on the web with excellent Bad advice as well. So my advise would be to get educated from the people who have worked and are working with SQLServer on a day to day basis.

Lastly, check this training from SQLSkills -- one of the best offered. I personally have taken IE1 and IE2 and trust me.. You will feel the difference !

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.