SBA/Sql Server/Server

[Ron Ifferte]

All,

Since I found many topics on the board relating to Sql Server/SBA
integration, I thought I would share my experience so that everyone
gains from it.

My office network consists of several workstations with no domains/AD.
I wanted to set up SBA so that it can be run from a central place and
used by several employees. So, I set up an older box with Windows
Server 2003, as well as installed Sql Server 2000. My thought was to
get SBA using Sql Server (instead of MSDE).

The first issue I discovered was that the instance name had to be
<servername>\MICROSOFTSMLBIZ. I initially read that as needing a db
named MICROSOFTSMBIZ - but that is not the case. I had installed Sql
Server using the default setup (i.e. the instance name was just
<servername>). Not wanting to reinstall Sql Server, I just left it as
is.

I then decided that I could just move the company database to Sql
Server and unistall MSDE (if you don't already know - SBA stores each
company as it's own database in the MSDE instance). I was successful in
moving the database and using SBA with Sql Server locally. I then set
up the company to by multi user aware. This created new SBC files -
which are simply text files that point to the company name and server
where the database is.

Since I was running fine locally - I decided to test from a
workstation. Nothing was working. It turns out that SBA connects to the
MSDE database on the network with a non standard port (it 5000
something - I don't remember exactly but if you look - its noted on
these boards). Now - I had two options - either reconfigure Sql Server
to listen on that port (and not the default port 1433 or revert back to
MSDE. I chose the later since I didn't want to interfere with other
apps that I may install that rely of Sql Server using it's standard
port. With MSDE installed, I went into Sql Server Enterprise Manager,
connected to the instance, and was successfully able to connect (as
well as develop a maintenance plan as per any Sql DB).

The one note that I have is that if you are on a WorkGroup, make sure
that your userids/pwds on each machine match. Otherwise you may have
conncetion issues (since you are not on a domain).

So - everything is running smoothly. I do hope MS allows for Sql Server
in an upcoming release. There is no technical reason why it couldn't
detect it and use it's normal port.

Thanks,

- Ron

[charlie]

Great post. Thank you.

I am planning to put our database on our SQL Server and this information
will come in quite helpful. Any updates that you may have would be greatly
appreciated.

--
Thanks,
Charlie

[William Stacey [MVP]]

> Since I was running fine locally - I decided to test from a
> workstation. Nothing was working. It turns out that SBA connects to the
> MSDE database on the network with a non standard port (it 5000
> something - I don't remember exactly but if you look - its noted on
> these boards). Now - I had two options - either reconfigure Sql Server
> to listen on that port (and not the default port 1433 or revert back to
> MSDE. I chose the later since I didn't want to interfere with other

You could setup a tcp port forwarder also on the sql box and forward 5000
port to 1433. Can't sql listen on more then one port?

--
William Stacey [MVP]