How to change the ownership of SQL Server or Database Owner permission from one account to other.

Problem: How to change the ownership of SQL Server or Database Owner permission from one account to other.

Solution: A​UT​HO​RI​ZA​TI​ON

ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals.

Pre-requisites for changing the owner to a login, the login must be one of the below:

·         A SQL Server authentication login.

·         A Windows authentication login representing a Windows user (not a group).

·         A Windows user that authenticates through a Windows authentication login representing a Windows group.

·         A user who is executing this alter statement must be a sysadmin.

Example: In this example, I would like to show we can change the ownership of a database

T-SQL:

ALTER AUTHORIZATION ON DATABASE::Test TO sa;

-- to test the change of the ownership.

SELECT d.name, d.owner_sid, sl.name  

FROM sys.databases AS d 

JOIN sys.sql_logins AS sl 

ON d.owner_sid = sl.sid

where d.name='Test';

OUTPUT:

name	owner_sid	name
Test	0x01	sa

Read More

Transfer Schema Objects

Problem: How to transfer objects from an old schema to a new schema with in the database.

Solution: ALTER SCHEMA TRANSFER

This transfers the specific objects in between schemas.

Syntax:

ALTER SCHEMA schema_name  

   TRANSFER [ <entity_type> :: ] securable_name  

[;] 

<entity_type> ::= 

    { 

    Object | Type | XML Schema Collection 

    } 

Example: In this example, I would like to show we can move tables, views and stored procedures from an old schema to new schema.

Declare @NewSchema VARCHAR(256),

@OldSchema VARCHAR(256)

SET @NewSchema='NewSchema'

SET @OldSchema ='OldSchema'

SELECT 'ALTER SCHEMA '+@NewSchema+'  TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'

FROM sys.Objects DbObjects

INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id

WHERE SysSchemas.Name = @OldSchema

AND (DbObjects.Type IN ('U', 'P', 'V'))--U(table)|P(procedures)|V(views)

Read More

How to Configure Database Mirroring?

Problem: How to Configure Database Mirroring?

Solution: Setting Up Database Mirroring (SQL Server)

Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Prerequisites:

      1.     Make Sure the SQL Servers of principal, Mirror and witness are on the same version and additionally principal and mirror should be having the same edition of SQL Server.
2.     Make Sure the Principal database is in full recovery mode.
3.     Take the backup of the database you want to mirror and restore it to the mirroring server you want to use as target with NORECOVERY OPTION.

Step 1: Taking backups of the principal and restoring that on to the mirror as below.

Backup the database On Principal:

BACKUP DATABASE TestUser TO DISK='C:\Testuser.bak'

BACKUP DATABASE TestUser TO DISK='C:\Diff.bak'

BACKUP DATABASE TestUser TO DISK='C:\Transactional.trn'

Restore the database On Mirror:

RESTORE DATABASE TestUser FROM DISK='C:\Testuser.bak' WITH NORECOVERY

RESTORE DATABASE TestUser FROM DISK='C:\Diff' WITH NORECOVERY

RESTORE DATABASE TestUser FROM DISK='C:\Transactional.trn' WITH NORECOVERY

Step 2: Launch the Mirroring Wizard from the SQL Server Management studio and point to the highlighted option selected as below.

Step 3: Once the Mirror option is selected from the previous step, there should a window popped out from the click saying Database properties. In this step Configuring Security to connect through the Server sets (Principal, Mirror and Witness)

Step 4: Once you have selected security in the Configuration dialog box this will open a wizard configuration for the principal, mirror and witness.

Step 5: Include a witness to check the health of the mirroring synchronization, this will help when to switch-over in the high availability fail over.

Step 6: Choose a witness server to configure.

Step 7: Configure the port for creating an endpoint to Principal Server Instance.

Step 8: Configure the port for creating an endpoint to Mirror Server Instance.

Step 9: Configure the port for creating an endpoint to Witness Server Instance.

Step 10: Which services or domain accounts should be used in connecting endpoints through each server.

Step 11: Review the information from connections and configurations for all the server sets.

Step 12: Success: Final step for Configuration of End points.

 

Step 13: Start Mirroring to start the synchronization between servers. 

This step might be giving an error if you have not backed up the databases as specified in the step 1

.

Step 14: after the mirroring is started there should be status of the synchronizing/ed.

Step 15: Launch the mirroring monitor. To see the progress of principal and mirror.  

Step 16: viewing the principal server to see the database properties.

Step 17: viewing the Mirror server to see the database properties.

Read More

Optimize query plan memory used for Ad-hoc Queries

Problem: An application executes a large volume of dynamic queries. How to reduce the amount of memory used for cached query plans?

Solution: 

Optimize for Ad Hoc Workload, Starting from SQL Server 2008

A Query when gets executed against a database engine creates and execution plan which will be stored in the query plan cache. Plans are stored for using the same execution plan when the same query is executed again it saves time to compile.

 Due to this reason, large number of query plans are stored in system. However, there are plenty of plans which are only used once and have never re-used again. One time ran batch plans wastes memory and resources.

SQL Server 2008 has feature of optimizing ad-hoc workloads which will work best for the ad hoc queries and looks for the execution counts in the plan caches to see if the query is executed more than once or else it will not cache it. This will reduce the memory used to store the plans for un used queries or procedures.

Example:

In this example i would like to show we can enable the optimize ad hoc workload option from T-SQL and SSMS Gui.

EXEC sp_CONFIGURE 'show advanced options',  1

RECONFIGURE

GO

EXEC sp_CONFIGURE 'optimize for ad hoc workloads',     1

RECONFIGURE

GO

Select name,description,value_in_use,is_dynamic from sys.configurations where name ='optimize for ad hoc workloads'

OUTPUT:

name	description	value_in_use	is_dynamic
optimize for ad hoc workloads	When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.	1	1

Read More

Detach and Attach Database.

Problem: How to detach and attach a database, where will this be useful process?

Solution: Database Detach and Attach (SQL Server)

The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.

A Database cannot be detached from the SQL Server Instance

·         if the database currently replicated.

·         if the database is in the mirroring.

·         if a snapshot of database is created and still exists.

·         database cannot be a system database.

A database cannot be attached to a SQL Server Instance

·         If there is a database file restore happening

This detaching and attaching of data files and log files will be helpful in a process of upgrades or patching the SQL Server and reducing the down time for application.

Example: In this example, I would like to show we can detach a database and get the database back online with attaching it in a quick second.

--Detaching a test Database

exec sp_detach_db @dbname='Management' 

    , @keepfulltextindexfile='true'; 

--This will be offline once the database detached.

Select state_desc from sys.master_files where name='Management'

--Attaching back again.

CREATE DATABASE AttachedDB  

    ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\Management.mdf'),  

    (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\Management_Log.ldf')  

    FOR ATTACH; 

--This will be ONLINE once the database is attached back again to instance.          

Select state_desc from sys.master_files where name='Management'

OUTPUT:

AS we can see the Database properties from the options the files are still the same data and log files under a database name we choose to attach.

Read More