EasyChart to display data spanned across two or more db

Ignition
1

Hi,

Let’s say we have a bunch of SQLTag History.
An easychart to display them.
Everything works ok until the site’s DBA come to play with us.
The DBA want to have a separate db for each year, but on the same SQL Server (so bad, otherwise, we’d used the linked server property of MSSQL).
And the user still want to use the same behavior as with the easychart.

What are the options ?

One we have investigate is to use classic chart with system.tag.queryTagHistory but this needs to have as much db connexion on the gateway as number of years to hold (as for now, 4). And that the db connexion cannot be created dynamically (or I missed something).

Thank you for your help or ideas…

2

That is a odd request. Right now Ignition creates separate tables for each month of history data. You cannot dynamically change the database a connection is using. You would have to do something manually? The easy chart can only query history from a single database.

Why don’t they just move older data to separate databases leaving the current one alone?

3

Travis,

Just because the db are too big for the backup jobs ; one year of data is about 10gb. So keeping 3+ years in one db is just not possible according to the IT dept.

:scratch:

4

I’d say this is a good time for getting IT practices updated if you can’t backup 30GB, no? We’ve had 110GB Super DLT tapes available since 2000, or you know, put it on network attached storage…

Partitioning the table could keep the table while splitting everything into multiple underlying filegroups. Also, this is venturing into the enterprise grade SQL Server but has your DBA looked into sharding (“federating” in Microsoft lingo IIRC) the historical tables?

5

@ gbuehler,

Yes, probably, but it’s a large administration with more than 2000 pcs spread on the site. The volume of data is very important and we have absolutely no way to change things today. As far as I know, data are backed up every night (incremental) and every week (full).

Even moving data on other servers is not an option. Things are that the IT dept. is not very fair as the project is in the energy dept…War War War…

6

Hi,

I think you can probably accomplish this, but I’m not sure you’ll also be able to use Ignition’s built in partitioning. Instead, you’ll set Ignition to partition on 1 year, and then set up the tables yourself, according to the names that it would create (“sqlt_data_20120101”).

Since the different databases are on the same server, you should be able to reach them through fully qualified names. So, you’ll have one database that is the “controller”, which is the one Ignition is set up to look at. It will have the “sqlth_partitions” table, which is used to associate data tables with time. Once you make the other databases, with a table for the data, you’ll add entries here for each year. The start_time will be Jan. 1 at midnight, and the end time will be Dec 31st, midnight-1 second, for each year. So, for example:

“data2012.sqlt_data_20120101”,1325404800000, 1357027199000
“data2013.sqlt_data_20130101”,1357027200000, 1388563199000
“data2014.sqlt_data_20140101”,1388563200000, 1420099199000
(come back in 2.5 years for the other times, if you need them :slight_smile:)

If you’re creating the tables yourself, the lack of built in partitioning isn’t exactly a problem, because as gbuehler mentioned, there are better facilities built into SQL Server that you could use.

As for whether or not the data should really be in different databases, it’s up to the DBA. I suspect he wants to do that because sql server naturally creates file groups per db (I think). You could keep all the data in the same db, using this same technique of pre-creating the tables, and manually create a file group for each table, but fundamentally everything would end up more or less the same.

Hope this helps (and works, since I’ve never tried it!),

7

Hi Colby,

Just to say thanks and that is the solution. Worked great.