The link to watch Webinar #1.1: Metadata Overview can be found here: Webinar #1 – SQL Server Metadata Overview
Once you complete the registration information, you will get an email with a link to the webinar. Yes, it’s a little tedious, but the marketing team says I have to use this link. 🙂 The zip file that includes the presentation and demo scripts is located at http://www.dbbest.com/download/SQL-Server-Metadata-Overview-Files.zip.
As I mentioned when telling you about my webinar series, I will be giving out homework. Of course, it is totally optional, but the problems give you an opportunity to put into practice some of the concepts discussed in the webinar. Here are the problems from the first webinar: Metadata Overview.
List the names of each database that is in SIMPLE recovery mode
In the current database, list the name of each user table along with the number of indexes it has.
In the current database, list just the catalog views.
Here are my solutions. As with most queries, there may be more than one way to write a TSQL statement to get the desired results.
This past Tuesday, the 19th of September, I started broadcasting a series of webinars which cover all the material in my SQL Server Internals classes that I’ve been delivering around the world for almost 20 years. I’ll even be able to add additional information not included in the classes because our time constraints are not quite so strict. As Microsoft added new features to the SQL Server product, I had to remove some still relevant content to make room for the new information, and now, I’ll be able to put that content back in. In addition, you won’t be sitting just listening to me for 5 days straight. We’ll take it a bit at a time, in manageable chunks, and if your brain goes sideways, you’ll be able to rewatch later. You’ll still be able to ask questions, just like in my regular classes.
Before SQL Server 2016, configuration referred to instance-wide settings. But with Azure SQL Database not giving us access to any instance-level information or giving us any instance-level control, we only can see information and control settings for a specific database. So now, a new SQL Server 2016 feature allows us to have a bit more control of individual databases. And once a feature gets tested and verified in Azure, it’s ported to the SQL Server on-premises code. We now have ALTER DATABASE SCOPED CONFIGURATION which does not take a database name and always only applies to the current database.
Some of the settings available with this command were not configurable from any kind of normal command or procedure prior to the addition of this new command, and some were undocumented. For example, clearing the plan cache was a DBCC command. Changing the cardinality estimation model and opting to use optimizer hotfixes were trace flags.
So far, there are only six options available, but I’m going to guess this number will increase in the next version, if not the next service pack.
The option I’ve used the most often is
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE
One of the largest transportation authorities was running the mission-critical system on SQL Server platform and any down times could harmfully affect on their business. So, they decided to modernize their system and implement high availability and disaster recovery solutions to keep their system running without interruptions. To meet the customer’s needs, we performed technical support and delivered detailed instructions and automated scripts to implement the highly available architecture in compliance with Always On Availability Group checklist. Our solution allowed the customer to build perfectly configured highly available system by themselves while saving configuration time and money.
Long ago (in SQL Server years) many metadata changes were implemented with special one-off stored procedures. For example, we had sp_addindex and sp_dropindex, as well as sp_addtype, sp_droptype, sp_addlogin and sp_droplogin. For changing certain database properties, we had sp_changedbowner and sp_dbcmptlevel, to name a few.
Gradually, Microsoft has started replacing these procedures with the more generic DDL commands ADD, DROP and ALTER. This is both good news and bad news. It’s good because these commands are more efficient and more predictable. It’s bad because the stored procedures were made up of T-SQL code that we could read and actually learn things from! I learned a lot of what I know about SQL Server metadata in the early days by reading the definitions of the system stored procedures.
Some of the changes sort of snuck up on me. I knew for example that sp_dboption had morphed into ALTER DATABASE, but I just discovered recently that you don’t even need to specify the database name. You can use the word ‘current’ to indicate the current database. This change was introduced in SQL Server 2012, but no one told me.  For example, we can run the following:
ALTER DATABASE current SET READ_COMMITTED_SNAPSHOT ON
However, this doesn’t apply to all options. And it’s not clear which ones.
A global pharmaceutical company has been supporting hundreds of applications on the Oracle platform. Their complex system became outdated over the years with 90% of end of life or extended support Oracle versions with very expensive contracts. They needed a cost effective solution, so they looked for a way to move their workloads to a higher value SQL Server solution. See how we helped our customer determine Oracle migration ROI (Return on Investment) for their highly complex environment.
Let’s see how we approached the analysis to demonstrate that our customer could achieve an Oracle migration ROI within 1-year by moving to SQL Server.
We’re happy to announce the latest release of DB Best Database Compare Suite. The new version marked 4.11 brings support for Vertica Analytics Platform. Learn more about the ultimate DBA tool and discover the major updates implemented in the latest release from our blog post.
DB Best will be hosting a series of FREE workshops in the Midwest region with Kalen Delaney presenting. The workshops will focus on ‘Getting the most out of the SQL Server Engine”.
Kalen Delaney is a SQL Server MVP, renowned author of the SQL Server Internals book series. With over two decades of SQL Server experience, Kalen has been a frequent speaker and authority on all things SQL Server. The workshops will include extensive Hands On Lab sections facilitated by experts from DB Best Technologies.
The Midwest SQL Server Tour will include the following cities:
Even though Harvey’s impact will be felt mainly in Texas, it has been having a big effect on my life here in the Beautiful Pacific Northwest today. I was planning on traveling to Houston next Monday, the 28th, and then on to Austin and Dallas, for a series of workshops, which I was calling my Texas Tour.
The worst of the storm might be over by Monday, but I am well aware of potential flooding problems in Houston post-storm. And if the storm moves further inland, the other cities could also be impacted.
So, with regret, I need to announce that DB Best is cancelling my Texas Tour next week. Hopefully, we’ll be able to reschedule in the not too distant future.
DB Best recently completed a project to migrate Sybase ASE databases to SQL Server for a global leader in security technology. Faced with skyrocketing license costs with SAP to renew their out-of-support server instances, our customer needed a way to migrate hundreds of Sybase ASE databases.
To make matters worse, our customer faced upgrading their out of support PowerBuilder applications.
By migrating to SQL Server on commodity Intel based servers, our customer found that they could exceed their performance needs and lower overall licensing costs. Check out the video below to see how we approached the project to migrate Sybase ASE databases to SQL Server.
