Glad you find the article helpful. The issue with the Authorized IPs is both good and bad. From a security perspective, the fixed IP address is obviously preferrable. As for small businesses, if they are using high bandwidth connections, it usually includes at least one fixed IP address. I know our local Verizon and Cox Cable business accounts include that.

Hi Ruud,
Glad you found the article helpful. Let me know how it goes.
Luke

Hi Bruce,

Not sure what you mean by 3rd party add-ons.

If you host data on SQL Azure, it's a SQL Database in the cloud and whoever you give permission to it could use it but that's not very standard. If it's Windows Azure with a .NET solution, then you have a web application that others could see and use. Depending on what you expose, maybe other programs could extend it, but that's no different from Microsoft hosting it or someone else.

Now if you create a .NET application on Azure, that application of course could use any web applications it can interact with using things like web service calls.

Does this help?

Hi Dale,

There are several differences between Azure and a traditional ISP hosted database. Based on my understanding, Azure automatically has the database mirrored so that there are three copies at all times. It's hosted at a highly secure location, robust platform, high bandwidth capacity, etc. It's also highly scalable so that multiple CPUs (on the Windows Azure side) can be added as needed. On the database side, the capacity can be increased economically up to 50 GB.

As for the authorized IP address, for security reasons, that's something one should have if the front end is from fixed locations. If you want to host a database that people can access from any location directly that may be a problem but that's not something we usually support. If you want to create a web site, Windows Azure can do that with a .NET application connected to SQL Azure. The .NET application can be available to anyone. It has the data access but the user wouldn't interact with the database directly, so the fixed IP address is a non-issue.

Hi Michael,

Thanks for your interest! I've followed up these two articles with another article discussing how to deploy Access databases that are linked to SQL Azure which should give you some additional details: www.fmsinc.com/.../...ked-azure-sql-databases.html

1. No. SQL Azure provides the tools you need. Downloads of the SQL Server programs are provided at the bottom of the Azure page: www.microsoft.com/.../default.aspx

2. You'll need to have at least the ODBC drivers installed which is covered in my deployment paper mentioned above.

3. You can use RDC on your machines as normal but you can't remote to the SQL Azure host.

4. SQL Azure is just like SQL Server but more scalable and managed. So you should be able to run as many jobs as you can with regular SQL Server.

5. With SQL Azure you're paying per database, not per server, so you can have as many as you'd like starting at $10 per month for the first gig.

6. Backups and restores need to be manually handled. SQL Azure automatically keeps three copies of your database at all times.

7. As discussed in my paper, you can link to it through the designated IP addresses you provide. We use a .NET web site and web pages to use SSL. I'll need to investigate how the secure certificates would be implemented for the SQL Azure databases for direct connections.

8. Yes, it's very scalable. For security reasons, each user's IP address needs to be registered though.

9. Yes.

10. Pricing includes both storage and bandwidth pricing and is on the Microsoft Azure site: www.microsoft.com/.../default.aspx.
Here are some numbers: $9.99 per database up to 1GB per month, $49.95 per database up to 5GB per month

11. See the link above for transfer pricing. It's pretty generous.

12. Total cost depends what you're doing. Think of it as electricity. The more you use, the more you pay. It's at least $10 a month for the first GB. You go from there.

Hopefully my papers already discuss how Azure differs from a database hosted on a single machine at an ISP. Basically, it's enterprise quality up-time support and scalabilty. It's replicated with three instances but all at the same location (some organizations may need the data in multiple sites).

Hi Edwin,

Glad you found my papers and the discussion here useful. Let me clarify some of the issues you've raised.

1. It's my undertstanding that SQL Azure replicates your database so there are three instances on separate machines. It's still one database, and you connect to it via SQL Server Management Studio like you normally would.

2. When an Access database (MDB or ACCDB) is linked to SQL Server, you can use DAO to work with your linked table like any other Access table. If you want to go to the SQL Server objects directly and not use your local linked tables, you'd use ADO. SQL Azure is no different than SQL Server for this.

3. I guess you'll need to test the responsiveness of SQL Azure vs. your local architecture. My presumption is that performance on your network is going to be faster than over the Internet. One advantage of having it on SQL Azure is that you can get to it from multiple locations. That may or may not matter in your case.

4. I'm not aware of any benchmarks. A proper comparison would be to see how it works against another ISP hosting a SQL Server database. Internal vs. external locaion hosting would probably be a test of the internet speed.

FYI, there are some things you don't get in SQL Azure that you would from regular SQL Server. Things like transaction logs, SQL plans, etc. aren't available.

Hope this helps.

I've updated my paper, www.fmsinc.com/.../...-to-azure-sql-database.html, to include a section about what you need to install on your machine to link to SQL Azure.

I originally stated that you need to have SQL Server 2008 R2 installed on your machine. That would work but is not accurate. After all, if you're using SQL Azure, you don't need to have SQL Server running as a server. However, you will need to manage the data you place on SQL Azure, so you'd want to have SQL Server Management Studio (SSMS) for SQL Server 2008 R2 installed. That comes with SQL Azure and is availablef for free from Microsoft: http://www.microsoft.com/express/database

Hope this clarifies what's needed.

I can see how requiring specific IP addresses in advance could be considered a hassle, but I would be uncomfortable allowing that in any Access applications we build.

One can't claim to have security while allowing a SQL Server database on the web let any IP address get to it. A more secure approach is to isolate SQL Server completely and have only web service calls (from a .NET application) control how people get to and from the database. That eliminates the use of a linked Access database but offers a web application that is useable from anywhere. Direct connections to the database would be reserved for the developers who would have a set of known IP addresses.

For people that want to connect to the database outside their office, one solution would be to have them use VPN and remotely run a machine in their office. That would have the known IP address. There are definitely tradeoffs but skimping on security could be very expensive after an incident occurs.

Hi Darren,

Maybe you're comfortable letting anyone connect to an Access database across your network. That may be fine, but when you place a SQL Server database on the web, it's considered best practice to restrict the IP addresses that can connect to it directly. That's a level of security that's built into SQL Server and something you should only weaken as needed. Yes, you can specify a range of IP addresses (not sure how wide it can be) but why would you do that if you didn't need to? Is it simply for your convenience? Do you believe there's no security threat? Maybe your data isn't confidential or valuable. In that case, you can simply use one of many web hosting sites that support SQL Server databases. They aren't the same level of security and up time as SQL Azure, but should be fine for what sounds like your needs.

Login and password can be hacked via brute force. Requiring the request to come from a specific IP address makes it significantly more difficult to hack.

That's just the way SQL Server stores tables and not specific to Azure. The dbo represents the owner name. You can have multiple tables with different owners and the same table names.

As you found, you can rename the linked table to anything you'd like in Access, so the Access side can still reference the same table name. In Access, with a little code, you could loop through the list of table names and if it starts with dbo_, rename the table without that prefix.

When you manually create a connection to the SQL Server table, what's the value of your connection string? You should be able to programmatically assign the same value.

Hi John,

Unfortunately, that's a limitation of the File DSN which is beyond the control of Access. You can link the tables directly within Access and store the login information in some sort of encrypted manner and programmatically open it when it launches, but that's a bit complicated and not entirely secure. I've asked some people at Microsoft for a preferred way of doing this but didn't get a reply.

Luke

Hi Chuck,

This works the same way your Access application would work linked to a SQL Server database located anywhere else. Yes, your front-end Access Jet database (ACCDB/MDB) links to the SQL Server tables, and your VBA code, queries, forms, reports, etc. work assuming they don't do anything that's a problem with SQL Server. If you use an ADP, you connect directly to it.

For security reasons, your SQL Azure database needs to be setup with the IP address of the sites that are going to link to it.

Luke

Hi Nick,

Are you saying SQL Azure is behaving differently from a regular SQL Server deployment of a database?

There would be a difference if you're moving lots of data between a web site vs. internal database on your network. SQL Azure should be compared to hosted databases that need to be used from multiple sites not on the same network.

Hi Jen,

So much of a decision is based on what your objectives are. If you're creating a web site, then .NET certainly makes sense. If you're upsizing an Access application to SQL Server, you simply need to make sure you're doing it for the right reasons and make the change. If you use linked tables rather than an ADP, the migration is pretty straightforward if the table and field names don't violate SQL Server constraints.

The other questions on approaches to creating a .NET application depend on the specific application. Are you making a Windows or web app? We like MVC.

Luke

If the front-end remains in an Access MDB/ACCDB file, the queries should all still work.

The other issues you've raised about intergration with other desktop applications is a general question of building a Windows or web application, which is independent of Access.

The advantage of changing a web app in one place is definitely addresses some deployment issues. We get around that in Access with our Total Access Startup program (www.fmsinc.com/.../VersionLauncher.asp) that also lets us Access centrally, so new versions of our application are automatically deployed and we can control which version of Access they launch.

The issue of SQL Azure is just a question of where you want your SQL Server database. The application needs to be updated to use a SQL Server database. You can then decide where that goes. For security reasons, a SQL Azure database needs to know the IP addresses that will call it, so it may not work if people are using the database on-the-road with random IP connections. No problem with fixed locations. This would also be an issue if you host your SQL Server database elsewhere. You could allow it to be accessible from any IP address, but that's a security hole. This is a non-issue if you create a web application because the web .NET application would be connected to the SQL Server database from a fixed location that wouldn't change and is independent of the user's location.

Let me know if you'd like to talk to our Professional Solutions Group (http://www.fmsinc.com/Consulting). They can help you make the transition with upsizing your existing Access application to SQL Server, to rewriting it on .NET for Windows and/or the web.