My challenge was how to manage all SQL Server instances, databases and have a complete inventory of my environment. SQL Server Management Studio is a tool will allow me to view data per instance, so I have to connect to each instance and browse the properties of each instance but not from a central view. I needed a tool that I can see total number of Servers with summary that I can drill down by environment by business unit down to an object level. I want to look up a user permission across SQL Servers and level of access, I need to know how many databases, objects and users are by environment i.e. Production, test, dev and QA, how much total storage my SQL Server are using with drill down capability and break it down by file type and display growth trending.
It is related to the database. If anyone wants to learn MSBI then he can go for MSBI Online Training modules that help him to gain additional knowledge in this field of technology where he gets interactive and exclusive training.
Long story short, I needed a centralized management view of my SQL server environments that I can have a complete inventory that can answer my previous questions.
I began building a centralized reporting system by creating a master repository database on a SQL Server instance. The repository would store data about every SQL Server instance, database, object, and user in an enterprise database environment.
I developed a custom an SSIS package that collects a plethora of SQL data with details on SQL server Instances and databases, and store the data in a central database.
After data is in the repository, you can use Reporting Services to view the data. I developed different dashboards and reports in SQL Server Reporting Services to view instance, databases, objects and users data. This solution helps me monitor the status and inventory of 100’s to 1000’s of databases and assess how big my environment is and have access to enterprise data from centralized reports.
If you want to learn more about MSBI, then The Complete MSBI Onine Training India Course is a great course, to begin with.