December 24, 2014 at 8:59 am
Hi, all!
So there's a little bit of explaining to do to make this all make sense, and I'll try to make it as to-the-point as possible. You're going to think we're insane. And we are. Clinically so.
I'm a career insurance lady with no background in technology, and my boss has me building a policy management system in Microsoft Access. Started learning Access in July, and VBA in August, and so far, I have most of the tables and a lot of the forms done, with lots of functional code. The plan is to have the system live by the end of the first quarter of 2015. Sometime between now and then, I have to learn SQL Server 2012, because I'm going to be storing the back end of the database in it. I've already been given a production environment with a terabyte of space and full authority, and once I have everything working properly, corporate will copy it into a secondary test environment for me.
My questions are:
A) What is the best/fastest way to learn SQL Server 2012? I've been learning Access and programming via books, the internet, and trial & error, and right now, that's the plan with SQL.
B) How long should it take to learn at least enough SQL Server to accomplish this? Right now, I just need to learn enough to stick the tables & queries of an Access 2007 database into SQL Server 2012, and have it run smoothly, all in time to launch this thing by the end of March 2015.
Please keep in mind I've only been doing all this since July, and have no formal training in anything IT-related. If I sound like a newb, it's because I am, and I'm sorry; I'm still working on grasping the technical terms, and such. THANK YOU FOR YOUR PATIENCE!
December 24, 2014 at 9:15 am
I'm going to be blunt and probably a little rude.
Your chances are not good. Sure, you may well get something that mostly works most of the time by March. With no experience, no IT background, no experienced designer or architect, the chance of a mess is high.
I've cleaned up those messes, they cost 10x or more to clean up what they cost to develop. This is not a reflection on you, this is a reflection on your boss who appears to think 'anyone can code'
Would you ask a chef to build a bridge?
Would you ask an accountant to diagnose illnesses?
Would you ask a teacher to fly a plane?
May I suggest, for the sake of your business (and reputation, after all it'll be your fault if there are problems), you chat with the boss about getting IT people to do IT. Again, not a reflection on you, but one does not acquire all the skills to write a scalable, well-performing, correct application in 3 months of reading and playing around.
My company (IT company, specialising in software development) has hired a bunch of university grads (IT degrees, best we could find) for Jan. We're putting them through 2 months of training from experienced devs, designers and architects before we'll consider letting them work, with supervision, on client's projects. That might give you an idea of why what your boss wants is a bad idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 24, 2014 at 9:27 am
The major question is: How important is this database for your company?
Will it change anything if, after a year or so, the system crashes and becomes unavailable and/or if all data are gone/manipulated?
If the company really needs the data I strongly recommend to get some help to verify the conceptual design and optimize the system. Maybe some kind of a mentor can help you to explore the world of SQL while reducing the risks for your company.
If it's more or less a "nice add-on" to support the company but is not business critical at all, then focus on the following items (from my point of view in the order as listed):
1) Backup and Restore (to make sure you don't end up without any data)
2) Security and permissions (to avoid data manipulation)
3) Concepts of software architecture (data, business, and application layer) as well as RDMBS (normalization, referential integrity,...)
and finally:
4) Table design and data types
5) T-SQL with focus on SQL2012
6) Concepts of code testing including performance test, maybe including source control
If you jump directly to step 4 you might be able to get the job done on time. But steps 1 to 3 will always hunt you down, probably sooner than later. The worst case scenario would be a total rewrite of the software including a total data loss, maybe even hacked company data that are not even stored on the server you're using...
Don't get me wrong:
I don't want to scare you away. But there are a few more aspects to it than just "stick tables and queries into SQL2012".
Regarding the conversion of ACCESS queries into T-SQL: Sometimes it's easier to re-create the queries directly using SSMS (SQL Server Management Studio) instead of trying to convert those IIF() statements.
Edit: And after all: is your boss willing to pay you for a job you've never done before and have no experience with?
December 24, 2014 at 9:33 am
Let me start with good luck and props to you for asking the question as well as taking this on. Working on stuff that might be out of your comfort zone can be a challenge (it can be for me at least)...
How long will it take to learn.... That is a difficult question because it will depend on the path you chose to take. Let me say I am not judging the various options, just pointing them out...
You could use the up-sizing wizard (assuming it is still around) to move your database to SQL Server and then use linked tables on the access side to try and make your app work would likely take the least amount of time to learn.
Everything else is going to take some real time, likely months at the least. Your mileage may vary.
Good luck
December 24, 2014 at 9:48 am
I'd like to add that I got my start in IT by building small systems in Access or some programming languages while I was a secretary. That said, it was a different time with different expectations and I had personal experience with databases before that. I heartily encourage you to continue learning SQL Server and to work on development. However, end of March probably isn't a reasonable time frame. I would suggest you hire at least one consultant (if there's reasons for this not to go through IT) and act as a junior developer on the project.
You have all the business logic, which is very important, get the consultant to essentially walk you through the process of developing a system. I've trained people this way as a consultant and if you get a good one you'll learn a lot. This will also help ensure you don't have any massive, hidden flaws that will bring the whole thing crashing down in 6 months when there's a lot more data in there or they want a new feature. No matter what they say now, they almost always end up wanting new features.
You'd also asked for other ways to learn SQL Server. In addition to reading the books (and there are a LOT of books you need to read) I'd suggest you get your employer to send you to a training course. After that, or instead of if they won't pay for it, find local user groups. They typically meet once a month and give a lecture about SQL Server and they're free. In addition you can meet SQL professionals there and there are plenty of people there who will answer questions or talk to you about design or problems. You should also google SQL Server Saturday and plan to attend as many of them as you can. They're usually free with a small charge for lunch. This is an all day event with a series of lectures, like a day of SQL Server college. There are multiple talks going on at the same time so you can pick which ones seem like they'd help you the most.
Posting questions here is a good idea, of course, but user groups & SQL Saturdays will give you contacts, other resources and training far beyond focused questions.
Finally, if you're going to be responsible for this for any length of time I would strongly suggest looking at what SQL Server professionals make. If this is more than you're making now, tell them you want more money. If it isn't, tell them you want more money anyway because you have additional responsibilities. I've seen a lot of companies ask non-IT people to develop something to save money. That both cheats the person doing the development and ends up costing them a lot more in the long run.
I wish you the best of luck. If you are in the Northeast USA, let me know and I can recommend user groups.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 24, 2014 at 10:48 am
Thanks for the responses, everyone!
To make some clarifications I now realize I should have from the start:
This system is NOT essential to the running of the business. This is a large, international company with multiple systems in place, but what my department does is so specialized that none of the existing systems really cover all our bases, and as of right now, we do a lot of hunting to find the data we need across the other systems, and a lot of manual work. The system I'm building will be taking in data from the other systems and user-entry, presenting exactly what we need, as we need it, and streamlining and automating a lot of the processes we are doing manually now. The main idea behind it, aside from streamlining our processes, is to have something that's tailored to our very specific needs, because there just isn't a product on the market right now that does all the specific things that my boss is looking for. If the system WERE to crash, there would definitely be some swearing and inconvenience, but the data exists elsewhere, and we could revert back to manual processes until it was fixed.
Trust me: I'm crazy, but I'm not crazy enough to take on a project like this if the system WAS critical--I would have said "absolutely no way".
Also (and I hope I can make this make sense), it's just the core data and a couple of major functions that I'm told are needed by the end of March. I have a LOT of big plans for this thing, but it's all going onto the back-burner to be patched in later.
@ GilaMonster: I TOTALLY get what you're saying. We're nuts, and your analogies are very fitting. With it not being a critical system (it's really more of a convenience) though, and a corporate IT team that can help if there are problems (they don't have time to teach me, but they'll fix things), I feel safe enough taking on the project.
@ LutzM: The only data that will be unique to this system is what relates to convenience for me and the team. All really critical, essential data exists in the systems provided by corporate, and my project is just meant to bring it all together automatically and in a meaningful way for my department, instead of us hunting it down and dragging it out like we have to now.
Thank you for the steps! I've actually done a lot of research into the basic premise of database design already, and have some of these things in place. I have a folder for backups, and a folder for backups of my backups. In addition, when it goes live, it will be backed up daily along with everything else in the network via corporate procedure. I'm relying on corporate's security (in addition, this system will not be online), and I'm currently working on the premissions system; at present, I do have logins set up, and users can either login manually, or automatically via their Windows ID. And I have restarted the project from scratch once, because after watching a series of tutorials, I realized my table setup was junk, and now I have a much better layout. Also, I've been testing the code and working out kinks as I go, and the boss realizes that this "live" version is also going to be doing some testing, since I can't test multi-user issues by myself; no one is expecting perfection at the beginning (because they fully realize that this is the first time I've ever done this, and my background), and the plan is to just point any issues out to me so I can fix them.
@Henry: Thank you, and yes it absolutely is. But I'm finding that I actually enjoy it all, even coding (even as I'm cussing at my computer).
Fortunately, the upsizing wizard is still available, and will be thoroughly abused. I haven't looked too far ahead to this yet though. When you say "linked tables on the access side", do you mean having copies of the tables in SQL AND in Access? If so, would both be storing data? What would be the function of the tables in the Access side? The reason reason for storing the back end in SQL Server is because I know we'll quickly hit Access' 2GB size limit, so I worry about data being in those tables. Or do you just mean linking the tables in SQL to the front end in Access, and I'm just reading too much into it?
@stefan: Thanks for the tips on learning opportunities! I will look for a group around here. I know they would be willing to pay for classes (my boss has happily ordered every book I've asked for), I'd just have to find some that work with my schedule; I'll have to try looking around for those too. I'll keep the consultant idea in mind, but I don't know if corporate would approve the spending, since this is not an "essential" system.
I will be keeping this up for a while, and adding new features as we find we need them. I honestly don't believe my boss has me doing this as a means of getting out on the cheap (though I have made the joke). In the beginning, it was a much smaller project, and it just sort of snowballed as our 3-woman department came up with new ideas for things that would make our work easier. And she explained that she had wanted me to do it because she wanted someone who's in the trenches with us and knows exactly what we do and what we need to make a product geared toward just that. From everything she's said, I know they're going to take good care of me, if I manage to pull this off; she's even told me to keep a log and everything to show our president what all it does and how it saves us time and manpower.
December 24, 2014 at 11:06 am
BTDT. Don't wait until everything is working then try to convert. You will have problems and need to work them out as early as possible. Convert ASAP as a pathfinder to just see if it will even work. Try everything and find what breaks. Willing to bet dates and data types are going to be wrenches in the machine. Same for queries calling queries (called views in MSSQL). And there is no such thing as stored procedures in Access but is generally used quite heavily in MSSQL. Permissions and security is a whole other layer of necessary complexity.
Please be aware that the SQL Server Migration Assistant for Access cannot do everything nor will it tell you exactly how to fix things. It also might tell you it doesn't have a clue about some things like the VBA stuff and things the tool doesn't understand. I have personally see it choose the wrong MSSQL data types.
Is going to the cloud a possibility? It does have some limitations and requirements but I have heard Access is making a comeback by using an Azure SQL database back-end. That would mitigate some of the risk and off-load some but not all of the management tasks. Might try the evaluation edition of SQL on a local machine and try to convert there too.
Adding to the blunt honesty- Access is not that complicated. SQL Server and n-tier complexity is. Not all IT conversion projects succeed on the first try!
I went down this road a long time ago- I didn't have a choice and was forced to learn outside of my comfort zone. I succeeded where other firms had failed- they were in too much of a hurry, not patient and not technically creative. The conversion project was my foray into the world of Accidental DBA. I've been a DBA ever since. Try to find local resources who you can use/ask questions. Good luck!
December 24, 2014 at 11:06 am
I haven't worked with Access in a while but I seem to recall you could set up a linked-table, where the data lives on a different database but is accessible via Access.
I only watched a bit of this...
https://www.youtube.com/watch?v=4bLRsYgtL-Q
but it may give you a better idea of what I am talking about...
Good luck
December 24, 2014 at 11:12 am
I've done more than one project where an app originally started with ACCESS only being converted to SQL Server back end and ACCESS as a frontend.
It's doable but there are pitfalls on both sides (e.g. data types and different style to design views).
So I second Todd to switch as early as possible.
December 24, 2014 at 11:29 am
When you switch Access to SQL Server and let the tool do the transfer, you can get really weird formatting of your TSQL. You might want to allocate some time reformatting it all for clarity.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 24, 2014 at 2:19 pm
You might also want to look at the SQL Server Migration Assistant for Access. http://msdn.microsoft.com/en-us/library/hh313039(v=sql.110).aspx This should help migrating the Access data and code both. I haven't used the SSMA for Access myself but I have used several different versions of SSMA to migrate from different databases to SQL Server with a lot of success.
December 24, 2014 at 4:49 pm
There's not much more to say that hasn't already been said. I did Access for a long time (and lots of VBA too), and moving to SQL Server takes a bit of a paradigm shift -- only because getting your head around all the stuff you can do in T-SQL that used to take queries and VBA etc was a lot. If you're not ready for SQL Server yet, model your system in Access and then rebuild in SQL Server. I would try to bite off the smallest chunk you can, and deal with that. Then go to the next one. Otherwise, it will get overwhelming. That's where having a local user group really helps. There are tons of insanely smart people there, and you should be able to find someone who can at least give you advice without much trouble.
December 24, 2014 at 9:07 pm
I'll just add what it says in my signature line about a paradigm shift but you have the added advantage of never having thought in rows before. Think about what you want to do to a column instead of what you want to do to a row and you'll be way ahead of the game. Also, look for the book on being an "Accidental DBA". That'll help a bit with the systems side of the house. Make sure that you have "Books Online" (the "help" system for SQL Server) loaded on your machine because it's easier to use than the Internet version (especially the INDEX tab). The first thing I always did when I studied a new computer language was to learn the basics and then study all the intrinsic functions. They make life a whole lot easier.
I'll also say that learning about indexes on tables is one of the most important things. Anything by Gail Shaw or Kimberly Tripp on that subject is well worth a read.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 27, 2014 at 5:26 am
Welcome to the forums!
Best of luck on what you're trying to accomplish.
The one thing I would add is to be sure that you learn how SQL Server does backups. It's different than other programs. You'll want to set up automatic backups and test them to be sure they're running well. I have an article [/url]I wrote on getting started with backups. I hope it's useful.
When you hit specific issues, please come back and post here in the forums. You'll get help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 28, 2014 at 10:59 pm
Dear Accidental Programmer,
Best of Luck..!! 🙂
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply