December 19, 2016 at 9:35 am
All,
I'm starting a new position as a SQL developer for an organization that primarily does reporting. They are now getting requests to allow record-edits and simple insert update to users, so I've been assigned with recommending a user front end; just a very simple forms/applications to query, import, and update the data. User authentication will be needed. This organization currently has nothing like this set up, so if I can make a good foundation for future projects like this, I want to.
During our initial conversations, I said that a web-based solution would be preferable to a FAT client. Is that correct? In the past, I have worked with ASP.net using VB and C#. I think we could have a simple ASP.net site up quickly, but I'm concerned this would give us an overhead with setting up IIS, and also lock us into a Microsoft-only language. I'm currently exploring other projects I think could help this company that all tie into Java, so if we can leverage that language for this project, it would really help us in the long run. I don't know anything about PHP or JSP, or if there's something else that's better, and it doesn't look like they're going to hire a dedicated developer any time soon, so this would likely be my lovechild. Do we have some front-end developers here who can give insight on using Java in some way?
Foremost, I want the right tool for the job, even if it's ASP or some other Microsoft tool. But, I'm hoping to get some information from anyone here who has tried a front-end setup for SQL Server, and can point me in the right direction.
December 19, 2016 at 11:03 am
gdpollock (12/19/2016)
All,I'm starting a new position as a SQL developer for an organization that primarily does reporting. They are now getting requests to allow record-edits and simple insert update to users, so I've been assigned with recommending a user front end; just a very simple forms/applications to query, import, and update the data. User authentication will be needed. This organization currently has nothing like this set up, so if I can make a good foundation for future projects like this, I want to.
During our initial conversations, I said that a web-based solution would be preferable to a FAT client. Is that correct? In the past, I have worked with ASP.net using VB and C#. I think we could have a simple ASP.net site up quickly, but I'm concerned this would give us an overhead with setting up IIS, and also lock us into a Microsoft-only language. I'm currently exploring other projects I think could help this company that all tie into Java, so if we can leverage that language for this project, it would really help us in the long run. I don't know anything about PHP or JSP, or if there's something else that's better, and it doesn't look like they're going to hire a dedicated developer any time soon, so this would likely be my lovechild. Do we have some front-end developers here who can give insight on using Java in some way?
Foremost, I want the right tool for the job, even if it's ASP or some other Microsoft tool. But, I'm hoping to get some information from anyone here who has tried a front-end setup for SQL Server, and can point me in the right direction.
Not sure I much like the idea of the folks getting a report to be able to massage the data that goes into it. That's kinda like letting the fox guard the hen-house. That said, what kind of data are we talking about (general category should be sufficient)? And why isn't there an application that is driving data entry / update for it?
There are some kinds of data that are entirely managed by reporting teams, so if that's your use case, then you probably shouldn't get involved in leveraging skills that usually require significant expertise, such as Java and C#, because at that point, you're creating an application.... AND buying into continued maintenance thereof, and that has a price. If you ever depart, they'd have to hire significant expertise to fix anything that goes wrong. Easier to deploy a quick and dirty MS Access front end and keep the data on SQL Server. It's relatively easy to automate that front end with VBA, so the skill set needed to pick up where you leave off is not as high. It also keeps the workload required to develop down considerably over an equivalent web-based app. And after all, if you keep the data out of the Access file, you can distribute updates to the app by e-mail without having to worry about affecting data updates.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
December 19, 2016 at 11:35 am
Well for your first question yes if at all possible you would want a web client over a fat client, that'll save you any number of head aches having to configure, install and update local installations.
Why are you opposed to C#? If you're already using SQL Server you're already tied to the MS Stack anyways unless you're planning at some point to make the back end platform independent.
December 19, 2016 at 11:40 am
Hi Steve,
In my brevity, I think I left some details out. I don't believe this will be a case of users editing their reports--they'll input other data that will still go through an ETL. I imagine this is to take on a new project, and bring data we didn't have before. For data type, I can't say for certain because I haven't officially started yet. I do know the nature of the data is insurance/risk management, so I will need to keep security in mind.
You also have a really good point about Access, and I'll take it to heart. I'll look into it more to weigh the pros and cons. Are there significant security concerns with users distributing the database? In another life I worked on a team that converted some Access databases into SQL/ASP setups--I thought we were supposed to move away from it?
One ulterior motive I should clear is that I'm looking into leveraging Hadoop for some of their unstructured data (different discussion entirely), and since that uses Java it can help with that transition; my role will be primarily SQL developer, but I have a feeling they want me to do more than just write stored procedures.
Thanks again for your reply.
December 19, 2016 at 11:50 am
ZZ,
Thank you for your feedback about web front-end. I don't like C# because I feel it would lock our organization into an MS-only shop; but I'm not a skilled front-end developer, so I don't want to rule it out either. I just don't have enough information yet to make a recommendation to my employer.
December 19, 2016 at 12:03 pm
Any tool that can present an input form and execute a stored procedure call can be used for this. Actually, your existing reporting tool probably has limited capability for this. For example, SSRS can be used to create user input forms. Not only is it a relatively simple one-off for you in terms of development and deployment, but it provides more of a seamless experience for the end users; that is the input form supporting the reporting process is built into the reporting dashboard.
http://www.jasonyousef.com/2011/12/ssrs-can-be-also-used-for-user-inputs.html
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 19, 2016 at 12:44 pm
gdpollock (12/19/2016)
Hi Steve,In my brevity, I think I left some details out. I don't believe this will be a case of users editing their reports--they'll input other data that will still go through an ETL. I imagine this is to take on a new project, and bring data we didn't have before. For data type, I can't say for certain because I haven't officially started yet. I do know the nature of the data is insurance/risk management, so I will need to keep security in mind.
You also have a really good point about Access, and I'll take it to heart. I'll look into it more to weigh the pros and cons. Are there significant security concerns with users distributing the database? In another life I worked on a team that converted some Access databases into SQL/ASP setups--I thought we were supposed to move away from it?
One ulterior motive I should clear is that I'm looking into leveraging Hadoop for some of their unstructured data (different discussion entirely), and since that uses Java it can help with that transition; my role will be primarily SQL developer, but I have a feeling they want me to do more than just write stored procedures.
Thanks again for your reply.
Glad I could offer an alternative. Just be aware, that all you might need to bring Hadoop into the equation with the MS Access option is an ODBC driver, as Access could then link tables via that data source as well.
As to security with the MS Access databases, that can be handled entirely at the SQL Server level. You set up all the ODBC Data Sources to use Integrated Security. That way, the only viable use of the Access database is on a machine connected to the network and logged in as a user that has access rights to the data. You won't need to play the game of setting up users within MS Access.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply