Happy Tuesday, blog reader.
Have you ever come across a database you had not seen before or was in a different naming convention than you are used to seeing?
Does this look familiar?
Databases listed with 1, 2, 3, 4? What do those databases represent? Where did those databases come from?
Would you believe me if I told you there was a way to identify where the databases were sourced from?
A setting that can be used in SQL Server would allow the person who built these databases or restored them from another system to add a note.
Enter the Extended Properties configuration setting for each database.
Microsoft Docs says this property allows you to add custom properties to database objects.
This means that if you have automation building or restoring databases, you can run code to populate these properties to help identify the source and need for the database. Let’s take a look at a couple of examples.
If you have never worked with extended properties, you can access the information in SSMS by right-clicking on the database in question and choosing Properties. Then click on the Extended Properties page.
In this example, you can see that the database was restored on 10/10/2022. This is helpful information, but we can take it further and add a Source property.
EXEC sp_addextendedproperty @name = 'SourceSystem', @value = 'PROD.sqlserver.com'
Now we can see that the database came from the PROD server.
For another example, what about using Extended Properties for tracking database deployments?
EXEC sp_addextendedproperty @name = 'Code Branch', @value = 'Release 99' EXEC sp_addextendedproperty @name = 'Deployment Date', @value = '10/10/2022' EXEC sp_addextendedproperty @name = 'Version', @value = '2.0.13' EXEC sp_addextendedproperty @name = 'Deployment Notes', @value = 'http://vsts.microsoft.com/Release_99'
The WWI_4 database came from a database deployment. We have the Code Branch, Deployment Date, Deployment Notes, and Version. This gives you a specific place to go and review the database-related properties.
Conclusion
Extended Properties is a valuable tool in SQL Server that can help with database documentation. Other things can be done with Extended Properties that can be helpful beyond what was displayed in this demonstration. I encourage you to consider your environment and where Extended Properties might be a good fit.
Microsoft Learn – Extended Properties
The post Where did this database come from?? appeared first on GarryBargsley.com.