SQL Express Installation Question - Existing Instance

[JMongi]

So I'm following this tutorial. When it gets to the part of naming the instance, it wants to use the default name for the instance.
However, I have Solidworks installed on this computer with their electrical package option which installs a version of SQL Express already and an instance with the default name.

Is there any reason that changing the default instance name for this new installation is going to break anything (other than me needing to remember to change it where appropriate)?

Edited for Clarity

 

[Isaac]

it's a shame you can't use the default instance - I do so, only because: if i ever lose my head and forget how everything works, i'd rather have someone tell me "the default name is ______" and then me use that, and it work.
however if you have to you should be able to change it without a problem.

just remember it and of course SSMS will populate most connection info and keep it

 

[JMongi]

It's all new to me. So, I appreciate the reassurance. It's one of those things where you understand some of the basics, but its a whole 'nother ballgame to install/configure and actually use it!

 

[Isaac]

It's all new to me. So, I appreciate the reassurance. It's one of those things where you understand some of the basics, but its a whole 'nother ballgame to install/configure and actually use it!

I agree. In my ideal imaginary world, roles & responsibilities are key, and they are clearly drawn and respected. (As opposed to the modern nonsense of "everything-says-yes-to-everything" team stuff). So I never wanted to attempt DBA activities, because I knew I wasn't really a DBA and shouldn't try. I was comfortable deferring humbly to the DBA advice in all cases.

Then I had to get uncomfortable as my clients wanted 'more and more', and get used to some sql server express installations myself. It violated my little perfect paradigm and made me squirm but so far so good, fingers crossed.

If you ever forget stuff, you can go back to the sql server installation center, kind of like a hub where you'd have a chance to, theoretically, view & change things like that which you had answered before.

 

[JMongi]

So, I think I've managed to get SQL Server Express 2019 installed and a new instance configured (if I recall, I still need to configure the windows defender firewall on this computer). I thought I bookmarked an Access specific tutorial on moving the backend to the server instance but it is nowhere to be found. Does anyone have some good links?

 

[JMongi]

Let's also throw a (probably) dumb question in here too.

Once I migrate an Access BE to SQL Server Express, any future BE changes must be done within the SQL Server environment, correct?

 

[Minty]

Correct. Any design changes to the tables etc,

You can use a pass-through query to instigate design changes, but it's not something I would bother with when it's so much easier in SSMS.

 

[JMongi]

but it's not something I would bother with when it's so much easier in SSMS

Can you give me an example since I've never done it before at all? I have an EXTREMELY rudimentary understanding of SQL.

 

[JMongi]

Ok, let me take a quick step back.

The end goal (FAAARRR in the distance...) is a rather robust application touching on asset management, maintenance tracking, ERP, inventory, possibly purchasing, etc.

Clearly the guts of these various application areas aren't even close to being ready. But, I would like to get various pieces up and running on SQL Server express (for laptop connections and remote access). Is it typical to do development of portions of the database in Access and migrate sections to SQL server? Does that open a whole can of worms? I'm trying to figure out the path forward where portions are usable while I work on other portions. The tricky part being that those portions would naturally interact some times.

Experience will likely dictate matters in the future, but I can still look for bread crumbs of a path forward now!

 

[Minty]

You can add new tables to an existing SQL BE database and it won't make any odds to your working system, much the same as a split Access BE environment, if you don't link them to the front end it's completely unaware of them.

Obviously, changes to an existing in-use data structure are a different ballgame.
This can be trickier to handle depending on the environment.

The normal route is to have a development copy of the SQL BE and a Dev Access FE. You carefully note the changes you make to any tables and script those in when you move from Dev > Test > Production status.

Sometimes you just have to say - "Sorry you have to update your FE now" and ensure the old FE won't work with the new BE.

SSMS will become your secondary development environment, maybe as much as 60-70% will be done on SQL server as opposed to Access, again dependent on the application.

 

[JMongi]

SSMS will become your secondary development environment, maybe as much as 60-70% will be done on SQL server as opposed to Access, again dependent on the application.

Interesting. I guess I better learn how to use it! Is it all SQL statement driven or is there some GUI involved? (Clearly I haven't even dove in yet...sorry!)

 

[Minty]

There is a GUI for certain things, but once you get used to it creating things like Views is easier as the editor is pretty good.