[AccessD] Linking to different instance of SQLEXPRESS

[Paul Wolstenholme]

Borge,

So as I understand it, you are running one SQL Server service - and that
has been upgraded to be SQL Server Express 2022.

That SQL Server service is probably supporting two database instances
{you've named them SQLEXPRESS and SQLEXPRESS2022} (there will be a
separate pair of files configured for each instance).

When you connect to the SQL Server service you normally specify the default
database (= default instance) that you wish to connect to.  As far as I'm
aware, this doesn't preclude you from accessing other supported instances
from the same connection (unless you have security settings that block
it).  It all depends on how fully you specify what you are accessing - i.e.
with or without the database name at the beginning.

I've never used the SQL Server Browser service and never used anything
other than the default port.  I assumed one SQL Server service would have
one TCP/IP port.

Paul

On Mon, 4 Sept 2023 at 16:45, Borge Hansen <pcs.accessd at gmail.com> wrote:

> So, we've installed SQLEXPRESS 2022 (16.) on a MS Windows Server 2022 (in
> place update from MS Windows Server 2012)  as a separate instance to the
> previously installed SQLEXPRESS instance (SQLEXPRESS 2014 version 12.)
> On the Firewall setup we set SQLEXPRESS2022 instance to listen on port 1433
>
> From our MS Access frontend for the 2014 version we linked using DSN less
> connection using
> [ComputerName]\SQLEXPRESS, 1433
> to point to the instance holding the database
>
> To access the restored database now living on the 2022 Express version we
> then for the DSN less connection used
> [ComputerName]\SQLEXPRESS2022, 1433
>
> Now on the linked tables on the frontend it says we are linked to
> SQLEXPRESS2022, 1433 but in actual fact we are still linking to the 2014
> version!!! ???
>
> On
>
> https://learn.microsoft.com/en-us/sql/relational-databases/lesson-2-connecting-from-another-computer?view=sql-server-ver16
>
> I found the following:
> ++++++
> Connecting Using the SQL Server Browser Service
> The SQL Server Browser service listens for incoming requests for SQL Server
> resources and provides information about SQL Server instances installed on
> the computer. When the SQL Server Browser service is running, users can
> connect to named instances by providing the computer name and instance
> name, instead of the computer name and port number. Because SQL Server
> Browser receives unauthenticated UDP requests, it is not always turned on
> during setup. For a description of the service and an explanation of when
> it is turned on, see SQL Server Browser Service (Database Engine and SSAS).
> To use the SQL Server Browser, you must follow the same steps as before and
> open UDP port 1434 in the firewall.
> +++++
>
> INTERESTING IT SAYS:
> When the SQL Server Browser service is running, users can connect to named
> instances by providing the computer name and instance name, instead of the
> computer name and port number.
>
> So it appears that when using the SQLEXPRESS2022 I should use another port
> number, otherwise it will link to the first installed sql server instance
> that is listening to that port number??
> Am I correct in assuming that?
>
> As for the SQL Server Browser Service, I am not sure if this service is
> available by SQL Server Express.
>
> Any ideas??
> Thanks,
> /borge
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>