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?
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.
TechEd is Microsoft’s premier conference for IT professionals and developers. The sold-out conference took place in Orlando, Florida last week.
If you didn’t attend, you can still watch many of the videos from the conference, including the keynotes and other highlights from each day. Visit the TechEd web site and learn about the latest in Microsoft technology.
The announcements this year are stunning with huge advances in Azure, Visual Studio .NET, SkyDrive, LightSwitch, Virtual Machines, and more.
LinkedIn offers many opportunities for professionals to interact with each other. There are many groups available for the Microsoft Access, Azure, SQL Server, and Visual Studio .NET communities. Here are some of the vibrant groups we’ve discovered:
FMS President Luke Chung will be attending and speaking at the three day Microsoft Access conference sponsored by the Portland Oregon Access User Group. Join him and other guest speakers including Alison Balter from InfoTech Services Group Inc., Armen Stein from J Street Technology, and Kevin Bell from Microsoft.
Luke will be speaking on the following topics:
FMS Products for Microsoft Access Developers and How they Make You Money
A few weeks ago, I discussed how Microsoft Access could link to databases hosted on SQL Azure. Here’s a new paper discussing How to Deploy Microsoft Access Databases Linked to a SQL Azure Database. Details on how this works and how to distribute your databases are covered. The people who receive your database don’t need a license of SQL Server but do need to have its ODBC driver installed on their machines.
We at FMS are very excited about cloud computing and started developing solutions using Microsoft Azure including SQL Azure well before it was released to the general public. We feel cloud computing represents the next big platform change in the software industry and the most significant transformation since the introduction of the Internet in the mid-1990’s. It will transform the internal hardware, application hosting, and data storage business the same way electric companies eliminated most organization’s need to generate their own electricity.
While there’s been lots of discussions of Azure with .NET and SQL Server, we also see lots of opportunities with Azure and the Microsoft Access/Excel/Office community. In fact, we’re busily working on a way to integrate Access data and files with the cloud. Meanwhile, we’d like to share some tips and techniques for linking Access databases directly to tables in SQL Azure. This opens up huge new opportunities to create and deploy Access databases using a more robust, cheaper, and highly scalable platform that is enterprise quality.
I look forward to your feedback on two new papers: