UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Move From Sharepoint To Azure., SharePoint 2013    
 
   
blueman2
post Nov 12 2018, 12:42 PM
Post#1



Posts: 228
Joined: 15-November 14



I've posted before about performance troubles with my access database back end that's hosted on sharepoint. The consensus is that I should move it to azure. The opinion of people in this forum, as well as someone from SharePoint is that our database is getting throttled and that this will get worse before it get's better.

Briefly: There are only two of us using our own desktop versions of an Access 2013 database from two different locations. The two back ends are linked to sharepoints lists, resulting in linked tables.
The database is not huge. Only 3 of the lists/tables approach 2500 records.

Because of the performance problems, I need another solution. Apparently Azure provides that. The problem is that while I built a somewhat complex database and was able to link to it on sharepoint, I know nothing about SQL. So what are the steps involved in getting what's now on sharepoint, on to Azure? And knowing Access to some degree, is this something I can do through Access on my own, or is it somehow something that can be fairly easily done when logged into Azure?

Thanks in advance
Go to the top of the page
 
MadPiet
post Nov 12 2018, 01:18 PM
Post#2



Posts: 3,325
Joined: 27-February 09



I don't think there's enough information here to comment sensibly. Could you post an empty copy of your database? How big is your database right now? (in MB). One thing you could do if it's approaching the Access size limits is put it on a free version of SQL Server, and it would be local to your network. The problem is that as you move to a more enterprise-level backend database, the complexity goes up. So make sure that's what you need before you upsize and find that it didn't solve the problem.

If you split the front end (forms, macros, modules) and backend (tables), then your problems might go away. But right now it's impossible to determine exactly what's wrong. What is not working?
Go to the top of the page
 
blueman2
post Nov 12 2018, 01:28 PM
Post#3



Posts: 228
Joined: 15-November 14



Thanks, but actually it's already split as I mentioned above. There is no problem with the database, the problem is SharePoint throttling. I've already posted about that and have received multiple answers here (also elsewhere): https://social.msdn.microsoft.com/Forums/of...ee-aa05878e441a


The question is about what steps are involved in moving from sharepoint to azure.

Go to the top of the page
 
GroverParkGeorge
post Nov 12 2018, 01:48 PM
Post#4


UA Admin
Posts: 35,841
Joined: 20-June 02
From: Newcastle, WA


As noted, there are a LOT of considerations.

First, Azure SQL is a SQL Server database, albeit tailored to run in the cloud environment and subject to some differences as a result.

So, you could start, as suggested, with a local copy of SQL Server Express to work out the migration, and then move the data from there to Azure.

If you decide you need to use Azure, you'll have to sign up for an Azure account.

BTW: You do need to consider whether you really want a "cloud" solution or not. If both you are inside a LAN, and can install a copy of SQL Server Express, perhaps that's less complicated. It depends, though, on your environment and requirements.

From there, what I would probably do is simply convert the SharePoint lists to local Access tables in an accdb and then re-export those tables into the SQL Server/SQL Azure database.

Prepare to learn a lot about managing a SQL database.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
blueman2
post Nov 12 2018, 02:12 PM
Post#5



Posts: 228
Joined: 15-November 14



Thanks GPG, that gets me started. We are in two separate locations miles apart so cloud-based is imperative. But now I understand what you mean about testing with a local copy. I understand there is some microsoft tool that will allow me to do the conversion to SQL. I'm not exactly sure what it is or where to find it but if you have any thoughts about being able to do the conversion easily, pleas let me know. Thanks again
Go to the top of the page
 
GroverParkGeorge
post Nov 12 2018, 06:14 PM
Post#6


UA Admin
Posts: 35,841
Joined: 20-June 02
From: Newcastle, WA


blush.gif
I forgot to provide the links to the tools you will need.

SQL Server Migration Assistant for ACCESS

SQL Server Management Studio

Both are free.

Once you have an Azure account where you can deploy the SQL Azure database, you can pull the SP lists into an Access accdb and then use the SSMA to migrate them to the SQL Azure db.

You can manage the tables, etc. in the SQL Azure database with SSMS.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RobKoelmans
post Nov 13 2018, 04:38 AM
Post#7



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


Hi,
Although you can connect to ListItems in SharePoint it's never meant as a main datasource to Access Applications i.m.o. AzureSQL is better for that but not very useful if you use 'out-of-the-box functionality within Access. Just opening a linked table and scrolling down or filtering by a string value will give you an hourglass for minutes. You have to manage an ado connection by yourself in code and then it's working fantastically. The other option is that you use Access Web Services in your SharePoint and redevelop your application in Access 2013. You will have to check whether Access Web Services is deployed in your companies SharePoint.

There are companies that deploy Access Web Apps in the cloud. Of which mine is one, so I'm not sure I'm allowed to mention this.

Kind regards,
Rob
Go to the top of the page
 
blueman2
post Nov 13 2018, 08:46 AM
Post#8



Posts: 228
Joined: 15-November 14



Thanks again GPG,

One simple question that I don't seem to be able to find any documentation for: when I want to convert my sharepoint lists to local tables, it seems like the only way to do it is to right click on individual tables and select : Convert to Local Tables" is there a way to just convert all tables at once?

Also, once they are converted, I assume that it's desirable (Before migrating to SQL) to get rid off all the extraneous columns that Sharepoint seems to add like "Created By", "workflow instance". "URL path" etc. These were never used within the Access database but seemed to have been created when the tables were first uploaded to Sharepoint,
Go to the top of the page
 
GroverParkGeorge
post Nov 13 2018, 10:21 AM
Post#9


UA Admin
Posts: 35,841
Joined: 20-June 02
From: Newcastle, WA


Two things. I have a tendency to overlook the option of Access Web Apps, which companies like Rob's support. It's a good choice under the right conditions. Microsoft no longer supports them via O365, but on-premises SharePoint is a very viable option.

And yes, as far as I know converting SP lists to local tables tends to be pretty manual. And you'll definitely want to clean them up, as you suggest.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
blueman2
post Nov 13 2018, 12:11 PM
Post#10



Posts: 228
Joined: 15-November 14



Thanks again.

As far as the web based option goes that Rob mentioned. At this point in time that's not an option for me. Though I built this database, which is fairly intricate, over the last 6 years, I'm not a database designer and VBA, though I know a handful of things, is not something I really have knowledge to speak of. Right now, other than the issue with sharepoint, we have a fully functioning database that works for us. There are only two of us using it, so it is a small yet busy company and having the database helps maintain that ratio. Since it's just two of us, we both have a lot of hats to wear so to spend time learning how to set up something completely new is not going to work. I need to understand what's going on with the database so that I can do a fix if need be. As you can figure, there's no IT guy on our staff. So again, unless converting this to a web based application approaches plug n play, it's not something we can do right now. I'll let Rob answer that if he wants.


But I did have a question about the addition columns that SharePoint added to the tables. I can see them in design mode and in the lists. Some of them show in datasheet view but most of them don't. I just want to know whether they will still be present when the lists are converted to local tables. And if so, should I delete those columns on the sharepoint list or just delete the fields once they become local tables.


Thanks

Go to the top of the page
 
GroverParkGeorge
post Nov 13 2018, 12:13 PM
Post#11


UA Admin
Posts: 35,841
Joined: 20-June 02
From: Newcastle, WA


Don't mess with them on the SharePoint side.

Modify them only after you convert them to local tables.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
blueman2
post Nov 13 2018, 03:23 PM
Post#12



Posts: 228
Joined: 15-November 14



thanks!
Go to the top of the page
 
RobKoelmans
post Nov 14 2018, 06:10 AM
Post#13



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


Hi Blueman,
Yes, this definitely is (not approaches) plug and play. You can set it up for yourself quite easily but I can do it for you too. You can decide when it's up and running. no cure, no pay and it's very cheap. And you'll agree there's no lock-in whatsoever. We have some very nice references (besides in the Netherlands in the UK and Australia) that will be happy to speak out for us (I can tell in advance smile.gif).


The bigger thing will be getting you to integrate PowerApps, Flow and (since recently) Access Desktop later on if that appears useful in your case. The two customers from the UK and Australia are perfectly happy with what they have in AWA though. They only connect with Excel for themselves.

Kind regards,
Rob
Go to the top of the page
 
blueman2
post Nov 14 2018, 08:46 AM
Post#14



Posts: 228
Joined: 15-November 14



Thanks
It sounds promising. I'm hanging on to this info.
Go to the top of the page
 
Minty
post Nov 14 2018, 11:34 AM
Post#15



Posts: 312
Joined: 5-July 16
From: UK - Wiltshire


If there are only two of you and you have any sort of server set up, have you considered storing everything in house and using remote desktop ?
I think it would be cheaper (Already mentioned free SQL Server Express) and a simple RDP set up and you would be done.

It would scale well for more users as well
Go to the top of the page
 
RobKoelmans
post Nov 14 2018, 01:15 PM
Post#16



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


Yes, running Access in runtime is quite attractive. As soon as you need MS-office for anything in Remote Desktop it becomes quite expensive. It became a little better the past two years due to pressure by O365 resellers when distributers like Ingram Micro started selling O365 to resellers. But you still need the most expensive o365 license to be allowed to run MS-Office in Remote Desktop. If you don't have what they call MS-Office 365 nowadays, but a standard license, running it in Remote Desktop is disproportional expensive if you read licensing rules correctly (I'm afraid not many people do though).

Another option is starting and taking over your desktop pc's. Much, much cheaper.
But - as said - running Access applications in Runtime costs you only remote desktop (and a sharepont CAL in your case. Anything you can't shut down without your application stop working, should be paid for is the rule). Exception on this exception: SharePoint Enterprise wit the public connector.
Rob
Go to the top of the page
 
RobKoelmans
post Nov 15 2018, 02:49 AM
Post#17



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


Hi Blueman,
I couldn't send you another pm before tomorrow (there seams to be a limit on that). Here's what I tried to sent.

Kind regards,
Rob

Regarding the database, you're right. You can connect your desktop access application with the database. See general info on internet for information on connecting to an acccess web app.
Googling "database connection information access web apps" will give you for instance: http://accesshosting.com/how-to-connect-to...-access-web-app

I'll be out of office today and tomorrow but - if you need further guidance - you can contact use https://www.metamicro.nl/nl-nl/Home/Contact



Link: https://www.metamicro.nl

Then: "Meer informatie" underneath AWA voor Office365 Flow en PowerApps.
Then: Maak hier uw eigen organisatie aan.
When you've gone through the process, click Log in in the right top.
After you've logged in, right click on your name in the right and go to My Organisations.

When you see your organization, open the site collection, go to site contents, create an app and select ms-access web app.
Kind regards,
Rob
Go to the top of the page
 
blueman2
post Nov 15 2018, 08:46 AM
Post#18



Posts: 228
Joined: 15-November 14



Thanks, I took a look. I may not get to try if for a while. I want to see if I can fix what's going on with sharepoint first. I have a few more options before moving elsewhere (it may be a cache issue- I'm testing now). But I'm definitely interested for the near future. I think you also should have my email address in the PM.

Thanks Rob
Go to the top of the page
 
RobKoelmans
post Nov 15 2018, 09:58 AM
Post#19



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


Ok, if you need help on your issue you can also let us know. Your app doesn't have to be migrated by the way. You just get the AWA browser functionlity as an extre. You can connect your existing Access Desktop Application as well. We have ways of limiting the amount of data per round trip which will make your forms perform fine.

What exactly are your performance issues in ListFolders? Did you manage the threshold settings or are they still on default?
Rob
Go to the top of the page
 
blueman2
post Nov 15 2018, 12:24 PM
Post#20



Posts: 228
Joined: 15-November 14



Ok Thanks

I did not manage any threshold settings. Not sure where I would find them or what to set them at.

As far as the current issues go, it's probably best explained (with images also) in this other thread so that I don't re-post everything in this one.

https://www.UtterAccess.com/forum/index.php...2051200&hl=
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th October 2019 - 03:05 AM