• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Database rebuild question

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    #31
    Originally posted by wattaj View Post
    The server is unlikely to be the issue when it comes to performance IMV. Access will have bottlenecks at the client side and on the network... particularly where there are concurrent users accessing the database across the same network.

    The server specs sound perfectly sufficient for running as a small business file/print server and hosting your SQL Server instance.
    Its definitely not the server.. Its everything else around Access that you state, definitely.

    That box is running the joint business activites of both myself and my wife. The properties, my business - brokerage, consultancy, the magazine etc and her investment stuff. There is some trading software on that box but the load is low.
    I will happily put the new system on a dedicated box, There is one available doing nothing.

    Email is handled by a commercial host as are the main websites. I spent a shed load of cash and seriously upgraded all of our hardware (business and personal) just over a year ago when I ran out of space in a couple of places during a hospital stay. That server got upgraded as did our laptops, tablets, my webservers and 2 x 16 TB Nas devices! There is tons of bandwidth and spare space. The network here is pretty good too.
    Former IPSE member
    My Website

    Comment


      #32
      Originally posted by wattaj View Post
      The server is unlikely to be the issue when it comes to performance IMV. Access will have bottlenecks at the client side and on the network... particularly where there are concurrent users accessing the database across the same network.

      The server specs sound perfectly sufficient for running as a small business file/print server and hosting your SQL Server instance.

      PS: happy to be corrected if there's a proper dev about; it's been some time since I cared about Access DBs.
      The interesting thing (ish) about Access is that you can still use any forms with the database tables that you've ported over to SQL Server. Potential migration route is therefore upgrade to Access 2019 if it's not already on that, then to SQL Server 2019 developer edition. Look at the licencing though - longer term you may want to move to Express once it's production-ready if Developer edition doesn't support it. Nothing to stop you doing dev locally on dev edition then pushing changes on to Express for example.

      Consider also getting a git repository or similar version management tool to sort your code versions out. If you get stuck with anything on the SQL side, just google Brent Ozar - his website is a fantastic resource, especially when you're only doing part time DBA tasks.

      Power BI is also free if the users want to play with the data and draw pictures with it.
      The greatest trick the devil ever pulled was convincing the world that he didn't exist

      Comment


        #33
        Originally posted by LondonManc View Post
        The interesting thing (ish) about Access is that you can still use any forms with the database tables that you've ported over to SQL Server. Potential migration route is therefore upgrade to Access 2019 if it's not already on that, then to SQL Server 2019 developer edition. Look at the licencing though - longer term you may want to move to Express once it's production-ready if Developer edition doesn't support it. Nothing to stop you doing dev locally on dev edition then pushing changes on to Express for example.

        Consider also getting a git repository or similar version management tool to sort your code versions out. If you get stuck with anything on the SQL side, just google Brent Ozar - his website is a fantastic resource, especially when you're only doing part time DBA tasks.

        Power BI is also free if the users want to play with the data and draw pictures with it.
        Yes this would be the line of thinking if SQL server is what I choose.
        I have quite an "intranet" going here and have GitLab installed on a webserver for source code management. I use it for my HTML stuff - I have a lot of HTML and CSS there. I have also got tons of retro games in HTML5 and stuff that I have purloined from GitHub (great for when i'm in hospital bored witless). Its linked to my personal NAS too so plenty of space!

        Thank you for the suggestion of Brent Ozar I have just looked at his site - its great stuff!
        Former IPSE member
        My Website

        Comment


          #34
          Originally posted by courtg9000 View Post
          Yes this would be the line of thinking if SQL server is what I choose.
          I have quite an "intranet" going here and have GitLab installed on a webserver for source code management. I use it for my HTML stuff - I have a lot of HTML and CSS there. I have also got tons of retro games in HTML5 and stuff that I have purloined from GitHub (great for when i'm in hospital bored witless). Its linked to my personal NAS too so plenty of space!

          Thank you for the suggestion of Brent Ozar I have just looked at his site - its great stuff!
          You're welcome.

          Seriously, it's really easy to import data.

          Create a SQL Server database, right click on its name in Management Studio, choose Tasks, then Import Data. Pick Access from the list of data providers, find your database in the explorer and choose which tables you want to import. Grab a coffee and try and look busy!
          The greatest trick the devil ever pulled was convincing the world that he didn't exist

          Comment


            #35
            Originally posted by LondonManc View Post
            You're welcome.

            Seriously, it's really easy to import data.

            Create a SQL Server database, right click on its name in Management Studio, choose Tasks, then Import Data. Pick Access from the list of data providers, find your database in the explorer and choose which tables you want to import. Grab a coffee and try and look busy!
            Yes. This now the plan. SQL Server.
            I was thinking I might get some varied and interesting responses here instead of a non valid arguement about hardware but hey ho!

            In theory I can do the migration this weekend. I will build the new box for it, get it up and running and get some use of it and review in 3-6 months.

            With between 2 & 5 residential evictions (tons of paperwork and tracking) coming up and possibly as many 100 pubs, hotels and restaurants (might also be some other businesses as well!) coming on to the system along with other stuff its probably best I do upgrade immediately.

            Thank You again and thanks also to @wattaj
            Former IPSE member
            My Website

            Comment


              #36
              Originally posted by courtg9000 View Post
              Yes. This now the plan. SQL Server.
              I was thinking I might get some varied and interesting responses here instead of a non valid arguement about hardware but hey ho!

              In theory I can do the migration this weekend. I will build the new box for it, get it up and running and get some use of it and review in 3-6 months.

              With between 2 & 5 residential evictions (tons of paperwork and tracking) coming up and possibly as many 100 pubs, hotels and restaurants (might also be some other businesses as well!) coming on to the system along with other stuff its probably best I do upgrade immediately.

              Thank You again and thanks also to @wattaj
              No problem. As I said, really easy to import the data as long as the SQL Server box can see the Access database.

              Just shout on here if you need any assistance.
              The greatest trick the devil ever pulled was convincing the world that he didn't exist

              Comment


                #37
                Originally posted by courtg9000 View Post

                Thank you for the suggestion of Brent Ozar I have just looked at his site - its great stuff!

                +1 for Brent , also very easy to listen to. Recommend his video explanation of pages, it was the first time I really got it.
                Always forgive your enemies; nothing annoys them so much.

                Comment


                  #38
                  Originally posted by wattaj View Post
                  You missed the bit where I pointed out that Access queries run on the client: all data for a query is sucked from the DB and then Access will discard what it does not need to return to the user. That's a lot of local load and network traffic. SQL Server is proper server-side processing.

                  Access may have changed since I last looked, but I doubt that it's changed that much.
                  ^ This.

                  People forget the fact that Access is not a client-server database.

                  You will need the fastest network possible and the fastest disks. Forget RAM and CPU on the "server".

                  The biggest risk is the whole file getting corrupted!

                  Comment


                    #39
                    Originally posted by DimPrawn View Post
                    ^ This.

                    People forget the fact that Access is not a client-server database.

                    You will need the fastest network possible and the fastest disks. Forget RAM and CPU on the "server".

                    The biggest risk is the whole file getting corrupted!
                    Yep corruption is a worry. Thankfully I have very regular backups
                    Former IPSE member
                    My Website

                    Comment


                      #40
                      Originally posted by courtg9000 View Post
                      Yes. This now the plan. SQL Server.
                      I was thinking I might get some varied and interesting responses here instead of a non valid arguement about hardware but hey ho!

                      In theory I can do the migration this weekend. I will build the new box for it, get it up and running and get some use of it and review in 3-6 months.

                      With between 2 & 5 residential evictions (tons of paperwork and tracking) coming up and possibly as many 100 pubs, hotels and restaurants (might also be some other businesses as well!) coming on to the system along with other stuff its probably best I do upgrade immediately.

                      Thank You again and thanks also to @wattaj
                      Remember to do a proper DB back-up plan and schedule. This will be a bit more involved than simply backing up the Access database file that you may be used to. Good luck. Happy to help.
                      ---

                      Former member of IPSE.


                      ---
                      Many a mickle makes a muckle.

                      ---

                      Comment

                      Working...
                      X