Confusions about migration Access 2010 >> SQL Server 2010

Question

I have an application I've been developing in Access 2010. I should start by saying that when I started this project in January, I was a raw beginner with Access. I've taken a few on-line courses, read some books, gotten a lot of help from folks on the forums, learned a lot about code over the past 6 months, and as a result have a pretty nice little application.

Now, however, it's time to go online with it. The application will have about 30 users who will upload small amounts of data once or twice a week, and 4 to 6 admin folk who will be running reports and down-loading data. However, the users are spread over 3 counties and the back end will be mounted on a web-based server. I've been told that Access will not perform as a back end under these circumstances and I need to migrate the back end to SQL Server.

Since we're only talking a dozen or so tables and nothing else, I expected this to be reasonably simple. I downloaded SQL Server Express 2010, and my next step was intended to create a back end on my own machine so I could experiment and learn how it works and how to link data from the Front End before actually going on-line with it.

But it turns out the program is so arcane compared to Access that I'm overwhelmed. Even the Migration Assistant is virtually incomphrehensible. I don't know where to start and am having trouble even determining what questions to ask. To start with:

1. Is it even possible to do what I'm trying to do (create a test back end on my own computer) or do I need to create it on the Server?
2. If so, does SQL Server itself need to be intalled on the server? This seems like a ridiculous question, but I ask because when I tried to create a test DB on my computer, I couldn't even find where the file was stored. Does it store the file within the program in some way?
3. Is there any really good tutorial that will take a raw beginner through this process? What I've found so far on the help menu is (again) pretty arcane, especially if you don't know which questions to ask.

Thanks so much

---

—nick

Answer 1

Yes. This can be confusing.

MS Access is a desktop product. The Access executable runs locally each computer, and each instance of Access reaches out and grabs the Access datafile. Sometimes multiple people can work with the data at once without blocking each other. But as the number of users increases, they tend to have to wait.

SQL Server is a server product. Only one SQL Server executable is necessary. It runs (in your case) on the server. Only that one SQL Server gets to get data from the data file. Every user asks that one SQL Server for the data. SQL Server doesn't block itself nearly as often.

SQL Server keeps the data tables. You will want to move the data from Access to SQL Server.

SQL Server doesn't have the same concept of Queries that Access has. Some Access queries are stored select statements. In SQL Server, those are called views. These transfer from Access to SQL Server pretty easily. If you have trouble, look at the SQL descriptions of the Access Queries that appears in the Access Query Editor. The same Query Editor exists in SQL Server Management Studio.

Some Access queries (the action queries) change data. These don't have a direct parallel in SQL Server. Some can be rewritten as stored procedures. Again, checking the SQL section of the query editor can help you figure out what's going on. These are the items that will probably give you the most trouble.

MS Access forms have no equivalent in SQL Server. SQL Server handles data, not the presentation of data. Usually when you migrate, you just use the forms in Access. That is, each person runs Access, and the forms in Access connect to SQL Server to get the data. So everyone runs MS Access locally. But the data is centralized in SQL Server.

SQL Server does have a reporting capabilty. But if the clients are all running MS Access for the forms, and if you have already writen the reports in Access, just use the Access reports. The client runs Access locally. The reports connect to SQL Server to get the latest centralized data.

Hope this gets you started. Speaking of which, here is a short tutorial if you are having trouble Tutorial: Getting Started with the Database Engine

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

Thank you for the overview, Rick. I will look at the tutorial you suggested.

In my case (at least as I presently understand it) the only thing that will need to exist in my back end are the tables. It seemed like getting those to SQL Server should be a fairly simepl proccess, but I'm grid-locked, can't figure how to get started.

Is it possible to do this (as a test) on my own computer before I mount the back end on the server? And does SQL Server store the data in a seperate file, the way most programs do? Or is it more like an e-mail client, where everything is store inside the program? Assuming that it's a seperate file, what file extension does it use?

Thanks again

---

—nick

Answer 3

The tutorial appears like it will give me what I need to get started. . . However, it does ask questions I don't know the answers to. For instance, it says " in the Login name box, type a Windows login in the format <domain>\<login>." What does this mean? is "domain" like a username? "login" a password? and are the <> and \ marks literal?

---

—nick

Answer 4

hi,

you should start reading the provided material linked by Rick..

as he stated, SQL Server is different from Access... and as indicated, you should not compare the "graphical part" of Access (the Forms/Reports/..) to SQL Server\SQLExpress... very loosely speaking, SQL Server is a database engine, that can be somehow compared to the database engine of Access (but only to that part of the product)... the engine must be installed on a machine that will act as the "server" for all the requiring clients... all clients must connect to that "server" (not to the database's files as Access does), and must query that "server" for all the relevant stuff... the "server" will perform all the required operations, querying/updating data and returning to the caller... the "server" will be the only responsible for all the databases management, in fact only 1 SQL Server instance per time can access a specified filebased database... a database is both a logical object, registered in the instance, and a physical object, composed by at least 2 physical files... but only the "server" will interact with those files... those files usually are a (primary) data file, with an .Mdf extension, and a transaction log file with .Ldf extension... they are usually stored in the \Program Files\Microsoft SQL Server\$InstanceName$\MSSQL\Data folder, where $InstanceName$ is the name of the installed SQL Server instance... you can have more than a single instance installed per machine, and each one corresponds to a "server", with particular settings, version or service pack level and the like... but you can store those files wherever you like on the local disk, you are only required to provide enought NTFS permissions to the Windows account running the SQL Server service, as SQL Server runs as a service (and not as an application) on the server machine...

again, very loosely speaking, being a "service" providing data access, it requires you (and every client) to authenticates yourself before starting data access.. this task is performed via the authentication phase... to be granted authentication permission, the "person" (actually not the specific person, but just to start understanding) must be granted that permission... this is devoted to the "logins" (now server principals objects), where an administrator grants "Nick" that permissions actually granting "Nick's login" the permission it self... Nick's login can be both a Windows's account (the one you use to log in in Windows or in your domain) or a standard SQL Server login... usually the Windows (or integrated security) authentication is preferrable as more secure from several points of view, and in this case your connection automatically provides (directly or via the Domain Controller involved in the local network) the actual info about "who is trying to connect to SQL Server"... in the other case, the "standard SQL Server authentication" mode, you have to provide a LoginName and relative Password to be checked... once the info are provided to the SQL Server instance, it checks the WinNT login or group the login is member of (integrated security mode) or the LoginName + Password (standard SQL Server security mode) are "valid"... if this is the case, the authentication is granted... by default, SQL Server only allows "integrated security" authentication, but you can change that setting accessing, say via SQL Server Management Studio, the server's property and modifying it... in the case of SQL Server Management Studio, select the "main" Node in the Object's Explorer, rx click->Properties and modifying in the "Security" pane the "Server authentication" as desired, in this case setting it to "SQL Server and Windows Authentication mode" to allow "mixed mode authentication" (those both modes)... always by default, all local administrators are usually granted authentication permissions at install time... but then you should start reading about the security implementation of SQL Server, becouse we are not done with it... there's plenty of still to understand :)

the second authentication phase is at database level, where each login will be granted database access mapping to a database user... registered users can access the specified database as they will be by default member of the "public" database role... but usually "public members" will not be authorized al lot of permissions on database objects..
the mapping is performed in the login<->database user relation.. so, the second phase regards a databa sesecurity implementation... in order to access a specified database the simple login existance does not provide database access, but a (database) user must be mapped to the corresponding login.. and is about verifying that at each object level (including database, tables, views, columns, procedures and so on) the Login/User association is permitted access to...
so the objects access permissions comes to play... where you define them granting users/roles SELECT/DELETE/EXECUTE (and so on) privileges at an object level (or column level for tables and views)..

again... all this very loosely speaking :)

regards

---

http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

Answer 5

So I have read through the tutorial. It was helpful but left some of my initial questions unanswered. Perhaps I understand enough to ask them again more clearly:

1. The first is the question I asked above:  in the statement "enter login in the format <domain>\<login>," what does "domain" mean? I figured out that in my present circumstance, "domain" is the name of the computer I'm working on. But what would it be under other circumstances— for instance, if the back end were on a server?
2. Much of the tutorial was devoted to connecting from another computer, and so I wansn't able to do those steps yet. That again raise the question for me: is it even possible to have front end (In Access) and back end (in SQL Server) on the same unit and really be able to test the system out? Or can that only be done when the back end is mounted on the server (which in my case will be on the cloud)?
3. Will it be possible to create a file (or set of files, if that's what's needed) using the SQL Server installation on my computer that contain the back end data for my system, and then later move that file (or those files) to the server? Or does SQL Server itself need to be installed on the Server, and create the files there?
4. How do I get my tables from my ACCDB file into SQL Server?
5. Once I've got them there, how do I access them?
6. How do I locate them to link the front end files to them?

Thanks

---

—nick

Answer 6

1. If you are setting up your system in a company XYZ, the company network will have a name call a domain. People don't log into Windows as Joe. They login as XYZCompany\Joe and then provide their password. The company network has a "domain controller" that does the authentication of the password. For a home machine, you don't normally have a domain. Your computer is in a "workgroup" which probably is just that single computer. You could say, you have a domain that consist of one computer. Authentication of the password when you login, is done by that computer.

Since you aren't familiar with the domain, I'm guessing you will not be placing this SQL Server on a domain. In that case you will be able to use Windows Authentication only when you are physically using the computer that is running SQL Server. All other people you want to connect to SQL Server will need "SQL Server authentication" logins (often called just SQL Server logins). The point is, that SQL Server keeps track of the password for the account, not the domain controller on the network.

2. Yes, you can install both SQL Server and Access on the same computer to develop and test the arrangement. Access will connect to the SQL Server to get the data from the SQL Server tables.

3. You can create the SQL Server database on one computer and then move it to SQL Server running on another computer. A big organization would make backup of the database, and restore the backup to another computer. In your case, I would stop SQL Server on your first computer (because it has an exclusive lock on the files). And then copy the data file (it has an extension .mdf) and the transaction log file (is has an extention .ldf) over to the other computer. Then, in SQL Server, you attach the database files to the new instance of SQL Server.

4. In Access, there is a Wizard to move the data. I'm looking at Access 2010, and On the Database Tools tab there is a button on the toolbar. Other versions might have it somewhere else. Access help will tell how to start the wizard. Or, you can create the tables in Access, there is some way you can tell Access to put the table on SQL Server. But I forget how.

5 and 6. After the tables are in SQL Server, in Access go to the External Data tab, and select ODBC database. Complete the wizard to tell Access where the data is. At some point it will ask for the SQL Server name. That is either your computer name (for example NICKSLAPTOP), or if you installed the free SQL Server Express edition, the name of the SQL Server would be something like NICKSLAPTOP\SQLEXPRESS

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 7

Thank you Rick, that's very helpful.

I want to clarify one thing: this means then that SQL Server will need to be installed on the server, right?And if the Server is "on the cloud," is this possible?

I tried using the Migration Assistant to move my tables. Under "Server" I put in the name of my computer, since I'm trying to install front end and back end on the same unit (temporarily) to better understand the process and test my connections. I got the following error message:

Connection to SQL Server failed.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

What does this actually mean? Does it mean that computer name does not equate with server name?

Or is there something I need to do first in SQL Server, to allow connection, before I can use the migration assistant?

thanks again!

---

—nick

Answer 8

On a computer named COMP1 the SQL Server running on that computer will be named COMP1 and can be reached by using that name. Unless lthe instance of SQL Server is installed as a "named instance". Then, if the instance is named BBB, the SQL Server can be reached by using the name COMP1\BBB If you installed SQL Server Express edition, the default name is SQLEXPRESS, so COMP1\SQLEXPRESS might do the trick.

There are a number of things that can block this. The Database Engine might not be started. To connect to a named instance the SQL Server Browser service needs to be started. Or the Database Engine might not be listening on the same protocol used by the client. The tutorial Tutorial: Getting Started with the Database Engine that I pointed you to earlier is the best place for someone new. For more complex problems, How to Troubleshoot Connecting to the SQL Server Database Engine

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty