How to create a DigaSystem database manually (MSSQL)
When setting up a fresh DigaSystem using an MS SQL Server you need to create a DigaSystem database with the "administration tables".
You can do this by using sql-scripts or by doing it manually step by step yourself. This article describes how to do it manually from SQL Management Studio.
- Install SQL Server 2014 Express (?with advanced features?), give it an instance name for accessing it e.g. SQLEXPRESS to access it with ".\SQLEXPRESS".
- Choose Mixed mode for authentication (SQL Server authentication and Windows authentication)
- User "sa" needs a password to remember for administration rights.
Install Management Studio 2014.
Open Management Studio 2014, the following instructions are inside this tool.
Under "Security" create a login "david" with password "digas" or an equivalent of your choice.
- Uncheck "User must change password at next login."
- Give "david" the role sysadmin, nothing more is needed.
- Create database "DigaSystem" or an equivalent of your choice. The owner is the just created login "david".
- Hint: You don’t need to give the database a compatibility level of e.g. SQL 2008 in properties of SQL Management Studio. 2014 works.
- In database "DigaSystem", under "Security", create a new schema "david" with owner "dbo".
- Go back to (outer) security of the whole Instance and make database "DigaSystem" the default database of login "david".
- Test: Login from SQL Management Studio with sql-user "david" and password "digas" and create a table (relation) of choice just for test. It will have "david" as owner. You see it in the name david.tablename. When this works, the login david should work correct and you should be able to create the administration tables in this database for your new DigaSystem database.
- Run DBM setup.
- Best practice: Use a separate folder for sound/media, e.g. D:\DsMedia and publish it like \\MyMachine\DsMedia\.
- Use Admin.exe from DBM setup.
- Open Admin with Administrator rights (to be able to change ODBC-connections).
- Create ODBC-connection (menu database - Manage Data Sources) to database DigaSystem by using System DSN with driver "SQL Server Native Client 11.0", choose Server ".\SQLEXPRESS", login "david" "digas" and database "DigaSystem" as default. Test of connection should work. (You could as well use the ""ODBC Data Source Administrator" from start of windows, it is not a functionality of the Admin.)
- Connect to database in Admin (maybe you have to re-start it before - it gets green after using the right credentials).
- Create administration tables with context menu on this ODBC-connection. - Which version? Version 3 is fine, version 4 when you want to use container (like groups over multiple tables). 5 has some more special features. You can always upgrade but you cannot come back.
- When following this plan to here strictly you did not re-start the computer, so you usually get errors now. I had "Cannot specify a column width on data type text." But it is not that dedicated special problem. It is simply the first try to access the database and this does not work. It is necessary to re-start the machine, the ODBC access to the SQL-server is working after re-start only. Special trick, some people don't believe, but it for many years.
- With using e.g. "SQL Server Native Client 11.0" you may need to create under Global settings\SQLAdmin\ a new folder "SQL Server Native Client 11.0". Copy complete content of folder "SQL Server Native Client 10.0" to new folder "SQL Server Native Client 11.0".
- Create administration tables should be done now.
Tables (with wizard)
- Create a (global) table e.g. Music:
- Best practice: Choose different folder for each table with the folder-name like the table-name. Publish a media directory D:\DsMedia with file explorer, choose the published folder, create folder Music inside.
- Use table wizzard of admin at tables.
- Name it Music, choose the created folder with server URL (not the local path) and the folder Music inside, e.g. "\\MyServer\DsMedia\Music".
- Don't specify more in the table-wizard if you are untrained.
- Give the users rights to the table, best practice is by groups.
- Open DBM and see the table.