marcingminski
commented
6 years ago Thank you for the kind words! :) I have some ideas for the centralised repo. This could be approached in two ways:
Same schema
I could introduce a concept of a server name to each table which would be part of the composite primary key. On the central instance the [dbo].[sql_perf_mon_server] would be a driver for the data import from "satellites". We would simply add servers we want to import data from there. There will have to be some ETL service running on the central instance of course.
This would increase the size of each "satellite" database but would allow keeping one and universal schema.
The "centralised" instance would be exactly the same with the only difference of having ETL running and more servers defined in the [dbo].[sql_perf_mon_server]
Dedicated schema A slightly different schema, with a server name and perhaps some specific configuration for the central instance. This would not require adding server name columns to the remote databases and would add server name during the import but it would require maintaining two schemas which may not be a great idea.
I am leaning towards option 1 but need to think a bit more of potential benefits of option 2
On the deployment subject - agree. It will be a pain. I am really keen on VS project and let sqlpackage.exe handle deployments. I have been doing this for a few years now with a great success and cannot really go back to script deployments but I want this to be easy for people to use.
So - if we make PS wrapper for sqlpackage.exe it will make it easy to deploy right?
Hi Marcin,
Great project - and one i think a lot will find value from!
Agree with Chrissy's comments - also some ideas from me:
A mechanism for people to raise new feature requests or bugs via Issues (dbatools have a great mechanism in place for this already, perhaps this could use something similar?)
Potentially issue with setup.sql later down the line if you make changes If you were to make changes to setup.sql later down the line and we deployed out to a server which already had those changes deployed it currently wouldn't upgrade some objects to the new version. This is less of an issue i guess while this is deployed to tempdb as it would be dropped if the instance restarted :) Could it be moved to SSDT with a PowerShell cmdlet to push out changes to servers?
Centralised perf repo Would be great to monitor multiple servers and push that data into a centralised repository. For this, you'd need to define a standard mechanism to pull the data from the source servers and enter into a centralised repo. You could then hook the PowerBI reports into the centralised repo. It would be great if this centralised repo could be on-prem or in the cloud, so that we could hook PowerBI reports straight into DB from Azure for example.