Tips and tricks


SQL Server all current versions

Make extensive jobhistory more readable

In SQL Server backups and maintenance of databases is usually managed using jobs.
In this article I’ll explain how the history of these jobs can be read, and what you can do if the output is too long or hardly readable.

In the example below you’ll see the job that makes backups of the systemdatabases. I you, like me, are using the maintenance scripts made by Ola Hallengren then the job should look familiar to you. Of course this example works with all jobs.

Request the properties of the job ‘DatabaseBackup – SYSTEM_DATABASES – FULL’:

Click ‘View Job History’.
Click the plus-sign in front of the runtime you want to investigate to see the history of the step:

In the bottom of the screen you see the details of the run. The scripts of OLA provide extensive logging but it has a side-effect: it is sometimes hard to read the long text properly.

By default Ola configures logging to file, and I am a big fan of that feature. It makes troubleshooting a jobrun much easier.

However, sometimes I work at a client that does not allow me access to the underlying operating sytem. In those cases I need another way to get this important info. And (of course) that is possible, I just need to make a small adjustment to the job.

Open the job you want to adjust:

Click ‘Steps’

Select the job-step you need to adjust and click Edit (or doubleclick the job-step) to open the properties:

Click ‘Advanced’

Put checkmarks in front of ‘Log to table’ and ‘Append output to existing entry in table’
The second option is neede in jobs with more than one step to make sure you get the histrory of all jobsteps in the table (instead of just the latest one).
Click OK to save your jobupdate.
Done!

Now the fun part, read the history from our new source.
You just need a query to do that. Of course you’ll have to wait for the job to run to see any data, so you may need some patience.

select * from msdb.dbo.sysjobstepslogs
order by log_id desc

Find the log_id of the run you want to check out and copy it to the clipboard.
Adjust the output of the query so that it outputs to text instead of grid by clicking the button in the GUI or just type CTRL+T.
Next, execute the following query:

select log_id, [log] from msdb.dbo.sysjobstepslogs
where log_id=<gekopieerde log_id>
order by log_id desc

Sometimes you still don’t see the whole text; results to text by default only shows max 256 characters.
You can change that for this query to the max value of 8192 in the query-options:
Click in the main menu of SSMS ‘Query’ and ‘Query Options’:

Adjust the indicated field to the max value of 8192.
If you want to set this for all queries to come, set this using ‘Tools’ in the menu and then ‘Options’:

Of course there are cases when 8192 characters is not enough. We can use the  import-export wizard to export the output of the query to a destination we want, for example a textfile on the machine you use to run SSMS.
Click the right mousebutton on the MSDB-database that contains all jobrelated info:

Select ‘Tasks’, ‘Export Data…’

Select as a source ‘SQL Server Native Client’ and click ‘Next’

Select as ‘Destination’ the ‘Flat File Destination’ and enter a filename and path.
Don’t forget the set the checkmarks in front of  ‘Unicode’ en ‘Column names in the first datarow’ as shown above!
Click ‘Next’

Select ‘Write a query…..’ and click ‘Next’.

Fill in the query we used before and click ‘Next’.

Click ‘Finish’
The export is executed:

The results of the query can be found in the file you set as ‘Destination’.

Good luck!

 

 

Azure SQL Database

Resolving T-SQL differences before migrating to Azure SQL Database

Migrating to Azure SQL Database can sometimes cause headaches because of the differences between the on-premise SQL Server version and Azure SQL database.

Migrating to Azure SQL Database can sometimes cause headaches because of the differences between the on-premise SQL Server version and Azure SQL database. This may clear things up abit:

Bron: Resolving T-SQL differences-migration-Azure SQL Database | Microsoft Docs

SQL Server 2017

What’s new in SQL Server 2017

Recently Microsoft released the new SQL Server version, SQL 2017.
This version has a nice set of new features like support for Linux operating systems.

If you are curious, see the following link:What’s new in SQL Server 2017 | Microsoft Docs
or Bob Ward’s blog: https://blogs.technet.microsoft.com/dataplatforminsider/2017/09/27/sql-server-2017-fast-faster-and-the-fastest-database-everywhere-you-need-it/

AlwaysOn Availabilitygroups

Support for AvailabilityGroups in SQL Server involving cross-database and distributed transactions

AlwaysOn Availability Groups in SQL Server do not support cross-database transactions, DTC-transactions support is limited. The exact limitations depend on the SQL Server version.

Many applications use different databases that are connected, sometimes using code but also using database connections. In time a lot of cross-database queries or stored procedures that reference other databases may have been created and used. Or Distributed transactions may have come in use. These structures work very well in a scenario with only a single database-instance or a clustered installation of SQL Server (FCI). As soon as high availability is considered, Availability Groups are the target. Not everyone realizes though that although this technique is a good fit for a lot of databases, it’s not always the best choice.

Installation and configuration of Availability groups seems simple and fast. It has many advantages but it can also lead to unexpected issues that may be discovered when it’s already too late. Many companies discovered this the hard way.

In short:

AlwaysOn Availability Groups in SQL Server do not support cross-database transactions (yet), DTC-transactions support is limited. The exact limitations depend on the SQL Server version:

SQL Server 2014 and 2012

No support for cross-database transactions or DTC transactions. When using these in an availabilitygroup there is a risk of dataloss and corruption. Even more important: there is no way to discover it, the problem is caused by the way availability groups work; it only gets visible when you try to read data that’s not there or that is not consistent. At that time no repair will be possible anymore.

SQL Server 2016

Support for cross-database transactions and DTC-transactions is very limited and only supported in specific situations. See the following article to prevent unexpected surprises: https://msdn.microsoft.com/en-us/library/ms366279.aspx

SQL Server 2017
MSDTC-transacties worden bijna volledig ondersteund. Cross-database transactions ook, maar allebei met duidelijke beperkingen bij databasemirroring en (dus ook) availabilitygroups. Meer informatie: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/transactions-always-on-availability-and-database-mirroring

SQL Server Updates

Updates to the SQL Server Incremental Servicing Model | SQL Server Release Services

Install CU's when available, don't wait for a new servicepack.

In the old days every experienced DBA updated SQL Server with only the latest servicepacks. CU’s (Cumulative Update packages) were available, but the advise was to only install those when a problem occurred that was fixed in the CU. ‘If it isn’t broken, don’t fix it!‘.
Occasionally CU’s would break smoothly running installations…..

Well, those were the old days.

Starting in 2016 Microsoft performs tests on CU’s the same way as on Servicepacks, and advises us to proactively install CU’s when available to get the most out of SQL Server. ‘If it isn’t broken, improve it!

Read more here: Announcing updates to the SQL Server Incremental Servicing Model (ISM) | SQL Server Release Services

SQL Server Updates

New servicing model for SQL Server

Starting at SQL Server 2017, no more servicepacks!

As from SQL Server 2017 Microsoft announced no more servicepacks will be released.

Periodically CU’s (Cumulative Update packages)  and General Distribution Releases (GDRs) will be distributed.

If you want to read more about it, please check this post from Glenn Berry: https://www.sqlskills.com/blogs/glenn/modern-sql-server-servicing-model/

 

Tips en trucs afbeelding

SQL Server 2016

What’s new in SQL Server 2016

SQL Server 2016 has been release some time ago. This new version contains a lot of new features and possibilities, but you might wonder if it’s worth the effort of updating.

The answer to that question will be most likely be ‘YES’, but you need to be careful *).

If you’d like to know what has changed, I’ll refer you to the following link:

What’s New in SQL Server 2016 | Microsoft Docs

*  Before upgrading I advise you to check what the new version does to YOUR workload. With this new version that’s even more important than before. If preparations are not performed as needed, performance may suffer heavily instead of improve. These issues can be avoided though, and the performance can improve hugely. But that only works after thorough testing and the right preparations before migration.

SQL Server 2016

SQL 2016 SP1 brings enterprise features to other editions

In SQL Server 2016 SP1 features that used to only be available in the enterprise-edition are also available in the other editions, even in the express-edition!

In SQL Server 2016 SP1 features that used to only be available in the enterprise-edition are also available in the other editions, even in the express-edition!

Think of features as datacompression, partitioning and ‘always-encrypted’.

Unfortunately online indexrebuilds will still be an enterprise-only feature. Nevertheless the new features in the non-enterprise-editions can save companies a lot of money!

Be careful when you remove SP1 without thorough research when you are using the new extra features, this may lead to serious problems and corruption.

See also https://technet.microsoft.com/nl-nl/windows/cc645993(v=sql.90) and https://www.microsoft.com/en-us/sql-server/sql-server-editions

< previous pagina 1 van 0 next >