SQL database structure recommendations

Ignition
1

Ignition 8, MSSQL

Hi All

Our ignition installation is in its infancy and I want to get the SQL database structure right at the start.

The factory is split into approximately 6 departments, with around 4 manufacturing cells per department, each containing a number of machines or other assets. Eventually, we will be aquiring data from most of the assets.

I currently have two schema, one is used as a historian (ignition tag historian) and the other is used for storing batch data for use in various reports.

943315
943315447×774 10.4 KB

Are there any recommendations for a good database structure for the setup above? Should I have a schema for each manufacturing cell and set this as its own historical data provider? I ask because I see the potential for issues with the current arrangement when we are logging all historical data to 1 schema.

Any advice would be great. Thanks.

2

I don’t see what having separate schemas for department historical data buys you. What potential issues do you see with a single schema for tag history?

Based on what you have shared, I think separating batch data into another schema makes sense because this schema couldn’t be automatically restored by Ignition.

I would probably make 2 schemas for the factory: 1 for tag history, and another for batch data.

3 Likes
3

There are lots of discussions on the sql server forums about schemas and their uses, and most of the pros I have found reliable tend to not use schemas much, if at all. We don’t use them, and have many very large applications running. They primarily are useful for segregating users by permissions – is this a problem for you and this app? I notice in your snip above that you have identically named tables, in different schemas. That looks to me like a headache waiting to happen.

2 Likes
4

Thanks to you both for the input. It sounds likle the recommendation is to keep the SQL setup as it is. I guess I was concerned that with significantly increasing the number of historical tags, the table size could become an issue. From the above it seems like this is not a concern. David, I can’t see 2 tables with the same name. The intent was to avoid this.

5

On closer look, I see you’re right. All are under the dbo schema, and there are prefixes to distinguish the manufacturing cell.

1 Like
6

Your SQL database structure doesn’t look so bad yet. My databases are not clear where it is not clear why. Do you happen to know how to sort all databases automatically?

7

Hi Wendy and welcome!

I’m afraid I don’t understand your question. Please can you rephrase it?

In my setup, the schema are created manually using Microsoft SQL management studio. For the reporting data, I also manually create the tables in management studio. For the historian schema, ignition will automatically create the tables (using the default settings) once a tag is configured as a historical tag.

8

And this is where I have a question or two:

I am attempting to use tag history with MSSQL, but I am not sure how. I get errors stating that the table (sqlth_drv) cannot be created.

In this particular development environment, I have a user (Ignition) that is also used for the Perspective to access the DB.

Can I, or should I, use this same user?
Can I change the schema the tag historian will write to, instead of dbo?
Are there any specific permissions required for the user, in the Securables tab in the SSMS?
And besides being a member of the ddladmin group, (so that Ignition can create the necessary tables), does it require any other memberships?

I have been playing with the permissions and memberships but I keep getting the same error, TagHistoryDataSourceLink; albeit this one occurs even after disabling the tag history on the tag I am testing with. Other errors included DatasourceForwardTransaction and MemoryForwardTransaction. These two, obviously, stopped by disabling the tag history.

EDIT:
I should also ask if I need to create a Database Table Historian in the Tag History on the Gateway, or can I just use a normal DB connection?

EDIT 2:
Without using the Database Table Historian, I am able to get Ignition to create a partitioned table in the DB, but the DatasourceForwardTransaction error and it's table (sqlth_drv) still cannot be created.

EDIT 3:
Well, it seems that the error in EDIT 2 must be related to another DB. I refreshed the tables in my working DB and found
image
So, it appears to be working. However, I am going to test using a separate MSSQL user login rather than the same one used for actual users.