December 30, 2014 at 7:38 am
GilaMonster (12/30/2014)
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.I'd be very doubtful about that. They probably take file backups, but file backups are not SQL backups and backups of the database files aren't always usable, especially if they're taken when the system is in use. Your database, you need to make sure about the backups, because you'll be the one people are yelling at if what corp restores won't attach and isn't usable.
From what I gather, there are SQL Server DBAs there and this database will be on a SQL Server dedicated server. It is possible that they do backups with a third party product that automatically backs up every database on the server.
Is that correct? It is something you should really check.
--------------------------------------
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 30, 2014 at 7:51 am
Stefan Krzywicki (12/30/2014)
GilaMonster (12/30/2014)
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.I'd be very doubtful about that. They probably take file backups, but file backups are not SQL backups and backups of the database files aren't always usable, especially if they're taken when the system is in use. Your database, you need to make sure about the backups, because you'll be the one people are yelling at if what corp restores won't attach and isn't usable.
From what I gather, there are SQL Server DBAs there and this database will be on a SQL Server dedicated server. It is possible that they do backups with a third party product that automatically backs up every database on the server.
Is that correct? It is something you should really check.
Many such 3rd party products register their backups in SQL Server and they'll show up in the restore window that I previously posted about.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2014 at 7:52 am
IncidentalProgrammer (12/29/2014)
Stefan Krzywicki (12/29/2014)
IncidentalProgrammer (12/29/2014)
Stefan Krzywicki (12/29/2014)
I've dealt with Access databases that reach that 2GB limit. You're going to want to switch long before you reach that limit since Access starts to slow down when it gets large.That isn't a problem! The tables shouldn't be much different. If you'd like to discuss proper data typing to make sure you have good field types, I'd be happy to help.
Are you developing in production? I tend to buy every version of SQL Server in the Developer version because it is nice to be able to use it to learn at home. Make sure they have a development environment for you at work, even if it is just a powerful desktop/laptop. You don't want to crash production because you were trying to make it better.
The plan is to switch before hitting the limit. It's just knowing about the limit that got me looking into SQL Server to fix the problem. I'm all about being proactive!
I may have to take you up on that, when I get back to work next week. Do the data types differ from Access, at all? I feel like I have a pretty good grasp on them in Access.
I am developing in production. At the moment, I'm using the same desktop as everyone else, but I'm getting a third monitor...I'm thinking you mean I need more power though, right? I don't have the specs handy at home, but I know it's nothing high-powered. Will SQL really take that much juice to run? I haven't had trouble with Access yet, except it makes Citrix kick me out constantly, but I'll be doing SQL Server outside of Citrix, so I won't have to worry about that.
And I WOULD do something goofy on a Monday with no coffee. My bad about the names!
It isn't that SQL takes so much juice to run, you just want to be able to use a dataset similar to what production will be for testing. Otherwise you might think something is fast when it isn't. A more powerful machine will also help guard against something seeming slow when it won't be.
If you are developing in Production and there are other databases on that server, you can cause problems for those databases with a development problem in yours. That's the main reason I suggest getting a dev environment.
Access data types are similar, but not exactly the same, if I recall correctly. The main thing you want to check is if you're using the smallest data type you possibly can. You should be. Know your data and size appropriately. When you're back at work, feel free to ping me and we can walk through it together.
I think I see what you mean. I'm REALLY hoping to get away with it on what I'm using now, though. I really don't want to ask for a new computer.
They've given me my own exclusive space to play with. I checked and rechecked to make sure of that, because I was worried I might bull-in-a-china-shop it, and break things. I've been assured that there's no way I can touch anything but my own DB, in there.
Sure, I can understand that, switching machines can be a pain. However, if that's the case, you should try to get them to give you a development area separate from production and there are several reasons for that.
1) As someone else mentioned, once you deploy it and people start using it you don't want to chance breaking it when making improvements.
2) You want to be able to try things out and see how they work without affecting users. Maybe you have a great idea, but it doesn't work out the first time and you accidentally wipe out the data. You don't want to have your users wait for a data restore and then have to re enter all the data they lost from earlier that day. Even if it does work fine, there are changes where you might have to create a new table, move the data, drop the old table and then modify the interface to read the new table. Do you want users to be offline that long?
3) If there's a hardware failure you could lose everything and have to start over from a backup. That backup won't have that day's data or any work you did since the last backup. You might want to ask if your IT department uses source control and if you can too. It is a great way to keep older versions of your work so you can go back and see what you've changed.
4) Is this on a VM(virtual machine)? You need to find that out. If you have your own VM or your own server, great. If not you can bring down the entire machine.
And I'm not saying all this because you're new to this. We all need to put safeguards in place. None of us are immune from mistakes and the more you prepare in advance, the less those mistakes will hurt when they happen.
Space-saving is one of the things I've put a lot of time into researching. I can't name all the data types off the top of my head, but that's why I take lots of notes. I've been shrinking things down as small as I possibly can.
Sure, I'm constantly looking at the data types page (http://msdn.microsoft.com/en-us/library/ms187752.aspx) to make sure I get everything right. I find it helps to have someone look at things with you to make sure you're looking at the data the right way. When space is an issue, there are things like SPARSE columns and Computed columns that can help. There's also making sure you have the tables normalized far enough that you minimize the space taken without seriously harming retrieval speeds. Sometimes you can have a column of data that you don't even realize has a more appropriate data type until you've really looked into it.
--------------------------------------
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 30, 2014 at 8:03 am
I must say this post brings back memories. I got my start doing water/waste-water utility billing data conversions into an Access system. The company got a bid from a larger utility and wanted a SQL Server back end. We hired an "expert" who had all the certifications to help us upsize the back end to SQL Server and get it all working. Well, he left us just as we were about to install the system and test it on the client. Since I was the only one on site, I had to fix everything that was messed up and learn TSQL by the seat of my pants. 14 years later the system is still running, But it has been a journey and it in no way resembles the original product.
I also suggest getting to the SQLServer back end quickly. I found many of the queries had to be reworked, and new indexes had to be built to get SQLServer to perform. But if you can get to a set based mindset there are great performance gains to be had. The billing routine written in VBA that went "row by agonizing row" (RBAR) took over 45 minutes to complete. That same routine as a stored procedure is less than 5 minutes. Just linking the tables to the backend and hoping everything works the same, certainly wasn't the case 14 years ago. I now work for the utility that bought the system and we often start with an Access front end and always use SQLServer as the back end.
Good luck, It can be done and it has been the time of my life for an old hack like me.
January 5, 2015 at 6:13 am
GilaMonster (12/30/2014)
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.I'd be very doubtful about that. They probably take file backups, but file backups are not SQL backups and backups of the database files aren't always usable, especially if they're taken when the system is in use. Your database, you need to make sure about the backups, because you'll be the one people are yelling at if what corp restores won't attach and isn't usable.
That sounds like something I definitely need to check up on. Thanks for the tip! And I'm sorry, but still new to all of this...in checking with IT to see which is the case, what would be a good way to phrase that so that I get an exact answer? Because when I asked before, I got the info above about their backups, so I'm apparently going about it wrong.
January 5, 2015 at 6:15 am
GilaMonster (12/30/2014)
IncidentalProgrammer (12/29/2014)
They've given me my own exclusive space to play with. I checked and rechecked to make sure of that, because I was worried I might bull-in-a-china-shop it, and break things. I've been assured that there's no way I can touch anything but my own DB, in there.But once your system starts getting used by others, you'll need a development or test environment otherwise a change could take the system down.
Absolutely! This is one of the things I am aware of. I have just the one for now, but once it's satisfactory, it'll be the live version, and I'll have corporate copy it into a second test version. I will not be experimenting on live. I know well the value of a guinea pig.
January 5, 2015 at 6:18 am
IncidentalProgrammer (1/5/2015)
GilaMonster (12/30/2014)
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.I'd be very doubtful about that. They probably take file backups, but file backups are not SQL backups and backups of the database files aren't always usable, especially if they're taken when the system is in use. Your database, you need to make sure about the backups, because you'll be the one people are yelling at if what corp restores won't attach and isn't usable.
That sounds like something I definitely need to check up on. Thanks for the tip! And I'm sorry, but still new to all of this...in checking with IT to see which is the case, what would be a good way to phrase that so that I get an exact answer? Because when I asked before, I got the info above about their backups, so I'm apparently going about it wrong.
Try using the phrase "transactionally aware" as in "Is the backup program transactionally aware? Will it support the durability property of my database files which are ensured by the SQL Server operating system? Really? Cool. Let's perform a test restore just so I can see it in action."
That's how I'd go about it. If they don't know about transactions and durability of database files, then chances are high that it's just a file backup process and isn't aware of how databases work (and they do work differently). this would mean you need to get a backup running internally to a location that the file backup can then capture. That way you'll be covered.
"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
January 5, 2015 at 6:18 am
Grant Fritchey (12/30/2014)
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.I don't trust that at all. SQL Server really does do things a little differently. The ACID properties of the transactions means that standard backup routines may not be able to restore things properly. I'd suggest a test of this when you can, assuming that data loss would be an issue for you.
Would it make any difference that they use SQL Server for other things worldwide, and should have all of this figured out already? Corporate seems to keep everything pretty much in their hands; we even have to call them for password resets. Would it be possible for them to be doing the kind of backups I'd need, or is that something that can only be done on location?
January 5, 2015 at 6:22 am
Jeff Moden (12/30/2014)
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.You can do a real quick check to see if and what kind of SQL Server backups they are (and you need to [font="Arial Black"]NOT [/font]click on OK EVER when doing this). If you right click on a database in the Object Explorer window, click {Tasks}, and then {Restore}, you should see a list of files (one MDF, possibly a DIF or two, and a fair number of LDF files) that can be restored and the dates should be quite recent. If you don't, then you're going to have to educate some folks.
Make sure you click {Cancel} to get out of that window.
Unfortunately, I don't have anything in SQL Server yet. I haven't moved anything over. I did, however, write this on a sticky note, and attach it to my monitor. Thanks for the tip! I do not want to click OK.
January 5, 2015 at 6:24 am
Ed Wagner (12/30/2014)
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.I'm so glad Gail, Grant and Jeff hit on this. I was reading the whole thread and got that sinking feeling when I read this comment. I wouldn't rely on a backup process by anyone without testing. If you don't test the backups by restoring, then you won't know they're any good. You need to do a test of their system by submitting a ticket, restoring to a separate database and then checking it. I do periodic checks to make sure my backups (both full and point-in-time) are viable. My advice on backups:
1. You need to know your backups are viable and you can restore from them. Test the backups.
2. You don't want your first restore to be when you need actually need it. Practice restoring before you need to.
I can do that! Thanks for the idea! Probably faster than getting an answer from corporate about how they work, anyway!
Just to make sure I know what all to look for, what types of markers would you suggest I place to be sure they've restored everything? I figure some obvious dummy records in the tables, but is there anything else you would recommend?
January 5, 2015 at 6:32 am
IncidentalProgrammer (1/5/2015)
Grant Fritchey (12/30/2014)
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.I don't trust that at all. SQL Server really does do things a little differently. The ACID properties of the transactions means that standard backup routines may not be able to restore things properly. I'd suggest a test of this when you can, assuming that data loss would be an issue for you.
Would it make any difference that they use SQL Server for other things worldwide, and should have all of this figured out already? Corporate seems to keep everything pretty much in their hands; we even have to call them for password resets. Would it be possible for them to be doing the kind of backups I'd need, or is that something that can only be done on location?
Yeah, it's absolutely possible. But it's not something I would assume, ever. I'd validate it. And the best way to validate it is to get a restore. Plus, a lot of enterprises have multiple support teams. There may be one support team for your IT infrastructure who works with DBAs and databases and knows all about this stuff and is on top of it and another team that works primarily with user laptops, etc., who really don't have a clue about SQL Server and locked files and ACID properties, etc. I've seen that pretty frequently in different enterprises. So, when there are silo applications like you're working with, they can be completely unsupported by the greater IT infrastructure.
"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
January 5, 2015 at 6:40 am
lshanahan (12/30/2014)
IncidentalProgrammer (12/29/2014)
I talked to my boss about the feedback, and she said if we have to start without SQL Server, that's fine. I know we will need to move up to it before long though, because we're going to chew through Access' 2GB limit. I still need to figure this out though, and make our DB as SQL-friendly as possible, to make the transition smooth.
Another good reason to build in SQL Server from the start in your case.
IncidentalProgrammer (12/29/2014)
@ SSC-Enthusiastic: When you say that SQL Server doesn't understand VBA or the Access Object model, do you mean that the code I'm running in my forms is going to have issues when I migrate to SQL Server? I've had to refer to fields in tables in the course of coding, but I tend to do that by mixing some SQL into my VBA; is that not going to read correctly after the switch, though? As long as the code stays with the front end, shouldn't it work?Let me introduce you to one of the "key phrases" of SQL Server (and likely databases in general): It depends.
Jet SQL - the flavor of SQL used by Access - is fairly similar to T-SQL used by SQL Server, but there are some notable differences. If your VBA/SQL code is referring to objects strictly in the Access front-end, you will probably be okay. Things like pass-through queries (queries that exist in Access and get passed to SQL Server for processing) can burn you. Pass-through queries become important when you want to execute SQL Server stored procedures from Access (and you will, trust me).
It's perfecctly acceptable to create something in Access and scale it up to SQL Server. If that's your comfort zone, by all means go for it. But since you're intending to scale up anyway in a relatively short period of time, I think you'd be better served focusing your efforts on learning SQL Server and how to create your solution there then figuring out how to connect up with Access.
Again, good luck.
P.S. What Gail, Grant, et al., said about backups. The question is how much data can you afford to lose?
I was figuring the queries I've made already would move into SQL Server with the tables, rather than remain with the Access front end. But there are queries that have to pass through both?
At the moment, I'm much more comfortable working in Access. Up until this past Summer, I'd never touched anything like this (except as a user), so I'm still a little shakey. With what initial launch has been bumped down to, I don't think we'll eat the 2GB all THAT fast, it's just that I'll want to have it in SQL Server before I patch too many other space-hogging features in. This way too, it should give me time to get a firmer grasp on SQL Server, before jumping right in.
January 5, 2015 at 6:46 am
Stefan Krzywicki (12/30/2014)
GilaMonster (12/30/2014)
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.I'd be very doubtful about that. They probably take file backups, but file backups are not SQL backups and backups of the database files aren't always usable, especially if they're taken when the system is in use. Your database, you need to make sure about the backups, because you'll be the one people are yelling at if what corp restores won't attach and isn't usable.
From what I gather, there are SQL Server DBAs there and this database will be on a SQL Server dedicated server. It is possible that they do backups with a third party product that automatically backs up every database on the server.
Is that correct? It is something you should really check.
Yes! That sounds right!
I just emailed our IT guy here, to ask if it's backing up the entire database. I may get referred to corporate, but when you put it that way, I'm pretty sure that's what they're doing.
January 5, 2015 at 6:58 am
Stefan Krzywicki (12/30/2014)
IncidentalProgrammer (12/29/2014)
Stefan Krzywicki (12/29/2014)
IncidentalProgrammer (12/29/2014)
Stefan Krzywicki (12/29/2014)
I think I see what you mean. I'm REALLY hoping to get away with it on what I'm using now, though. I really don't want to ask for a new computer.They've given me my own exclusive space to play with. I checked and rechecked to make sure of that, because I was worried I might bull-in-a-china-shop it, and break things. I've been assured that there's no way I can touch anything but my own DB, in there.
Sure, I can understand that, switching machines can be a pain. However, if that's the case, you should try to get them to give you a development area separate from production and there are several reasons for that.
1) As someone else mentioned, once you deploy it and people start using it you don't want to chance breaking it when making improvements.
2) You want to be able to try things out and see how they work without affecting users. Maybe you have a great idea, but it doesn't work out the first time and you accidentally wipe out the data. You don't want to have your users wait for a data restore and then have to re enter all the data they lost from earlier that day. Even if it does work fine, there are changes where you might have to create a new table, move the data, drop the old table and then modify the interface to read the new table. Do you want users to be offline that long?
3) If there's a hardware failure you could lose everything and have to start over from a backup. That backup won't have that day's data or any work you did since the last backup. You might want to ask if your IT department uses source control and if you can too. It is a great way to keep older versions of your work so you can go back and see what you've changed.
4) Is this on a VM(virtual machine)? You need to find that out. If you have your own VM or your own server, great. If not you can bring down the entire machine.
And I'm not saying all this because you're new to this. We all need to put safeguards in place. None of us are immune from mistakes and the more you prepare in advance, the less those mistakes will hurt when they happen.
Space-saving is one of the things I've put a lot of time into researching. I can't name all the data types off the top of my head, but that's why I take lots of notes. I've been shrinking things down as small as I possibly can.
Sure, I'm constantly looking at the data types page (http://msdn.microsoft.com/en-us/library/ms187752.aspx) to make sure I get everything right. I find it helps to have someone look at things with you to make sure you're looking at the data the right way. When space is an issue, there are things like SPARSE columns and Computed columns that can help. There's also making sure you have the tables normalized far enough that you minimize the space taken without seriously harming retrieval speeds. Sometimes you can have a column of data that you don't even realize has a more appropriate data type until you've really looked into it.
When I'm ready, they're going to copy everything over so that I have a test environment, and a live version. Or do you mean different physical work stations, as well? Moving me in back, with the IT guys might not be such a bad idea; programming has me wanting to cuss like a sailor, some days.
By VM, would you mean something like Citrix? The front end will be stored out in a general area within Citrix, where everyone who needs it can access it. The back end, from my understanding, is my own instance of SQL Server. I do access it from my desktop (outside of Citrix), but I'm sure corporate could hook it up to a new computer, if mine were to meet disaster.
And YES, I love safeguards! "Hope for the best, prepare for the worst!"
Thanks for that link! It's the Access ones I have. I'll have to refigure my data types, then.
January 5, 2015 at 7:03 am
Todd Payne (12/30/2014)
I must say this post brings back memories. I got my start doing water/waste-water utility billing data conversions into an Access system. The company got a bid from a larger utility and wanted a SQL Server back end. We hired an "expert" who had all the certifications to help us upsize the back end to SQL Server and get it all working. Well, he left us just as we were about to install the system and test it on the client. Since I was the only one on site, I had to fix everything that was messed up and learn TSQL by the seat of my pants. 14 years later the system is still running, But it has been a journey and it in no way resembles the original product.I also suggest getting to the SQLServer back end quickly. I found many of the queries had to be reworked, and new indexes had to be built to get SQLServer to perform. But if you can get to a set based mindset there are great performance gains to be had. The billing routine written in VBA that went "row by agonizing row" (RBAR) took over 45 minutes to complete. That same routine as a stored procedure is less than 5 minutes. Just linking the tables to the backend and hoping everything works the same, certainly wasn't the case 14 years ago. I now work for the utility that bought the system and we often start with an Access front end and always use SQLServer as the back end.
Good luck, It can be done and it has been the time of my life for an old hack like me.
Thanks, Todd! It's great to hear a success story.
I'm determined to get this into SQL Server at some point in the not-so-distant-future; that's a definite. We'll need the space, and the speed. I'm thinking that with the deadline being what it is, it might be safest for me to just go live in Access, and then the next project be to move it into SQL Server. As I go, I'm trying to design things to play as nicely with SQL as I can (like not using lookup or multi-value fields), but I know there will be things I need to redo.
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply