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
> Server 2008 R2 Or Express 2017, Any Versions    
 
   
firlandsfarm
post Jan 17 2020, 12:23 AM
Post#1



Posts: 393
Joined: 28-April 02
From: Heathfield, England


Hi, I'm starting a new database. I expect it to be around 1GB in size, I will be the only user and I will be using Access 2016 as a front end. I have the option to install it on a 2008 R2 version or on Server Express 2017, both are installed on my laptop. I appreciate MS have withdrawn support for 2008 R2 but will I see a difference between the full 2008 R2 version and the restricted Express 2017 version? Also the new database will be drawing data from an established database on the 2008 R2 set-up (approx 2.5 Gb in size but cannot move the database, not in my control). Would there be a speed disadvantage in having the two databases on different servers? I just want to get a feel for it before I start work and would appreciate any thoughts others may have as my experience of SQL is less than zero! smile.gif

Stop Press: Just found Express 2019 so am upgrading to that and the question is now 2008 R2 v's Express 2019 smile.gif
This post has been edited by firlandsfarm: Jan 17 2020, 01:10 AM

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
MadPiet
post Jan 17 2020, 02:39 AM
Post#2



Posts: 3,467
Joined: 27-February 09



I'd check the features that have been added since 2008R2. If you're going to use SQL Server for just a data repository, I guess it doesn't matter that much, but seems a shame to install SQL Server and then not use the query engine. Windowing functions and variables etc and proper stored procedures make SQL Server more difficult to learn, but a lot more flexible and powerful.

So this isn't really an apples to apples comparison. SQL Server is a whole lot more powerful, but has a steeper learning curve too.

What do you need the database engine to do that Access doesn't do already? What kinds of queries do you need to write?
This post has been edited by MadPiet: Jan 17 2020, 02:40 AM
Go to the top of the page
 
GroverParkGeorge
post Jan 17 2020, 08:46 AM
Post#3


UA Admin
Posts: 36,760
Joined: 20-June 02
From: Newcastle, WA


I can't think of any serious argument for continuing to use SS 2008 R2, especially for a new installation.

What are the reasons for considering it here?

The one thing I'm aware of that might be a limitation on an Express version of SS would be the lack of support for automatic backups....




--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post Jan 17 2020, 09:39 AM
Post#4



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


QUOTE
Hi, I'm starting a new database. I expect it to be around 1GB in size...


Storing 1GB of data in an Access db will not perform well. SQL Server Express 2019 has a 10GB limit, a better alternative, and supported, 2008 is not. You said single user, so is this going to be a data warehousing analytical type application?
This post has been edited by FrankRuperto: Jan 17 2020, 09:44 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
firlandsfarm
post Jan 17 2020, 12:43 PM
Post#5



Posts: 393
Joined: 28-April 02
From: Heathfield, England


I'm basically with you on that George. 2008 R2 was in the mix because that's where the 3rd party database is and they have confirmed they have no plans to move it. Going to 2019 Express for my own stuff seemed the obvious choice but I didn't know how much if any 2019 Express is scaled down compared to 2008 R2 and if there would be much loss of speed having 2019 Express interrogating a database on 2008 R2 compared to having it all on 2008 R2. I've been playing around (and frustrating myself!) today and any difference in performance depending on which server I place my database on is hardly noticeable if at all.

The main reason for the move MadPiet is that I have hit the 2Gb limit a few times when running queries and I have been told in past discussions on these forums to have data partly on an SQL server and partly in Access is the worst of both worlds. All SQL or all Access is best and as I'm not in control of the SQL database and it is itself over 2Gb I am left with all SQL as the only option. I'm really looking for just a data warehouse and sorter/sifter with Access as the FE.

I think you came to the same conclusion as me Frank. smile.gif

Thanks for your input guys, much appreciated.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
FrankRuperto
post Jan 17 2020, 03:00 PM
Post#6



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Sounds like a plan! Keep in mind that you want SQL Server to do as much of the heavy lifting as possible, e.g. queries, sorting. That means creating PT (PassThrough) queries in Access that will be executed by the SQL_Server engine. These PT queries have to be written using T-SQL syntax, not AccessSQL syntax. You can also create SP's (StoredProcedures) and Views in SQL_Server to optimize performance. The idea is to keep the traffic of data between SQL_Server and the Access FrontEnd down to a minimum.

As to SQL Server Express 2017 limits: One CPU Socket (a socket can have more than one CPU core), 2GB RAM, 10GB db size. (Notice I said Express 2017, not 2019).

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
AlbertKallal
post Jan 17 2020, 06:41 PM
Post#7


UtterAccess VIP
Posts: 2,954
Joined: 12-April 07
From: Edmonton, Alberta Canada


the one issue that will crop up?

If you create or use 2019 to create a database, then you not be able to do a quick backup and restore it on the production 2008 version. So that can be a real pain.

The express edition is limited to 10 gigs - likely not a issue, but if 2008 is not express, then you have no such limitation.

So, if you have to transfer database between the two systems, having a higher version then the target can be a real pain.

If the database is not too large, then you can script up the schema + data, but these files tend to be really huge, but it certainly a way to transfer say a 2019 database to say SQL server 2012. There is unfortunately no way to "back date" and use a backup from 2019 say on a previous version.

So with 2019, you can certainly pull a copy (a backup) of a previous version of SQL server, and place it local on your DEV box, and restore for testing. I often do this to track down some bug or issue - but a bug or issue not occurring with my "sample" database. So I grab a clients copy as a backup and restore. You can always restore upwards, but once you done such a restore, there is no going backwards with a backup. (you have to script it out).

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
MadPiet
post Jan 17 2020, 09:14 PM
Post#8



Posts: 3,467
Joined: 27-February 09



Albert,

Is it possible to run Express in a lower compatibility level? (Never tried it... I know you can do it with the over editions of SQL Server.
Go to the top of the page
 
FrankRuperto
post Jan 17 2020, 09:40 PM
Post#9



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Albert,

QUOTE
If you create or use 2019 to create a database, then you not be able to do a quick backup and restore it on the production 2008 version. So that can be a real pain.


The OP said he is going to be running a single-user data warehouse analysis app on the new db, so the data will only be ETL from the production 2008 db to the 2017 warehouse db.

FirlandsFarm,

If you are going to periodically take snapshots of the production data for your warehouse app, I suggest you only bring in the data you need for generating the reports and queries you are planning to run. Despite SQL_Server Express' ability to handle 10GB of data, size matters performance-wise when importing large amounts of data, creating indexes, executing queries, etc. Smaller is always better! Remember that Express has a 2GB memory limit and some queries might create temporary tables that could hit that limit.

I also suggest you start familiarising yourself with SQL_Server, Transact-SQL and how to use the SSMS management tool. You also most likely will have to cleanse data you are going to import, add surrogate keys, update NULL values, etc. and SSIS is a good tool for doing that, but I dont think its available for free.
This post has been edited by FrankRuperto: Jan 17 2020, 10:02 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
FrankRuperto
post Jan 18 2020, 12:01 AM
Post#10



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


CORRECTION: SQL_Server Express 2017 is limited to 1GB of memory. I don't know if reaching that limit throws an error, or if virtual memory swapping occurs. Albert Kallal can elaborate on this.


Attached File(s)
Attached File  SSE2017.PNG ( 266.43K )Number of downloads: 6
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
firlandsfarm
post Jan 18 2020, 04:26 AM
Post#11



Posts: 393
Joined: 28-April 02
From: Heathfield, England


Frank … I won't be using pass-throughs, all queries will be within SQL 2019 and Access will just call the queries from SQL as objects.

I wasn't expecting the detail that is in some responses so think I should clarify things a little. The database (DB2008) on the 2008 R2 server is totally outside my control. It is updated daily by the proprietor by automatic downloads. For my purposes it is totally read only and should a reinstallation be required a total download of DB and Server OS is available at any time from the proprietor's website so no need for me to back-up. My 2019 Express database (DB2019) will read data from DB2008 as required and combine with my own data held in DB2019 to perform my queries. I'm not looking to physically transfer any data from either DB to the other, just have Access tell DB2019 what it wants and for DB2019 to read data from DB2008 when it needs to. All queries will be held in DB2019. Access will be used as a FE overlay to display the returns from the queries in DB2019, to assist populating/updating my tables in DB2019 and assist me in writing the T-SQL queries for DB2019 by building the query in Access Query Design mode, extracting the Access SQL and then translating that to T-SQL … yes it may sound laborious to you who can write SQL as if it's your native language but I've found it the best way for me to create/learn SQL in live mode.

I am only switching to 2019 Express because of the Access size limit and if it wasn't for the increased speed of doing everything in SQL I would be splitting my Access database into two or more Access BE db's to stay within the size limit and linking the Access BE's and DB2008 to an Access FE but I'm expecting that would be a speed disaster!

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
FrankRuperto
post Jan 18 2020, 07:04 AM
Post#12



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


QUOTE
writing the T-SQL queries for DB2019 by building the query in Access Query Design mode, extracting the Access SQL and then translating that to T-SQL


I strongly recommend against building the queries in AccessSQL. ODBC does a poor job in translating them to T-SQL, mostly resulting in inefficient queries that end up reading all the records in tables. This is why I was suggesting you write PT queries in Access that get executed on SQL_Server. The alternative is to create VIEWS in SQL_Server that the Access reports will consume.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
AlbertKallal
post Jan 18 2020, 03:36 PM
Post#13


UtterAccess VIP
Posts: 2,954
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
Is it possible to run Express in a lower compatibility level?


Yes, but don't let that fool you into thinking that you can move + exchange a backup as a result. So yes, you can run in a compatibility mode - it is nice.

However, this does not change the exchange of backups. Even if you set the combability level?
Even if you RESTORE a older database and keep the older compatibly level?

Once done? If you THEN make a backup of that database? You simply cannot transfer that backup BACK to a older version. The ONLY way to accomplish this would be to use the database scripting wizard - and I often find for customer databases, this is a really slow process.

So yes, you can use compatibility mode - but it helps ZERO in regards to backup combability. Once you restore on a newer version, you can't create a simple ".bak" file (a database backup) and restore it on a older previous version. This issue remains regardless of the combability settings you use.

In a way, it kind of like Access. You can pull forward - but once touched or used by a newer version, you often can't go backwards.

So we often in Access develop in the lowest common version. The problem with SQL server is if you restore that slightly older verison on their NEWER version of SQL server, then you now can't pull a backup copy to your dev computer for testing. You require at least the same version + level of SQL server to do this.

R
Albert
Go to the top of the page
 
FrankRuperto
post Jan 18 2020, 04:55 PM
Post#14



Posts: 633
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Albert,

That being said about backup/restore compatibility, plus the 1GB memory limitation of the Express versions, I am thinking the OP would be better off using another same 2008 R2 full (Standard?) version which the OP said he has installed for his DataMart application. For what the OP wants to do, I dont see significant advantage in using a newer Express version that's limited memory and cpu-wise. Newer versions do provide "in-memory" tables, but with only 1GB of RAM available for Express, how could that effectively be leveraged? If the 2008 R2 is a Standard version or higher, then more memory, CPU and db size will be available to the OP. I dont know if 2008 provides parallel queries capability which can be leveraged for DataMart procesing. My experience is more with other db servers like PostgreSQL, Informix and Oracle. From time to time I have tinkered with MSSQL. Last time was 2 years ago when I used bcp, the bulk copy utility to export data out of some tables, so I am definitely not an authority on MSSQL, but to me virtually all popular db servers are basically the same, sans the SQL syntax, available functions, etc. etc.


FirlandsFarm,

In my previous post to you, I think I misinterpreted what you want to do. Is it that you want to use the Access Query Design Wizard to generate the SQL statements in AccessSQL syntax, and then you will manually translate them to T-SQL syntax and execute them on SQL Server? If true, are you doing this to save time, not well_experienced with writting from scratch AccessSQL or T-SQL statements? I personally have had a tough time assimilating AccessSQL syntax because its flavor really detracts far from ANSI SQL, and I am used to very rich functionality available with Oracle SQL-Plus, but I am getting better with AccessSQL's syntax and use of native Access functions, although the built-in SQL Editor has a lot to be desired, but practice makes perfect.
This post has been edited by FrankRuperto: Jan 18 2020, 05:31 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
AlbertKallal
post Jan 18 2020, 07:15 PM
Post#15


UtterAccess VIP
Posts: 2,954
Joined: 12-April 07
From: Edmonton, Alberta Canada


My case was a bit different. some clients had updated to 2017. I was on 2012.

So I could not pull a copy and test local. The downside is if I now have to transfer or move a database from my dev computer to their system. This does not happen very often, so it was/is the lessor of two evils for me.

And as noted, you CAN use the scripting wizard, and script out the whole database + data - and that will let you go back to near any version you want. The only issue is that scripted files are rather large - and restoring them takes quite a bit of time. However, I don't have to do this very often, and I do still have a means to send a database I am working on "back in time". And for smaller databases, then the scripted database is not all that bad. But, if the database is say 3-5 gigs, that's not oh so huge - but restoring from a script is still a lot slower then a .bak file - they are very fast.

If the server is to remain 2008? Then I agree - I would stick with 2008 on my dev box. This would allow a easy moving back and forth - and backups and anything one tests would be 100% compatible. I never had issues with compatible in terms of code or features. CTE (common table expressions) came out in 2005, or 2008. About the only relative recent feature is iif() like we have in Access - that was introduced in 2012. So that's a tough call - I have seen "some" use of iif() creep into my code due to Access habits. (but I prefer the CASE for that).

R
Albert
Go to the top of the page
 
firlandsfarm
post Jan 19 2020, 08:15 AM
Post#16



Posts: 393
Joined: 28-April 02
From: Heathfield, England


Frank: Yes, I have more experience translating AccessSQL into T-SQL than I do with building queries in T-SQL, I see it as the lazy mans' approach! smile.gif And I only need to remember to change a few functions now that I have 2019Ex and can use 'iif' in place of Case. Also I don't see any advantage in using PT queries if sets of queries have to be layered to obtain the required result … surely it must be more efficient to have all queries in 2019Ex and Access linking to the top layer view.

Albert: Maybe I'm misunderstand you but I don't see a problem with backing up. The two databases are effectively mutually exclusive they can be backed-up individually and restored individually. For now neither database stores data from the other. I say "for now" because I am looking at saving some calculated values from the 2008R2 database in tables in the 2019Ex database where it makes sense to.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th February 2020 - 03:27 PM