Business Accounting Database System Networking |
Automotive Business Accounting Database Software System for Businesses
ShopMate Automotive Business Accounting Database Networking Ideas
AzureMate - Best in Windows Azure Cloud Storage Explorer Software Products
Welcome to Softhard Solutions ShopMate Database Networking Ideas page. ShopMate Database Networking
can be implemented as a LAN (Local Area Network), two or more workstations connected to one local Sql Server, or
WAN (Wide Area Network), two or more different premises LANS connected to an external Sql Server.
LAN involves connecting every workstation on-premises to a Sql Server. The selected Sql Server may live on an
ordinary workstation (this is described below) or on a computer running a Server operating system. As companies
accumulate more and more data in modern times, keeping expensive infrastructure on-premises is becoming a concern
for them so LAN is becoming less popular.
WAN, on the other hand, is gaining more and more popularity since software companies, like Microsoft, provide their
Cloud computing and data storage services for a fraction of the costs involved in an on-premises business operations.
You can easily establish a Microsoft Windows Azure Cloud Services account and then create Azure Cloud Sql
Server and Database service account.
You can establish your account on their website:
https://azure.microsoft.com.
Then create a Sql Server and Database service account in your Azure Portal.
A good thing about storing data in the Microsoft Azure Cloud Service is that it is very economical financially and your
stored data is safe and available anywhere in the world via Internet Network as long as you have ShopMate, a computer to run
it on and a connection to network.
ShopMate supports all the connections to a Sql Server mentioned above. Even if you do not have ShopMate installed, read on,
these Sql Server Database Networking Ideas may be useful to you if you deal with a Sql Server's Databases and
connections.
For some operations described here you need Microsoft Sql Server Management Studio (SSMS) tool. You can get
it free at Sql Server Express Downloads
page. There you can select either 32 or 64 bit version.
Important Note
If you are using LAN and an ordinary workstation for your Sql Server, you should use the
Windows Professional Edition or Higher Operating System because of proper networking available in these operating
systems. Home systems have only rudimentary networking available.
LAN Networking with ShopMate
Due to Microsoft® ever increasing network security, to explain Windows® operating
system networking fully is beyond the scope of this help document, especially if
you have any legacy workstations and or one or more domain (.com etc.) controllers,
and should not be attempted without a competent IT personnel.
If you have a simple local area network comprising of a few modern home group or work group of
workstations and no domains you may try to do as simply explained in the following
paragraphs, with care.
The ShopMate Database can reside on any instance of SQL Server 2012
or higher installed on any machine on your network. A ShopMate installation
must exist on every workstation you wish to use to run ShopMate operations.
With every installation of ShopMate on your network, an instance of SQL Server 2012,
called SHOPSQLEXPRESS is installed if it does not exist. On one workstation you may install up to
16 named instances of various SQL Servers.
The ShopMate Database can reside on any instance of SQL Server 2012 any edition, or higher,
that is installed on any machine on your network. A ShopMate Installation must exist on every workstation
you wish to use to run ShopMate program operations.
With every installation of ShopMate on your network, an instance of SQL Server 2012 is installed, if it does not
exist, and with it all the necessary server and client network protocols which can be handled by the
SQL Server's Configuration Manager (see pictures below) which provides basic configuration management
for SQL Server network services, server protocols, client protocols and client aliases.
Note that when the sql server is first installed by ShopMate main installation and it is not an upgrade, the
networking is enabled and the SA (system server administrator) password is set to 'shopmate123'
(all small letters without the apostrophes) by default to enable both types of Authentication, Windows and
Sql Server. Also, the installation creates a user login called shopmate on the sql server
with the same password.
You can use both accounts, SA and ShopMate, in the Sql Server Authentications. If the
installation upgraded your existing sql server then the networking
may be disabled by previous settings, you must check and enable the TCP/IP Network,
Shared Memory and Named Pipes protocols on both, the sql server and
client protocols using the SQL Server's Configuration Manager described
here.
You will find this manager on your Task Bar in (see the example below):
Start\Programs\Microsoft Sql Server 2012\Configuration Tools\SQL Server Configuration Manager
installed by sql server installation. Click this shortcut to make changes to Sql
Server network protocols and permissions. Click on Sql Server Services on
the left then click (highlight) the Sql Server (SQLEXPRESS) record on right,
right click it and select Properties. In there you can setup your server
permissions.
But before you do that, you may setup the server networking first. Click Protocols for SQLEXPRESS
on the left and right click each protocol on the right selecting either Enabled or Disabled
choice. Do not turn on the VIA protocol, it is a legacy and not used. Match your settings in the
Client Protocols on left. Then stop and restart the Sql Server in its Properties as above for the
changes to take effect. On any one workstation you may install up to 16 named instances
of various SQL Servers but ShopMate needs only one.
Important Note
If you are unable to Backup or Restore database, your Sql Server may be running under low permissions
like NT AUTHORITY\NETWORK or \SERVICE or similar. You must increase the permissions of your Sql
Server, in the Windows Services, or in the Sql Server Configuration Manager as above, to at least
Local System Account or higher.
You must, therefore, decide which of the installed SQL Servers 2012 or higher on your network you
are going to use as your central database repository for your data, which workstation is going to be the Host
for the ShopMate Sql Server Database and connect all the other workstations to it.
Important thing is, the selected central Sql Server must have administrative permissions to access all the external
workstations connected to it in case of backing up to or restoring from them. You can set its higher permissions
in the Sql Server Service (in the Windows Services) or in the Sql Server Configuration Manager above.
If you can't backup or restore database even on the server's own workstation, you also have to increase its permissions!
If you still can't connect remotely after you've done all the above, the reason may be that in your networking scenario the Sql Server Browser Service may not be running or the system's Firewall is Blocking the selected Sql Server's Incoming or Outgoing Network Connections. You may try the following steps but be careful. Your DBA (database administrator) should know what to do.
Next Networking Steps With ShopMate
Make sure the SQL Server Browser service is running. It is used with SQL Server 2012 for client connections. This tip covers what this is and how to use this service to improve and protect your SQL Server Environment.
The SQL Server Browser service runs as a Windows Service and it is an extension of SQL Server. It plays an important role while working with more than one SQL Server instance installed on the same machine. The basic purpose of the SQL Server Browser service is to provide instance and port information to incoming connection requests.
To configure a better and safe access mechanism for your SQL Server, your DBA (database administrator) should have proper understanding of the SQL Server Browser Service.
What if SQL Server Browser Service is stopped?
The SQL Server Browser Service is installed with installation of SQL Server 2012. It also serves earlier versions installed on the same machine if there are any. The SQL Server Browser Service has different impact in different situations, such as:
-
If you have just one instance installed on machine and it is running on default
port 1433, then status of SQL Server Browser Service does not make any difference
in your connection parameters.
-
If there are more than one instances running on the same machine, in that case either
you have to start SQL Server Browser Service or provide the Port Number along
with IP (or Workstation Name) and Instance Name, to access
any other instance than default.
-
If SQL Server Browser service is stopped and IP along with port number is not provided
then connection will be refused.
-
If SQL Server instance is configured using dynamic ports then browser service is
required to connect to correct port number.
- Also your named instances will not be published in the list of SQL Server instances on the network (which could be a good thing)
Best practices for using SQL Server Browser Service
The way you use SQL Server Browser service affects the access to your SQL Server instances, hence the security of installed instances. At one end, the most security conscious approach may be to use customized static ports (see example below) for your instances and access SQL Server with fully qualified connection parameters. It would include IP\InstanceName,PortNumber. In this configuration your instance would not be exposed to the network also more parameters are required in the connection string.
It would be a secure configuration compared to having the SQL Server Browser service running and serving the incoming connection requests with instance and port information. If you have no problem in using fully customized connection strings (IP\InstanceName,PortNumber), then you can stop the browser service permanently and access the instance through a fully qualified connection string.
In another type of configuration, if the instance is configured to use dynamic ports each time, then SQL Server Browser service should be running, otherwise there would be no way to keep track of the dynamic ports that are assigned.
If you have just a default instance installed (with default port) on your machine then SQL Server Browser service may be stopped to avoid overhead and access the instance with just WorkstationName\InstanceName in ShopMate.
As part of best practices, always run SQL Server Browser service with a minimum privileged account. According to BOL any Windows user having the following rights could be capable to run the SQL Server Browser service.
- Deny access to this computer from the network
- Deny log on locally
- Deny log on as a batch job
- Deny log on through Terminal Services
- Log on as a service
- Read and write the SQL Server registry keys related to network communication (ports and pipes)
You can change the default account of the browser service (local system), to any other user having the above mentioned rights on the machine.
Limitations and further considerations
-
SQL Server Browser service operates at the Operating System level. You can not have
the browser service started for one instance and stopped for others on the same
machine. If SQL Server Browser service is started, it would be serving all incoming
connection requests.
-
Same concept may be applied in case of clustering. You have to separately install
and start the browser service on each node of the cluster.
-
If you run the sql browser service and if you are using any type of firewall then you must
include UDP port 1434 in the exception list. The UDP port 1434 is a port used by the SQL Server Browser service.
-
If you are running SQL Server 2000 instance side by side along with SQL Server 2005/2012,
then make sure to apply service pack 3 for the SQL Server 2000 instance. SP3 is
required to work correctly with the SQL Server Browser service.
- For developers, it would be better if you have SQL Server Browser service with the same settings on both development and production servers. It would prevent any problems due to differences of connection string information.
Testing the configurations
In this test, a named instance with the following customized static port (1444 instead
of the normal 1433) as shown in the TCP/IP properties (right click the protocol
and select Properties) of the instance in the SQL Server Configuration Manager
mentioned above and shown below.
If you are not going to run the SQL Server Browser service you must assign a static
port 1433 (or other) to the sql server service as seen below in IP1 and IPAll on TCP Port fields and clear the
TCP Dynamic Ports fields (nothing in them not even a zero). After any changes you must restart the sql service for the
changes to take effect (click Sql Server Services on the left and right click the sql service record on the right selecting
Properties).
Note the test is done with sql server 2008 but should be same for 2012 and higher.
If SQL Server Browser Service is not running then start it in the Windows Services.
When the Browser Service is running, we can access the instance without specifying the port number. We can successfully login SSMS with the following connection parameters IP\InstanceName or WorkstationName\InstanceName.
Now stop the SQL Server Browser Service in the Windows Services. Also exit SSMS and reopen it. Try to login again with the same parameters as before. This time you will get an error message like the following.
The reason is that the browser service is not running and the customized static port is also missing in the connection parameters. To login successfully we have to provide the customized port number in the following way. The server name field would need IP\InstanceName,PortNumber or WorkstationName\InstanceName,PortNumber.
Hopefully this has given you enough information to understand how the browser service runs and when you do not need to have this service running. It is very important to note that after making any changes to the SQL Server Browser Service make sure that the connection strings in ShopMate Business Software are changed to meet the changed configurations, otherwise you may have some connection issues.
Next Possible Networking Steps With ShopMate
If your Sql Server connection described in the above steps solved your problem and is working, nothing else has to be done. If not, you may try the following steps. If your system runs any firewall you can exempt Sql Server and its services from being blocked by doing something similar to following:
Windows 7, 8, 10 or higher:
In the Control Panel\System and Security\Windows Firewall click on Allow a program or feature Through Firewall,
click Change Settings button and then Allow another program... button. In there click the Browse button
and navigate to the sql server or to the sql server browser runtime as above and select it in that dialog box.
Click Network location types button and select Home/Work (Private) check box, see security note below
on Public networks but usually you would leave that unchecked. Click Add button to add the exception to the system.
For TCP and UDP ports:
In the Control Panel\System and Security\Windows Firewall click on Advanced settings,
click Inbound Rules command and either right click Inbound Rules and select New Rule or click the
New Rule... command on the right pane. In that dialog box select Port radio button and click Next button.
Select TCP or UDP and Specific local ports radio buttons.
In the Specific local ports field type in
1433 for TCP port or 1434 for UDP port and click Next button. Select Allow the connection radio button and click
Next button and select Domain and Private network check boxes, see security note below on Public networks but usually
you would leave Public check box unchecked.
Click Next button and give the exception a name like "Inbound Sql TCP Port 1433"
or "Inbound Sql UDP Port 1434" and exception description like "Opens up Sql Server TCP Port 1433" or
"Opens up Sql Server Browser UDP Port 1434". Click Finish button to install the new rule into the system.
In case the required ports are blocked on the outbound, create similar rules same way on the Outbound Rules.
Important Network Security Note
Unless you have a valid reason why all the Internet users should be allowed
to connect to this SQL Server, and so increase the security risk, in the Add Program or Port applet make
sure the sqlservr.exe or sqlbrowser.exe or a port is selected, then click the Change scope
button and in that applet select the 'My network (subnet) only' option.
This will make sure that only the workstations on your local network can connect
to this and not the Internet users. However, on a wide area network which
employs the tunneling through the Public Internet Networks and Domains this
has to be set for public use.
Also, if you use a Wide Area Network (two or more local networks connected together)
in your network setup which employs the tunneling through the Public Internet Network,
the cost when you run out of your provider's maximum uploads and downloads must be considered.
All the other workstations should be re-connected to this selected central SQL Server
called 'WorkstationName\ServerName' and the chosen server must have permission access
to all these external workstations. Do that in ShopMate in the System\Sql Server\Connect
menu or System Module 'Maintenance' button.
Optionally you can disable (in Windows Services) all the other SQL Servers
on all workstations that have ShopMate installation. A good practice is to keep
some of them enabled as a fail-over, in case the workstation hosting your central
sql server fails.
See More Information On:
-
ShopMateWeb Online Cloud Based Business Database Application
-
ShopMate Desktop Automotive Database Software
-
ShopMate Desktop Modules Explained - Screen Shots
-
AzureMate Desktop Cloud Data Storage Explorer Software
-
Software Downloads and Installations
-
MotoShop Automotive Database Software
-
Accountancy - Accounting Theories
-
Ideas for Business - Business Tips
- Automobile History - Automotive Fuels