I have just used SQL notebooks today for a SQL migration from SQL Server 2000 database to SQL Server 2016 with many, many updates to code, procedures, configuration changes, linked servers and thought I would share some thoughts on using SQL notebooks for production change.
I had 30 + code sections with multiple steps, including scheme change, data fixes, and 50 odd stored procedure updates. I had for each one a text section with what the change actually was, using markup sections.
There were several steps that I couldn’t run from the main SQL notebook but I annotated the code that needed to run, as this was a SQL 2000 migration so required backup/restore to SQL 2008 before the leap to the future and SQL 2016.
It was all contained in one document and I could still see the steps not available to run in SQL notebooks I just had to just copy and paste, either Enterprise Manager or PowerShell and execute that element.
This was a complex upgrade and things might need tweaking as running through each section some steps couldn’t run easily in test environments, so it was expected that some tweaks would be needed this was where SQL notebooks are so much better than running stand-alone scripts.
So anything that changed from what was planned I added a new text section and added comment using the emphasize option, so I can add notes when things don’t work as planned for the record.
SQL Notebooks advantage over running 30+ separate SQL scripts
- it’s obvious where you are up to
- you can step through and run each section easily
- the result set is retained for each section
- you can save all the result with the scripts
- you can annotate anything that needs tweaking if it doesn’t work as planned as you go along
any gotchas/changes? Yes
- make sure you specify the database context your running in, so USE [Database]
- there were a lot of commands completed successfully. I’d consider adding in print statements to keep a better record, everything succeeded but that might not have been the case.
- don’t be caught by trying to highlight a section of code in a notebook, it won’t run the section you have highlighted but the whole section