How easy is it to deploy your database to the cloud?

Posted by Pieter on May 7, 2012

Posted in: SQL 2012, SQL Azure. Leave a Comment

With the current hype in cloud computing, DBA’s want to have an easy way to deploy their database to SQL Azure. In previous versions, migrating a database to the cloud was a significant undertaking. You could download the SQL Azure Migration Wizard which actually created some scripts to move your database. Microsoft also provided following solution Migrating Databases to SQL Azure. Other possibilities are BCP, building SSIS packages, “Generate script wizard”,…

I was wondering why it has to be that “difficult” and “complex”. People are still using these solutions, which are certainly not bad, believe me, but…Now. with SQL Server Management Tools in SQL 2012 you can use the Deploy Database to SQL Azure feature, which under the hood exports a bacpac and then imports it to SQL Azure. It makes the life of the DBA much easier. Let’s see how this works.

Open the Object Explorer and go to your database that you want to deploy. Right click on it and select Tasks – Deploy Database to SQL Azure

You should now see an introduction screen of the deploy wizard. Just click next…

Now you need to specify the deployment settings. First of all, you need to connect to your SQL Azure Server. Click the connect button.

Enter your SQL Azure credentials and click the connect button. Please note that the connection to a SQL Azure server in some companies can fail. Probably because of firewall settings. …this company is not ready yet for the cloud… Denkende emoticon .

Once you are connected, specify the correct database name.
Select the Edition (Web or Business) of SQL Azure and the maximum database size that you need. For more information on SQL Azure and Pricing check out this link

When you click next,you will get a summary of the specified settings

Click Finish to start the deployment to SQL Azure. The wizard will first evaluate your database if it meets all the requirements of SQL Azure. If not,it will fail, as you can see below Knipogende emoticon .

If you click on the “error link” to verify what is wrong. In my case, I have a table with no clustered index. That’s not allowed in SQL Azure. Check the following page if you want an overview of the Guidelines and Limitations (SQL Azure Database)

You need to restart the wizard all over again. Microsoft didn’t provide a retry button here…
I have corrected my database and retried the deploy again. Successful! As you can see, the wizard is first exporting the database, the schema and the data itself. Secondly a database on the SQL Azure is created, schema and data are imported.

Now let’s verify if my database is indeed deployed correctly.
When I check the Windows Azure Platform, I find indeed my new deployed database

Conclusion: The other deployment tools were very useful in previous versions but when you have installed SQL Server 2012, I really should recommend this new feature.

How to Audit SQL Audit

Posted by Pieter on March 28, 2012

Posted in: Audit, SQL 2012.

While I was preparing my session for my next TechNet Live Meeting – Who did what and when on my database?, I was curious how you could audit the SQL audit. Actually, my goal was to be notified if my audit object layout was changed, disabled or dropped.

First thing that you can do is configuring the action group AUDIT_CHANGE_GROUP in the server and database audit specification. This action group will log an event whenever and audit is created, modified or deleted. OK, fine, nice feature, however… If somebody changes or disables the audit, I will not be notified about it. It will be in just the audit log… Unless I verify the audit logs, I will never be aware about the changes that have been made.

So I was thinking of a way how I could be alerted in a fast way if somebody or something modifies the server or database audits. I know that there are other tools that are made for monitoring and alerting. For example, Microsoft System Center. A possible solution would be writing the audit events to the application log or security log of the SQL Server. The System Center Operations Manager is dedicated to monitoring, reporting and alerting on Windows event logs. So, problem solved! However, not every small company has those tools available…

I was looking for a solution within SQL Server to send me an alert if my audit object has been changed.

” … to send me an alert…”. SQL Server Agent can handle alerts!

Let’s see how we can configure this. I have the following configuration

Server audit with name “Server Audit”
Server audit specification with name “Server audit specification”
Database audit specification with name “Database audit specification”

I’ve created 2 alerts, one that will capture all the modifications on my server audit objects and one that will capture the changes on my database audit object. I have used the event classes Audit Database Object Management Event Class and Audit Server Object Management Event Class for my WMI query. Be aware when you use the where_condition that you can only use the = operand together with DatabaseName, SchemaName and ObjectName. Other expressions cannot be used with these event properties.

Audit Server Alert

Audit Database Alert

The response of each alert is the execution of a job. The job is going to gather some information about the event as will send an E-mail. You can find the script below to create the job.

USE [msdb]
GO

/****** Object: Job [Send Alert]    Script Date: 28/03/2012 14:56:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]]    Script Date: 28/03/2012 14:56:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’[Uncategorized (Local)]‘ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’[Uncategorized (Local)]‘
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Send Alert’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N’[Uncategorized (Local)]‘,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Mail]    Script Date: 28/03/2012 14:56:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Mail’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’declare @Msg varchar(8000)
declare @sub varchar(128)
declare @State smallint
declare @Statename varchar(128)
declare @StartTime varchar(30)
declare @Description varchar(512)

SELECT @Sub = ”Server Audit State of $(ESCAPE_NONE(WMI(ServerName))) changed!”
set @StartTime=SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,0,5) + ”-” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,5,2)
+ ”-” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,7,2)
+ ” ” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,9,2)
+ ”:” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,11,2)
+ ”:” + SUBSTRING(”$(ESCAPE_NONE(WMI(StartTime)))”,13,2)
SELECT @Msg = ”ObjectName: $(ESCAPE_NONE(WMI(ObjectName)))”
+ CHAR(13)
+”Hostname: $(ESCAPE_NONE(WMI(Hostname)))”
+ CHAR(13)
+”Loginname: $(ESCAPE_NONE(WMI(Loginname)))”
+ CHAR(13)
+”NTDomainname: $(ESCAPE_NONE(WMI(NTDomainname)))”
+ CHAR(13)
+”NTUsername: $(ESCAPE_NONE(WMI(NTUsername)))”
+ CHAR(13)
+”StartTime: ” + @StartTime
+ CHAR(13)
+ ”Executed Statement:”
+ CHAR(13)
+ ”$(ESCAPE_NONE(WMI(TextData)))”
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ”Pieter”,
@recipients = ”pieter.vanhove@kohera.be”,
@body = @Msg,
@subject = @Sub’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N’(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Remark: Make sure that Database mail is configured correctly and that the setting “Replace tokens for all job responses to alerts” is checked. You can find this setting by selecting the properties of the SQL Server Agent (SSMS –> Right click on SQL Server Agent –> Properties) and go to the Alert system tab.

I know that there are still some gaps in this solution, like “What will happen if somebody disables the alert or stops the SQL Server Agent”, but at least this blog can be considered as a start…

Happy auditing!

Move Cluster Group is back!

Posted by Pieter on March 15, 2012

Posted in: Uncategorized.

I recently installed a Windows Server 8 beta cluster on my laptop. While I was exploring some of the new features, I noticed that Microsoft has put back the possibility to move the “cluster group” in the Failover Cluster Manager.

Since Windows Server 2008, it was not possible anymore to move the “cluster group” with the GUI of the Failover Cluster Manager. If you wanted to move it, you had to use the cluster.exe command.

cluster group “Cluster Group” /move

When I checked the Failover Cluster Manager I found the following option “Move Core Cluster Resources” on the “Actions” tab of the cluster itself.

You even now have the possibility to choose to which node you’re going to failover in case you have more than 2 nodes, which is also a nice improvement.

Looks like Microsoft missed this feature after all and I think they will make a lot of people happy with this!

Snippets and Template Browser in SSMS

Posted by Pieter on February 21, 2012

Posted in: SQL 2012, SQL Server Management Studio, Scripting, Snippet, Template.

While I was working one my presentation for 12 hours of SQL Server 2012 I noticed a new feature of SQL 2012 called Snippets. These code snippets are small templates that can be used as a starting point when building your queries. They can also be pretty handy for junior DBA’s that are not pretty sure about the syntax of certain statements.

Let me give an example. I want to create a table Person with following structure:

Field	Type
PersonID	int
Last Name	varchar(50)
First Name	varchar(50)
Age	smallint

Right click in your query windows and select “Insert Snippet” or just use the short keys CTRL+K, CTRL+X

The snippet window opens. Just double click the type of snippet that you want to insert, in my case Table

As result you get the following code. As you can see, the snippet has some replacement points which are marked in yellow

Now, go through the template with “tab” and change the replacement points into the appropriate values that you want. The replacement points remain active until you “close” the snippet. Just press “Enter” to close it.

My example,

CREATE TABLE dbo.Person
(
PersonID int NOT NULL,
Lastname Varchar(50),
Firstname varchar(50),
Age smallint
);

But… Didn’t we have this feature in older versions? I thought templates were actually providing the same. But in fact, there are some differences. Let’s follow my example again, and create the same table by using the templates.

In the menu click on View – Template Browser

In the template browser, select Table and double click on Create Table

You’ll get the following result in your query window

– =========================================
– Create table template
– =========================================
USE <database,sysname,AdventureWorks>
GO

IF OBJECT_ID(”<schema_name, sysname, dbo>.<table_name, sysname, sample_table>”, ”U”) IS NOT NULL
DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

You can use the Replace Template Parameters dialog box to specify values any time a parameter definition is used in code. Or just change the code manually.

As you can see the sample code that is provide by default is slightly different. In the template code, they added the IF clause to determine if the table already exists or not. The template also provides more detailed information on the create table statement like identity, data types, constraints and primary keys,…

A snippet is really something you can use as a starting point when building your queries, I call it a quick insert of a SQL statement, while a template provides a more detailed pre-written SQL statement. Templates are highly useful queries to jumpstart some of the “not-so-familiar tasks”. They also come in handy when writing deployment scripts. Templates are also a place to store and organize your own parameterized queries. I personally, used templates a lot when I was working as a junior DBA. Once you have used them a lot, you will know them by hart Knipogende emoticon

Restore Database Enhancements in SQL 2012

Posted by Pieter on January 26, 2012

Posted in: Restore, SQL 2012.

In SQL 2012, Microsoft has introduced some nice restore database enhancements.
The major enhancements are:

Point-in-time restore has now a visual timeline that allows you to quickly select the target time and perform your restore.
Page Restore worked already in SQL 2008 (R2) and SQL 2005 but it has now a nice user interface. It allows you to check your database for corrupt pages and restore them from a good backup file.

In this blog, I’ll give you an overview how to use these 2 new features.

Point-in-time restore

In the Object Browser of your SSMS, right click on Databases and select “Restore Database”
In this example, I will perform a restore of the Adventureworks2008R2 database. I selected Device to get my backup files. Just press the […] button

In the locate backup file window, I select all the backup files (Full backups and Transaction Logs) that have been made. To create the backups, I just created a simple Maintenance Plan.
Click on OK.

Now all the backup sets are in the list (this is not new…). As you can see, there is a new button called “Timeline”. Click on it to open the timeline interface.

Now, you can choose to restore to the last backup taken or choose a specific date and time. With the timeline, you can scroll to the restore time that you want. On the timeline you can also see what types of backups will be used to perform the restore. Once you selected the correct time, just press the OK button.

Now press OK again, to start your restore. A restore plan is automatically generated and your database is restored till the requested time.

How cool is that!

Page Restore

To perform a page restore,I first need to have a corrupt database Knipogende emoticon and you also need to have a GOOD backup file,which means, without the corrupt page.
As you can see below, I did a DBCC checkdb and my database is indeed corrupt.

Let’s fix this database!
Right click on your DB, select Tasks – Restore – Page

In the Restore Page window, the database is selected and the Pages grid is automatically showing the damaged pages. You can also run DBCC CHECKDB, by clicking on the button “Check Database Pages”, to find out if there are more damaged pages in the database. You also need to set the location for the Tail-Log backup file. The Backup sets grid shows you all the backups that can be used to fix your pages.

Just click on the OK button to start the page restore

When I check my database again with DBCC CHECKDB I see that the damaged page has been fixed Emoticon met brede lach

I think those 2 new features will make the life of the DBA just a little bit easier.

Have fun!

How to move the Available Storage group in a Cluster

Posted by Pieter on January 12, 2012

Posted in: Failover Cluster.

I’m working on a fully automatic silent installation of an SQL 2008 Failover cluster. This include a lot of testing, believe me Knipogende emoticon After each uninstall, I always had to move the available storage to the correct node to start my installation. I found it very annoying that this is not possible in the Failover Cluster Manager tool. As you can see below,when you click on the storage or on a specific disk, there is no option available to move the Available Storage group to another node.

So the only option that I had was rebooting the server so that the storage group did a failover Verwarde emoticon
It takes a lot of time to reboot so there must be a faster way.

And there is one! Pretty simple in fact…

Just open a command prompt and execute the command below where <Nodename> is the name of the server where you want to move your storage.

cluster group “Available Storage” /MOVE:<Nodename>

Conclusion:The cluster command has more options available than the failover cluster manager. So, if you want to perform a certain action on your cluster and it’s not available in the failover cluster manager, don’t forget to check the cluster command. Maybe you’ll find the solution there.

Configure Cluster MSDTC with Powershell

Posted by Pieter on December 28, 2011

Posted in: Failover Cluster, MSDTC, Powershell, Scripting.

Today, I configured a new SQL Server Failover Cluster with command line. The goal is to have a fully automatic installation of a SQL Server Failover Cluster. By using Powershell, I was already able to add the MSDTC resource. My next challenge was to configure it automatically Knipogende emoticon

I figured out that the MSDTC settings were in the registry under HKEY_LOCAL_MACHINEClusterResources. The problem was that the folders below this registry key are having an ID, so I had to find a way to know which one is used for MSDTC.

Well, I managed to configure it with the following script.

Import-Module FailoverClusters;
$InstanceName=$args[0]
$msdtcKey = Get-ChildItem “Registry::HKEY_LOCAL_MACHINE\Cluster\Resources” | Where-Object {([string](get-itemproperty -path “Registry::$_”).”Name”) -match “MSDTC-SQL Server ($InstanceName)”}
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC” -name “AllowOnlySecureRPCCalls” -value 0
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC” -name “TurnOffRpcSecurity” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “NetworkDTCAccess” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “NetworkDTCAccessClients” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “NetworkDTCAccessInbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “NetworkDTCAccessOutbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “NetworkDTCAccessTransactions” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “XATransactions” -value 1
stop-ClusterResource “MSDTC-SQL Server ($InstanceName)”
start-ClusterResource “MSDTC-SQL Server ($InstanceName)”

Let’s have a detailed look

$msdtcKey = Get-ChildItem “Registry::HKEY_LOCAL_MACHINE\Cluster\Resources” | Where-Object {([string](get-itemproperty -path “Registry::$_”).”Name”) -match “MSDTC-SQL Server ($InstanceName)”}

First, I get all the child items of the registry HKEY_LOCAL_MACHINE\Cluster\Resources. In each of the child items, I’m searching for a name that matches “MSDTC-SQL Server (<MyinstanceName>)”
Of course, you should change the “match” parameter when you have used another name for your MSDTC. Note: The parameter $InstanceName should be the name of your SQL Server Instance
As a result, I get the correct key!

Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC” -name “AllowOnlySecureRPCCalls” -value 0
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC” -name “TurnOffRpcSecurity” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “NetworkDTCAccess” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “NetworkDTCAccessClients” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “NetworkDTCAccessInbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “NetworkDTCAccessOutbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “NetworkDTCAccessTransactions” -value 1
Set-ItemProperty -path “Registry::$msdtcKey\MSDTCPRIVATE\MSDTC\Security” -name “XATransactions” -value 1

I have used that key to set the correct registry values, according to my customer needs.
More information can also be found on

http://msdn.microsoft.com/en-us/library/dd897479(v=bts.10).aspx

stop-ClusterResource “MSDTC-SQL Server ($InstanceName)”
start-ClusterResource “MSDTC-SQL Server ($InstanceName)”

Finally, you need to stop and start the cluster resource in order to take the changes into effect.

Note: The parameter $InstanceName should be the name of your SQL Server Instance

Have fun with it!

Confusion about Transparent Client Redirection with Database Mirroring

Posted by Pieter on December 15, 2011

Posted in: Database Mirroring, Transparent Client Redirection.

Yesterday I was preparing an info session for my customer on database mirroring.
I came to the part where I had to explain how the developers can use database mirroring in their application code, which is called Transparent Client Redirection. As there is a lot of confusion about the use of the “Failover Partner”, I want to clarify this in this blog.

Implicit mode

When you want to use this mode, actually, you don’t need to change anything in your connection string.
I thought, that you also need to specify the “Failover Partner” parameter, but that’s not the case.
Here is how it works. When you setup a connection to the SQL Server that is hosting a mirrored database, the SQL Server provider will cache the name of the mirror server into the memory. While your connection is up and your primary server should not be reachable anymore, the SQL Server provider will try to connect to the mirror server.

Explicit mode

With this mode, you need to specify the server name for the mirror in your connection string.
You should add the following to your connection string “;Failover Partner=<servername>” (where <servername> is the name of your mirror server).

.NET Example

Data Source=SQLA;Failover Partner=SQLB;Initial Catalog=AdventureWorks2008R2;Integrated Security=True

That failover partner option will be used for the initial connection towards your principal server. When you startup your application and it cannot connect to the principal server, the SQL Server provider will check the failover partner and will try to connect to that server. If you didn’t specify the failover partner option, your application will not start up.

Remark: Keep in mind that the failover partner will be overwritten by the SQL Server provider, with the name it received from the SQL Server for the mirror server.

Conclusion

Always add the failover partner option in your connection string if you want to fully use the advantage of database mirroring. If you don’t specify it, and your database is not available, transparent client redirection will not work and your application will not startup.
And you must admit, it’s not a very time consuming job to modify your connection string Knipogende emoticon

More information can be found on http://technet.microsoft.com/en-us/library/cc917713.aspx

How to configure Availability Groups in 10 steps

Posted by Pieter on December 8, 2011

Posted in: AlwaysOn, SQL 2012.

Last week I installed SQL Server 2012 RC0 which was released a few weeks ago.

I was playing around with the AlwaysOn high availability groups and noticed some small, but nice, improvements in the Availability Group Wizard.

It has become very easy now to configure an availability group in just 10 steps.

In this post you can find the steps I followed, I will also describe the enhancements in the wizard that were not in the CTP3 version.

Step 1: Startup the wizard

In Object Explorer, connect to the server instance that hosts the primary availability replica, and click the server name.
Expand the AlwaysOn High Availability node.
Right-click the Availability Groups node, and select the New Availability Group

Now here we have already the first difference compared to CTP3. In the old version you had to go to “Management” à Availability Groups. With RC0, Microsoft created a separated node called “AlwaysOn High Availability”

Step2: Specify Availability Group Name

Just specify the name for the availability group.

Step 3: Select Databases

Select the databases that you want to be part of the availability group.
If you notice that you can”t select one of your database, check the status column to find out the reason, for example your recovery model is set to simple or no full backup of the databases is available.
More information on Checklist: Requirements Availability Databases

Step 4: Specify replica

In this step, Microsoft changed a lot compared to CTP3.
For each replica, you can now choose if it should use Automatic Failover of not and if it should use Synchronous Commit mode or not.
In CTP3,you only had the option Replica Mode where you could choose the options Automatic Failover,High Performance and High Safety.

Automatic Failover à Synchronous Commit mode will be selected to because you cannot combine asynchronous commit mode with Automatic Failover
High Performance à Don”t select Automatic Failover and don”t select Synchronous Commit
High Safety à Select Synchronous Commit. Don”t select Automatic Failover. Only manual failover will be possible. If you should select Automatic Failover, you”re back to the first mode.

The readable Secondary column has the same functionality but Microsoft just put other “names”
In CTP3 you had Disallow Connection, Allow all connections and allow only read-intent connections

CTP3	RC0
Disallow Connection	No
Allow all connections	Yes
Allow only read-intent connections	Read-intent only

Step 5: Specify Endpoints

In this section you have to specify the endpoint that you want to use for your mirroring session.
This is actually the same like configuring endpoints for database mirroring.
Nothing changed much here compared to CTP3

Step 6: Backup Preferences

The backup preferences tab is a new tab that has been added in RC0.
It allows you to specify where your backups have to occur.
You can choose between:

Prefer Secondary
Secondary Only
Primary
Any Replica

The only “issue” is that the DBA has to implement the logic himself. You can use the sys.availability_groups table to find the backup preference. Also check the sys.availability_replicas to check the replica backup priority. By querying these views and tables, you can determine which replica should run a given backup job by running a simple script.

I”m currently working on that script, which will be posted later on.

More information can be found on Backup on Secondary Replicas (AlwaysOn Availability Groups)

Step 7: Listener

Your application should use the listener to connect to the databases. The listener is owned by the primary server, so for the application it”s transparent which server is the active on because it”s connecting to the listener.
The big improvement in this wizard of RC0 is that you can specify a static IP address. In CTP3, you only had the option to specify DHCP. If you wanted to use a static IP, you had to skip it in the wizard and configure the Listener afterwards.

Just specify the DNS name, port and network mode.

Step 8: Select Initial Data Synchronization

In CTP3 you only had the option Full (formally known as “Perform initial data synchronization”) and Skip initial data synchronization.

In this new version, Microsoft added an extra option Join only. If you want to use this option, the database must already be restored on the replica.
This option will then just join the selected database to the availability group.

Step 9: Validation

Now your complete setup will be verified. Everything should be successful before you can continue with the next step.

Step 10: Summary and Results

The wizard creates a summary for the user. The script button will generate a script with all the settings that you have selected.
Might be handy if you want to test different configuration and you don”t want to go through this wizard every time.
When you click finish, the availability group is created and finally you should have a successful result

SQL Server Management Studio Scripting Options

Posted by Pieter on October 20, 2011

Posted in: SQL Server Management Studio, Scripting.

This week I had to partition a table for performance improvements. I scripted the table to create a temporary table for my sliding window. When I wanted to change the script, I noticed that none of my indexes and my partition scheme was scripted… Strange… because I thought that indexes were always scripted together with the table. In the Options menu of the management studio I found the default scripting options. You can find them under Tools –> Options –> SQL Server Object Explorer

When I checked the default setting of the option “Script indexes”, it was indeed false. However, according to the books online, the default setting should be true.So I compared my SQL Management Studio scripting default values with the books online default values and I noticed a few differences, which can cause some confusions, like I had… Maybe it’s worth to mention that I used a standard installation and didn’t change any option

Below you can find a list of the SSMS settings and the books online default values

According to me, following settings should be changed after installation of the SSMS, otherwise you will get faulty scripts, which can have a serious impact on your database if you should reuse the scripts.

Script Data Compression Options
Script indexes
Script partition schemes
Script Triggers

HAVE FUN WITH IT!

Pieter Vanhove's Blog