- This line was added.
- This line was removed.
- Formatting was changed.
What is the difference between the term Instance and Database?
There has been some confusion between the term "Database" and "Instance" when describing problems with ArtiosCAD's "database".
The term "Instance" refers to the group of services\ registry entries\ directory structure that combine to create a distinct SQL Server on a system. An instance is capable of independent processing from other database instances on the system.
SQL Server Enterprise can have up to 50 instances on a single system, SQL Server Workgroup/ Express is limited to 16 instances. Instances show up as Services on the system.
Instances can either be a named instance or a default instance, however there can only be one default instance on the system.
By default, for ArtiosCAD installations:
MSDE = Instance name of
SQLExpress = Instance name of
The default instance is the instance that "by default" you would connect to if you didn't supply an instance name but it actually has a name
To connect to a default instance on a system named myserver, type:
OSQL -Smyserver -Usa
To connect to a named instance (myinstance) on a system named myserver, type:
OSQL -Smyserver\myinstance -Usa
As a rule, in MSDE and SQLExpress, you always have to supply an instance name. For a full version of SQL Server you may have to supply an instance name. It depends on how the SQL Administrator installed the database.
Database refers to the group of tables, triggers, users, views etc., that manage a collection of records/ data within an instance. Databases can be accessed from the command line or tool (SQL Server Management Studio Express) that is connected to an instance.
Each instance of the SQL Server contains multiple databases. For example, the master database which contains a list of all of the other databases/ objects within the instance as well as the configuration of the instance.
If you have three database instances on a system, you also have three distinct master databases.
Other databases in an instance:
model - the default template for a new database
tempbd - working database for the SQL Instance, stores results for processing for sorting, temp tables/obejcts etc...
msdb - data for management of scheduled jobs,backup/restore records
For MSDE and SQLExpress, the ArtiosCAD database is named
ArtiosCADdb, unfortunate choice for MSDE as the instance name is also
In MSQL Server 2005, maximum number of databases per instance are 32,767
Connection to the SQL Server instance doesn't mean you are in the correct database. By default, when you log in as
sa, you'll be working in the master database.
- Open your ArtiosCAD ODBC entry.
- Enter in
safor the user and the sa password as
for SQLExpress ("" if MSDE).
- Click Next.
Notice the default database for that instance.
Reset it back to
programmer, the default database should now be
ArtiosCADdb, this is because the login for programmer is setup to go to ("use" in SQL Server) the
ArtiosCADdb by default.
The scripts for building SQL Server/ SQL Express databases,
Build_MSSQLServerSchema.bat have the second parameter as the database instance to connect too.
Build_ExpressSchema.bat rem The 1st parameter must be the sa password. rem o It is required. rem The 2nd parameter must be the server instance name rem o It is required. It will be <db server machine>\ArtiosCADExp2005 ... Build_MSSQLServerSchema.bat rem The 1st parameter must be the sa password. rem o It can be omitted if the instance name is not required rem o It should be "" if no password exists rem The 2nd parameter must be the server instance name rem o It is required. It will be <db server machine>\ArtiosCADdb ...
Don't be confused by the
<db server machine>\ArtiosCADdb. If it's a full version of SQL Server, the database administrator could have named the instance anything or nothing (default). Try logging into the instance via the command line first to see if you have the name correct.
In the above screenshot, the blue boxes are SQL Server Instances, green "cans" are databases and the brown box is the entire server.
SQL Browser tells you where to find the instance, the login tells you which database to work in by default.
|Applies to||ArtiosCAD all versions|