PASS Summit Speaker Idol is here, and the window to submit is closing

Published On: 2018-07-31By:

While yes, the PASS Summit is a few months away still, the window for submitting for the PASS Summit 2018 Speaker Idol competition is starting to close. As I PASS Logoannounced in June all entries to this years speaker idol need to be submitted by September 8th, 2018. That’s just a little over a month away. This give me time to review the submissions, notify the contestents, have a call with them, and give them time to make their presentation for the PASS Summit.

If you’re an aspiring presenter who’s looking to learn from some of the best presenters out there, then this is for you and you need to sign up.

Denny

The post PASS Summit Speaker Idol is here, and the window to submit is closing appeared first on SQL Server with Mr. Denny.

Why has my Azure Load Balancer stopped working?

Published On: 2018-07-28By:

Azure and the load balancers can be annoying little things. Especially when you are doing maintenance on your Azure environment, or setting it up for Cloudflare being the cloudthe first time. One of the quirks you may run into is that if you leave an Azure Load Balancer sitting for to long with no machines behind it, you may find that the load balancer itself just stops working.

Thankfully fixing this is actually pretty straight forward. Simply delete the load balancer from Azure and recreate it. Now you may be worried about dropping the IP address that it’s configured with, and don’t worry, you don’t have to. If it’s an Internal Load Balancer (for a failover cluster or a SQL Availability Group for example) then it’s just a private IP and you can just reassign the same IP when you recreate it. If it’s a public IP, you don’t need to drop the public IP address object from Azure, which means that the public IP address will stay right where it is. You can just reuse the same IP address object and you’re good to go.

It’s an easy fix to an annoying problem. For internal load banacers (ones with private IP addresses) they can be really hard to troubleshoot as there’s next to no logging done on an internal load balancer.

Denny

The post Why has my Azure Load Balancer stopped working? appeared first on SQL Server with Mr. Denny.

General availability: SQL Database auto-failover groups – and it looks awesome

Published On: 2018-07-17By:

A really great feature in Azure SQL DB went GA today. That feature gives you and SQL DB the ability to automatically fail databases over to a Secondary replia, without having Cloudflare being the cloudto configure your application to handle that failover. You point your application at a VIP and that VIP will automatically handle failover of the resource.

Say for example you have a database in US West named db1-west.database.windows.net and the DR copy of it in US East named db1-east.database.windows.net.  This feature lets you create the VIP db1-vip.database.windows.net which automatically points to whichever database is currently active.  In the event of a failover of US West, the VIP is going to failover to the database in US East, the database in US East become writable and when the US West is back up, the data will sync back.

Another cool thing which this feature does is something that most features won’t do, it’ll trigger a failover that allows for data loss. Now, this normally would be a very dangerous thing, but the Azure team has come up with a safe way of doing it. When you figure the service to do the failover, you decide how long you want to wait for there to be no data loss. If you want the system back up as soon as it allows for, select the smallest number, otherwise select a larger number. This allows you, and the business unit that you support, to decide what level of protection you want to have built into the system.

If you are thinking about moving to PaaS, not being able to have a DR option may have been stopping you. This is no longer a blocking point, you now have an easy to configure DR, that you can manually failover is need be.  If you’re thinking of moving to Azure, DCAC can help to plan and execute that migration. Contact us today, to schedule a meeting to discuss if the cloud is right for you.

Denny

The post General availability: SQL Database auto-failover groups – and it looks awesome appeared first on SQL Server with Mr. Denny.

Exchanging SQL Certificates with different service accounts can be tricky

Published On: 2018-07-10By:

Setting up Transparent Data Encryption, or Backup Encryption, or basically anything else where you need to restore a certificate to another SQL Server instance can get tricky as soon as you run the instances under different service accounts.  The problem that people will usually run into is that all the example code assumes that all the instances are running under the same service account, but this isn’t always the case in the real world.  So let’s review the code that Nic posted to Stack Exchange earlier today.

/* Server 1 */
/* Create the master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomeRandomSecureString’;
GO

/* Create the certificate to be used for backups */
CREATE CERTIFICATE BackupCert
WITH SUBJECT = ‘Backup Encryption Certificate’;
GO

/* Backup the master key */
BACKUP MASTER KEY TO FILE = ‘\\FileShare\DatabaseMasterKey_Master.key’
ENCRYPTION BY PASSWORD = ‘SomeRandomPwd’;

BACKUP CERTIFICATE BackupCert TO FILE = ‘\\FileShare\BackupCert.cer’
WITH PRIVATE KEY (FILE = ‘\\FileShareBackupCert.pvk’,
ENCRYPTION BY PASSWORD = ‘RandomEncryptionPwd’);
GO

/* Server 2 */
/* Create master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomeRandomSecureString’;
GO

/* Restore the cert */
CREATE CERTIFICATE BackupCert FROM FILE = ‘\\FileShare\BackupCert.cer’
WITH PRIVATE KEY (FILE = ‘\\FileShare\BackupCert.pvk’,
DECRYPTION BY PASSWORD = ‘RandomEncryptionPwd’);

–Msg 15208, Level 16, State 6, Line 32
–The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

/* Try restoring the master key instead */
DROP MASTER KEY;

RESTORE MASTER KEY FROM FILE = ‘\\FileShare\DatabaseMasterKey_Master.key’
DECRYPTION BY PASSWORD = ‘RandomEncryptionPwd’
ENCRYPTION BY PASSWORD = ‘RandomEncryptionPwd’;

–Msg 15317, Level 16, State 2, Line 39
–The master key file does not exist or has invalid format.

Now as we can see, the creation of the certificate failed, but only when running each server under its own service account. The question becomes why.

The answer is depressingly simple, NTFS permissions. By default SQL Server will make it so that the backup of the certificate is only available to the account that is running the instance that creates the certificate backup. So if you need to restore the certificate using the CREATE CERTIFICATE syntax shown in the example you’ll get an error. If you change the permissions so that the second SQL Account can read the certificate backup file you created then the restore will work.

Denny

The post Exchanging SQL Certificates with different service accounts can be tricky appeared first on SQL Server with Mr. Denny.

1 3 4 5 6 7 334

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.