FMS President Luke Chung was a presenter at the Virtual Microsoft Access DevCon 2020 in Vienna, Austria on April 23, 2020. He gave a presentation called “Remote access to Access“, which is available for everyone to watch.
Remote Desktop and RemoteApp let your users run Access applications without having to installing anything on their local machine. That includes Access, the database, and any related programs. It lets users run the program across the network or Internet, from their PC or even a Mac. There are different approaches depending on whether the host is internal or in the cloud, and for backend databases in Access and Microsoft SQL Server.
Special thanks to Microsoft Access MVP Karl Donaubauer, who hosted the fourth annual Access DevCon and made it an online event during the COVID-19 lockdown.
Having Microsoft Azure host SQL Server databases on their servers is very cost-effective and efficient. Within minutes, one can have a SQL Server database hosted in the cloud and available to applications on the cloud or on premise.
As with all cloud resources, and especially databases, security is a huge concern. Fortunately, SQL Azure includes features to restrict what can connect to your database server but you need to know how to use them and realize that the default settings do not protect you best.
Setting Firewalls and Virtual Networks
This is an important feature for cloud solutions so that only permitted sources are allowed to get data from your server and databases. You can set the IP Addresses you allow at the database level and server level. The database settings take precedence over the server settings.
Cannot Open Server Error
If you try to connect to the database from an unauthorized IP address, it triggers an error like this:
Cannot open server ‘ServerName’ requested by the login. Client with IP address ‘220.127.116.11’. is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.
By Default, All Azure Resources can Connect to Your Database
By default, all Azure resources can connect to your server and databases hosted on Azure:
Allowing All Azure Services to Connect to Your Server is a Huge Security Hole!
If you “Allow access to Azure Services” set to On, you create a huge security hole for your server and every database in it. Not only can all your resources connect to your databases, Any Azure resource from any organization can connect to your database.
This setting is NOT restricted to the Azure resources in your subscription. It’d be nice to restrict permissions to the current subscription or list of subscriptions but that’s not possible. It’s everything on all of Microsoft Azure or you need to specify each IP address.
Turn Off Permissions to All Azure Services
Set the permissions to OFF to disallow all Azure services to connect to your SQL server:
Explicitly Specify the IP Addresses Allowed
To avoid the ability of rogue Azure resources from breaching your database security, you need to manually specify the IP Address of every resource that may connect to your server and databases. This can be a real pain.
Microsoft Azure lets you economically and quickly host enterprise quality SQL Server databases in the cloud. The cost of each database is relatively modest.
Managing Resources and Costs for Individual Databases
However, as you add more databases, larger databases, and/or databases that require more resources, costs increase. Providing more resources to a database is helpful when it demands it, but when users aren’t on it or during non-business hours, it may be wasted capacity. Even during business hours, one can have some databases being utilized more than others at unpredictable levels.
Pooled Resources Across Multiple Databases
Fortunately, Azure offers an Elastic Pool option to share resources across multiple databases. If the demand on your databases is inconsistent (spiky), you can provide a high level of capacity that’s available to the most demanding database while allowing other databases to share those abundant resources when needed.
You no longer need to set the limits of each database,
You are not charged a per database monthly fee which is great for supporting lightly used databases.
Migrating Existing SQL Server Databases to Elastic Pool
Microsoft provides information on SQL Elastic Pools but does not explain how to convert existing databases to an Elastic Pool.
FMS President Luke Chung wrote a new paper with step-by-step instructions on how to convert existing SQL Server databases on Azure to an Elastic Pool without the need to change the database connection strings:
Microsoft Azure lets you easily create and deploy enterprise quality SQL Server on the cloud and scale it to suit your application’s needs. From the SQL Server database’s Azure dashboard, you can see the Database Transaction Unit (DTU) usage against the specified DTU limit for the database.
One Hour Usage Graph
This is what we saw for usage over one hour. The cyan line across the top is the DTU limit. The dark blue line is the DTU used. The limit is what you pay, so it’s important to scale it to what the application needs.
One Hour Azure SQL Server DTU use versus limit
While everything seemed fine at the weekly level, looking at the hourly graph gave us a shock. It looks like the database is maxed out for most of the hour. It seems conclusive that we need to increase our DTU level.
65 Minute Graph
But when we set the range to 65 minutes and saw this:
65-minute Azure SQL Server DTU use versus limit
These are completely different displays of the same period of time. The 65 minute graph never hits the maximum DTU. What’s going on?
We’ve completely revamped our Microsoft Access to SQL Server Upsizing Resource Center. Find links to all our related white papers and Microsoft resources to help you with the SQL Server upsizing process, from deciding why and which Microsoft Access databases to upsize, the different options, and using SQL Server Express.
We have several new and updated resources:
Microsoft SQL Server Express: Version Comparison Matrix and Free Downloads
For the first time, all the different versions of SQL Server Express from 2005 to 2014 are shown, compared, and referenced with download links. This content required extensive research to document the details of which operating system each SQL Server version supports. Just because Microsoft web pages list the versions they support doesn’t necessarily mean it works when you actually try to install it.
When and How to Upsize Microsoft Access Databases to SQL Server
The original version of this was written for Microsoft when they selected us for a joint national campaign a decade ago to promote Microsoft Access to SQL Server Upsizing. We’ve updated it to better explain why and why not people should upsize their Access databases and an overview of what the options are and what to do.
Microsoft Office 365 is Microsoft’s new and popular way to license the Office products for online and desktop use. It also includes hosted Exchange for email, SharePoint, OneDrive for shared hard disk files, and the communications package Lync. The cloud based platform means Microsoft takes care of the system administration to update versions, apply security patches, monitor usage, ensure uptime and connectivity, and address hardware problems.
Let Microsoft Take Care of Exchange and Email
If you are still hosting your own Exchange Server in your facility, it’s time to consider outsourcing so Microsoft can worry about the versions, patches, hardware failures, and other maintenance chores. Microsoft will also host it in a real data center with reliable power sources, battery backups, multiple internet trunk lines, and enterprise quality physical security.
If you’re already outsourcing your email/Exchange hosting, Office 365 is a wonderful alternative and lets Microsoft deal with the challenges of keeping email up and running 24/7/365.
Includes Desktop Copies of Microsoft Office
If an option includes the Windows copies of Office, you can install on your local machine Office 2013 copies of Microsoft Access, Excel, OneNote, Outlook, PowerPoint, Publisher, Word, Lync, and InfoPath. This lets you have both the online versions of Office and the traditional non-Internet dependent local copy.
Office 365 with SharePoint and Access Web Apps with SQL Azure
With Office 365, the hassles of hosting and maintaining your own SharePoint site is gone. Microsoft takes care of that for you and lets you create both private and public web sites.
You can also enable Access Web Apps to create simple database solutions with data automatically hosted in SQL Server (SQL Azure). The data can also be shared with other applications such as the desktop version of Microsoft Access.
Office 365 Options
There are many options based on your situation:
Business Plans with special pricing for Small Businesses (< 25 users), Midsize (< 300 users) and Enterprise (unlimited)
Non-Profit Plans (Microsoft offers free licenses to qualified 501c(3) organizations)
Office Pro Plus Trial – 25 licenses (Details)
This is the traditional Office on the desktop without the online services. Rather than buying the licenses upfront, Microsoft now offers the ability to pay for it on a monthly basis for $12 and install it on up to 5 machines.
Trial for Microsoft Dynamics
We are also pleased to extend Microsoft’s trial offer for their Dynamics CRM system
We are very pleased to announce our new technical support site (http://support.fmsinc.com) to provide forums and the ability to submit technical support inquiries.
Our new site lets you submit requests and respond to them via email with our support team. It also lets you visit our site to check the status of your requests and their entire chain of communications. You can login directly or use affiliated logins from Facebook, Google, and Twitter.
You can also read information and ask questions to the community on topics related to Microsoft Access, Visual Studio, LightSwitch, and SQL Server. We hope you’ll join us.
Last month I spoke at the Portland Access Users Group Conference at Silver Falls State Park. I gave a presentation introducing Visual Studio LightSwitch and how it could be used for SQL Server applications deployed on a variety of platforms. As a follow-up, I’ve created a summary matrix and discussion that highlights the features and limitations of the variety of platforms from Microsoft Access, Visual Studio LightSwitch, and Visual Studio.
Microsoft Access started at the beginning of the Windows revolution 20+ years ago and became the most popular database of all time. More recently, additional technologies have become significant, so it behooves the Microsoft Access community to be aware of the trends and options.
Ultimately, it’s about being able to create solutions that help you and/or your users accomplish their mission. Sometimes the user’s platform is critical, sometimes, it’s the data source, and other times it’s the permissions you have to deploy a solution. A variety of platforms and options are available with benefits and limitations with each. Meanwhile, Microsoft Access is also evolving with their latest Access 2013 version offering new web based solutions.
Sentinel Visualzer helps analysts mine their data to find hidden relationships among people, places and events. Built with Visual Studio .NET on a SQL Server database, Sentinel Visualizer provdes advanced data visualization through link analysis, geospatial mapping, timelines, social network analysis (SNA), advanced filtering and decluttering, and many other tools to maximize the value of data.
As the rampage and manhunt in Boston, Cambridge, and Watertown transpired, MIT Technology Review published an article that mentioned our Sentinel Visualizer product. In the April 19, 2013 article, David Talbot describes the rise of technology to detect the activities of criminals over the Internet and connect seemingly unrelated people, places and things. For more information, visit Building a Picture of the Boston Marathon Bombing Suspects through Social Network Analysis.
In this March 10th Washington Post article, Alexandria tailor weaves custom solution for taking orders, a local firm is mentioned having struggled with Microsoft Access and being forced to migrate to a new system due to problems with their Access database. In particular, their database couldn’t provide multiuser support and lost data when more than one person used it.
Unfortunately, stories like this perpetuate the myth that Microsoft Access features are limited rather than the lack of skills of the developer who tried to customize it. It’s a shame the business owner and developer weren’t aware that Access could address the multiuser issues they encountered; thereby saving time, money, and headaches from having to migrate to a new platform.
Microsoft Access is Multiuser Ready
The reality is that Microsoft Access is fully capable of providing multiuser support if it’s designed properly. For basic database solutions with under 1GB of data (maximum 2GB), Access comfortably supports up to 200 simultaneous users with a properly designed solution.
As the number of users and data expands, Access makes it relatively easy to migrate the data storage from an Access database to SQL Server, while maintaing the application layer (forms, reports, code, etc.) in Access. This also lets you share the SQL Server data on web sites and other platforms. That means supporting two users in a tailor shop would be trivial with MS Access.
Split Database Architecture for Multiuser Solutions
People sometimes treat Access databases like Excel spreadsheets and want each user to open and close the same file. That’s not the way to support multiuser data sharing in Access. A split database architecture is needed to separate the application layer from the shared database. Each user gets their own copy of the front-end database application that links to the tables in the shared database.
While having a web application has its role and advantages, the article mentions their internet connection isn’t reliable and their business is negatively impacted when that happens. That’s an unfortunate result of their new platform. There are ways to create hybrid solutions to provide on premises support with shared web solutions, so these issues need to be considered when creating business critical solutions.
Using Microsoft Access Strategically
Small businesses often have very limited budgets and time to understand technological options. Completely eliminating Office and Access as viable solutions for incorrect reasons is wasteful. Microsoft Access addresses an important segment of database needs, and offers small businesses and information workers the ability to make modifications and extensions that other platforms do not allow so easily. Understanding where and how to use Microsoft Access effectively along with its limitations offers organizations of all sizes a competitive advantage. We’ve helped many small businesses, non-profits, and multi-national companies properly use this technology very effectively. Here’s our article on Microsoft Access within an Organization’s Database Strategy that discusses this in more detail.
There are lots of terrible applications created on every technology platform whether it’s Microsoft Access, Excel, Visual Studio, Java, Oracle, SAP, etc. In this case, the skills of the Access developer were clearly lacking. Getting that confused with the technology is misguided.
For additional resources to build robust Microsoft Access solutions and understand what’s possible, visit our: