SQL 2012 AlwaysOn + IP for Azure in Orchestrator 2012 SP1

Just a quick heads up for something interesting in this blog post.

1.       Learn how to configure SQL 2012 AlwaysOn for your VMM 2012 SP1 database

http://blogs.technet.com/b/scvmm/archive/2012/10/24/how-to-configure-sql-2012-alwayson-availability-groups-in-system-center-2012-virtual-machine-manager-service-pack-1.aspx

2.       Download and test the Integration Pack for Orchestrator 2012 SP1 that let you manage your virtual machines, services etc in Windows Azure.

http://www.microsoft.com/en-us/download/details.aspx?id=35399

Overview

​The Integration Pack for Windows Azure enables you to automate Windows Azure operations related to certificates, deployments, hosted services, storage, and virtual machines.

Feature Summary
The Integration Pack includes the following activities:

• Azure Certificates- the Azure Certificates activity is used in a runbook to add, delete, and list management and service certificates
• Azure Deployments- the Azure Deployments activity is used in a runbook to create, delete, get, and swap deployments, change deployment configurations, update deployment statuses, rollback an update or upgrade, get and change deployment operating systems, upgrade deployments, walk upgrade domains, and reboot and reimage role instances
• Azure Cloud Services- the Azure Cloud Services activity is used in a runbook to create, delete, and get cloud services, check cloud service name availability, and create affinity groups
• Azure Storage- The Azure Storage activity is used in a runbook to create, delete, update, and list storage accounts, get storage account properties, get and regenerate storage account keys, create, list, and delete containers, and put, copy, delete, list, snapshot, and download blobs
• Azure Virtual Machine Disks- the Azure Virtual Machine Disks activity is used in a runbook to add, delete, update, and list virtual machine disks and virtual machine data disks
• Azure Virtual Machine Images- the Azure Virtual Machine Images activity is used in a runbook to add, delete, update, and list virtual machine operating system images
• Azure Virtual Machines- the Azure Virtual Machines activity is used in a runbook to create virtual machine deployments, download virtual machine remote desktop files, as well as get, delete, start, restart, shutdown, capture, and update virtual machine roles

 

Change DB options (SQL)

If you want to change DB options on several databases, you could either click yourself to death, or simply use the following T-SQL to modify all user databasases at once:

----------------------

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))

   EXECUTE (''ALTER DATABASE [?] SET RECOVERY SIMPLE  WITH NO_WAIT'') '

---------------------

In this example, I`ve used "SET RECOVERY SIMPLE WITH NO_WAIT", but you can use any other.

Cannot generate SSPI context

It`s quite common (here in Norway) that some local hero have installed the Windows Server when the organization/company is really small (1-5 employees).

The thing I want to address is that when they do this, they normally install the Active Directory Domain Services role – along with DNS server.

What about the DHCP role?

Nope. They already got some internet connection delivered by their ISP, so the clients are already connected to the internet.

Brilliant, right?

For all the other natural causes that I could use as arguments against this setup, I`d rather want to mention a common error message that appear when they run their LOB applications that uses Microsoft SQL server for their databases.

“Cannot generate SSPI context”

This error message occurs on clients attempting to connect to a SQL Server on the network.

And this message is purely related to DNS.

When you have an Active Directory domain and the clients are using the “wrong” DNS, which in these cases is the router/firewall (default gateway) or an external DNS, they cannot use name lookups to verify the server name.

In short, the DNS server from their ISP have very little knowledge of the server who is responsible for their databases on their local area network.
Conclusion: If you`re running an Active Directory domain, whether you have one employee or 15, please use your internal DNS server so that name lookups and other AD-related stuff may occur.  

Connect to your SQL server

A simple script that enables connection to your default SQL server on port 1433. Works on XP, Vista, Win 7, 2003, 2008, and 2008 R2

Copy the content to notepad and save the file as .bat

---------------------------------------------------------------------------------------------------------

@rem Easy Firewall-script for enabling connetion to default SQL

@echo = SQL Server Ports =
@echo SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"

@echo Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"

@echo SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 "SQL Service Broker"

@echo Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"

@echo = Analysis Services Ports =
@echo SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"

@echo SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"

@echo = HTTP, SSL, Browser, and Multicast =
@echo HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"

@echo SSL port 443
netsh firewall set portopening TCP 443 "SSL"

@echo SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"

@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE


---------------------------------------------------------------------------------------------------------

DBCC (Database repairs)

DBCC CHECKDB can perform repairs of corruption that it finds in the database. But what could you do if you don’t have a backup, and the DBCC returns errors that include:

-          Corruption in a PFS page header

-          Corruption in the leaf level of a critical system catalog index

-          Errors from system catalog cross-checks

-          Data purity errors

You might want to get to know the REPAIR_ALLOW_DATA_LOSS option.

It does what it says. It deletes whatever that is corrupt and fix up all linkages to and from the corrupt object. This is the fastest and easiest way to remove a corruption. But remember, even REPAIR_ALLOW_DATA_LOSS can`t fix every corruption.

You need to set the database to single_user before you run this command.

The syntax:

DBCC CHECKDB (‘DB_NAME’, ‘REPAIR_ALLOW_DATA_LOSS’)

Impersonation (SQL)

You may be familiar with the ‘Run as..’ option in Windows.

In SQL, you can impersonate another principal to execute commands in a specific user context.

You must have the IMPERSONATE permission grated to your account on the principal that you want to impersonate. You can also assign the IMPERSONATE permission during login, so you can execute under that principal`s authority in any database to which the principal has access.

EXEC | EXECUTE AS <context_spcification>

Upgrade your databases to SQL 2008 R2 (script)

I think I have upgraded SQL servers and databases more often than I have eaten hot dinners.

So I have created a SQL script that will do the following:

1.       Set Auto_close to off (makes your DBs faster)

2.       Set Auto_shrink to off (the Auto_shrink feature gives your SQL server a performance overhead, and the DBs will grow to the same size anyhow afterwards. Use db_shrink for shrinking your DBs)

3.       Set Parameterization to forced (optimizing queries)

4.       Set Auto_create_statistics to on (helps your SQL server to query more intelligent)

5.       Set Page_verify to checksum (gives you the best security for dirty pages and corruption)

6.       Set Compatibility_level to 100 (this is the compatibility level for SQL 2008 (80 is MSDE, 90 is SQL 2005, so when you upgrade to SQL 2008/R2, also upgrade the compatibility level))

In general, you can use this stored procedure for setting dboptions on your databases:

----------------------

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))

   EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'') '

---------------------

In my script, I will not touch the recovery model for the databases, since this is not me to decide, but the the organizations backup policy.

I also have to mention that you should set the options properly on the ‘Model’ database as well, since the databases you create will inherit the settings from here.

The script will run in SSMS 2008 (just copy the content to notepad, and save the as sqlscript.sql

--------------------------------------------------------------------------------------------------------------------------------------

--SQL script from Virtualization and some coffee

--Auto_close, auto_shrink = off, Parameterization = forced, Auto_create_statistics = on, Page_verify = Checksum, Auto_update_statistics = on

--Sets compability level to 100 - SQL 2008

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))

  

EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')

'

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''MASTER'', ''TEMPDB'', ''MSDB'', ''MODEL''))

EXECUTE (''ALTER DATABASE [?] SET AUTO_SHRINK OFF WITH NO_WAIT'')

'

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''MASTER'', ''TEMPDB'', ''MSDB'', ''MODEL''))

EXECUTE (''ALTER DATABASE [?] SET PARAMETERIZATION FORCED WITH NO_WAIT'')

'

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''MASTER'', ''TEMPDB'', ''MSDB'', ''MODEL''))

EXECUTE (''ALTER DATABASE [?] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT'')

'

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''MASTER'', ''TEMPDB'', ''MSDB'', ''MODEL''))

EXECUTE (''ALTER DATABASE [?] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT'')

'

EXECUTE sp_MSforeachdb

'

IF (''?'' NOT IN (''MASTER'', ''TEMPDB'', ''MSDB'', ''MODEL''))

EXECUTE (''ALTER DATABASE [?] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT'')

'

DECLARE GET_DATABASES CURSOR 

READ_ONLY  

FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != '100' 

 

DECLARE @DATABASENAME NVARCHAR(255)  

DECLARE @COUNTER INT 

 

SET @COUNTER = 1  

 

OPEN GET_DATABASES  

FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME  

WHILE (@@fetch_status <> -1)  

BEGIN 

IF (@@fetch_status <> -2)  

BEGIN 

-- Endrer db-compatibility  

EXECUTE sp_dbcmptlevel @DATABASENAME , '100' 

 

PRINT  @DATABASENAME + ' changed' 

 

SET @COUNTER = @COUNTER + 1  

END 

 

FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME  

END 

 

CLOSE GET_DATABASES  

DEALLOCATE GET_DATABASES  

 

GO 

How to shrink your SCVMM database (Transactional Log)

(Generally speaking, this is not a ‘SCVMM-problem’. It`s linked to SQL, and the way the SQL server act when it comes to I/O, Memory, Data, - and log files. But since this is a virtualization-blog, I want to angle this toward the SCVMM server since it`s also relevant here)

Ok, you have had your SCVMM server running for a while now, all roles and services installed on a single server. Remember that the SCVMM 2008 R2 server installs a SQL 2005 Express if you don’t specify another instance for this purpose.

The storage limit for a database in SQL 2005 Express is 4GB, so what do you do when the VirtualManagerDB_log starts to grow out of control ?

First, we have to locate our databases.

SCVMMàAdministrationàGeneralàDatabase Connection

Here we`ll find the information we need

We`ll see the Database Server Name, Instance, and the name of our database.

We should also locate the file location for the databases.

Startàcmd

SQLCMD –S .\Microsoft$VMM$  (Log on to your SQL instance with trusted connection)

1.       Select * from sysdatabases where name = ‘VirtualManagerDB’

2.       Go

Now we`ve located the database, and we can see that the log file is growing (the reason for this, is that the VirtualManagerDB has the ‘Recovery Model’ set to full, which allows the log file to grow since you are able to run log-backups, and can restore the database to any point in time).

We can actually shrink this log file in two ways.

1)      In a heavy way (Fullbackup->log backup->shrink log file (log backup and shrink log file x2)

2)      In a awesome way (sp_detach_db, rename log file, sp_attach_single_file_db)

I`ll go for number 2.

(Be careful if you are planning to do this in production, and off course have a solid backup and make a copy of the log file before editing).

This procedure will cause the SCVMM server to be offline, since we are detaching the database from our SQL server.

Log on to your SQL server instance with SQLCMD –S .\Microsoft$VMM$

1.       Sp_detach_db virtualmanagerdb

2.       Go

3.       Rename the VirtualManagerDB_log to OLDVirtualManagerDB_log

4.       Sp_attach_single_file_db virtualmanagerdb, ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\virtualmanagerdb.mdf’

5.       Go

6.       In the data folder for your SQL server, you will now have a newly created log file for VirtualManagerDB at the size of 512KB.

7.       Alter database VirtualManagerDB set recovery simple with no_wait

8.       Go

The database is now in simple recovery, meaning that the log file will not grow out of control again but on a regularly basis create a checkpoint to the data file. (The way SQL uses its log files does not changes, whether you run full, bulk, or simple).

Strømbrudd -> diskhavari -> SQL = merde...

I mine 5 år i bransjen, har jeg opplevd til tider daglig, korrupte SQL-databaser.

Hvorfor skjer dette ?

SQL er en server som genererer mye I/O, og kanskje mest av alt kjent for å konsumere det som er av RAM på maskinen, dersom det ikke er satt opp noe tak for dette.

( Sette MAX RAM til 1GB:

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE



GO


EXEC sys.sp_configure N'max server memory (MB)', N'1024'


GO


RECONFIGURE WITH OVERRIDE


GO


EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE


GO


)
 
SQL sin måte å forhindre datakorrupsjon på, er 'Page Verify' innstillingen på hver enkelt database. Denne anbefales å sette til Checksum. Dette vil gi deg best sikkerhet, men samtidig ta ørlite mer av maskinens ressurser.
 
Dersom uhellet alikevel er ute, og det har oppstått datakorrupsjon, vil du etter all sansynlighet finne informasjon om dette i errorloggen til SQL-server.
Det kan f.eks gi melding om at databasen ikke kan åpnes siden er er merket 'SUSPECT'. I de verste tilfellene så merker ikke brukeren noe av korrupsjonen, da databasene er online, men samtidig skadet. Mistanken her kommer først når dataene ikke samsvarer med det brukeren faktisk hadde forventet. F.eks negativt resultat i regnskapsrapporter :-)
 
Dette vil man kunne avklare om man kjører en sjekk på databasen:
 
dbcc checkdb ('userdatabase')
 
Denne kommandoen sjekker databasen, og fikser eventuelt moderate feil. Skulle den gi melding om feil som krever ytterligere behandling, bør du krysse det du har om at du har fersk backup.
 
Desverre er ikke dette alltid tilfelle, og man må derfor sette på takstameteret og begynne feilrettingen.
 
Hvordan redde data fra korrupt databaase:
 
Dersom basen er online, kan man gjøre følgende:
 
1. Sette databasen i single_user mode. ( alter database userdatabase set single_user )
2. dbcc checkdb ('userdatabase', 'repair_allow_data_loss') <-- Denne gjør faktisk det du tror
3. dbcc checkdb ('userdatabase') . Kjør denne for å se at den ikke rapporterer flere feil.
4. Sette databasen i multi_user mode. ( alter database userdatabase set multi_user )
5. Databasen er klar til drift.
 
Dersom databasen er merket 'SUSPECT' og man ikke får tilgang:
 
1. Sette databasen i 'nødsituasjon'. alter database userdatabase set emergency
2. Databasen er nå i 'emergency', tilsvarende read-only.
3. BCP...  Bulk Copy Program. Brukes til å eksportere data fra tabeller, og dumpes enten til disk, eller til en ny SQL-database.

Husk: Alltid ha fersk backup. I de tilfellene det ikke er fullversjon av SQL installert, så vil det heller ikke være anledning til å kjøre SSIS-pakker (backupjobber, vedlikehold m.m).
Man kan da enkelt sette opp et .bat-script som gjør denne jobben.

Dette kan kjøres på kveld/nattestid,og kopiere data til filserver, eller annet område.

Net stop mssqlserver /Y
xcopy "c:\program files\microsoft sql server\mssql\data\*.*" P:\backup /C /Y
Net start mssqlserver