Wednesday, February 27, 2013

SQL Server 2012 Administration, Backup, Recovery and Tuning

You will learn how to configure SQLServer 2012 tools and service accounts, Database diagrams and Availability options.

SQL Server 2012 Configuration Manager

SQL Server 2012 Service Accounts

SQL Server 2012 Database Diagrams

Automating SQL Server 2012

SQL Server Agent Introduction

Configuring SQL Server Agent

Multiserver Administration in SQL Server

Availability Options in SQL Server

History of Microsoft SQL Server

Using MMC Snap-Ins in SQL Server

Backup and Restore of SQL Server Databases and Maintenance plans
The SQL Server backup and restore component provides an essential safeguard for protecting your business critical data in the databases. The below Videos will help to understand the backing up SQL Server databases, backup and restore strategies.

Understanding Recovery Models in SQL Server

Backup and Recovery Planning in SQL Server

Performing Backups and Restores in SQL Server

Using the Maintenance Plan Wizard in SQL Server

Using Maintenance Plan Design in SQL Server

How to Import database in SQL Server 2012

SQLServer Database Tuning and Server MonitoringSQL Server provides a comprehensive set of tools to monitor Database engine, events in the SQL Server and for tuning the physical database.

You will learn monitoring SQLServer and tuning.

What to Monitor in SQL Server

Database Engine Tuning Advisor in SQL Server

Monitoring Basics in SQL Server

Monitoring Events in SQL Server

Dedicated Administrator Connection in SQL Server

Using System Monitor in SQL Server

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Understanding Roles, Permissions, Profiler and Data Collection

SQL Server provides server-level roles to help you manage the permissions in Securing the SQLServer.

SQL Server Profiler is a nice interface to create and manage traces and analyze and replay trace results to diagnose a problem

See the below video tutorials to learn about Roles, Permissions, Profiler and Data Collection

Understanding Permissions in SQL Server

Understanding Roles in SQL Server

Fixed Server-Level Roles in SQL Server

Database Level Roles in SQL Server

Authentication Modes in SQL Server

Principals and Securables in SQL Server

Principal and Securable Hierarchy in SQL Server

Securing SQL Server

SQL Server Profiler part 1

SQL Server Profiler part 2

Pages and Extents in SQL Server

Understanding FillFactor in SQL Server

Linked Server vs Openrowset in SQL Server

Data Collector in SQL Server

SQL Server Standard Reports

SQL Server Best Practice Analyzer

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com

Understanding Indexes in SQL Server

An Index in a database lets you quickly find specific information in a table or indexed view and you can significantly improve the performance of applications and database queries by well-designed indexes to support your queries.

Visit below links to learn about different types of Indexes in SQLServer.

Understanding Indexes in SQL Server
What Should Be Indexed in SQL Server

Indexes A Closer Look in SQL Server

Clustered Indexes in SQL Server

Non-Clustered Indexes in SQL Server

New Index Features in in SQL Server 2012

Understanding SQL Server Database Objects and Data Types

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

Understand SQL Server Database Objects and Data Types

You will learn about SQL Server Database Objects and Types. Many users benefited from these videos/links and I hope it will be helpful to you.

Understanding Database & Table Normalization

Understanding SQL Server Data Types part 1

Understanding SQL Server Data Types part 2

Creating a Database

Understanding SQL Server Databases

Understanding SQL Server Tables

Understanding Primary Keys in SQL Table

Table Change Gotcha in SQL Server

Understanding SQL Server Schemas

Understanding SQL Server Synonyms

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Monday, February 25, 2013

Backup SQLServer Database and different types of Backup

You should always have proper backup plan in place to protect your database from failures. SQLServer have four types of backups

1. Full Database backup – Complete database backup at the time of backup.
2. Transaction Log backup – Backups up the transaction log file
3. Differential backup–  Backs up the parts of the database that is changed since the last Full Database backup
4. File and file group backup – Backs up the database files that you specify in the FILE or FILEGROUP option.
In SQLServer either you can mention the backup path or you can create a backup device use the device name during the backup. You can either use T-SQL or SQLServer Management Studio to backup SQLServer Database.

Full Database backup: You can backup whole database, this includes part of transaction log which is needed to recover the database using full backup.

BACKUP DATABASE DB_Name
    TO Backup_Device
    WITH FORMAT;
GO

or
BACKUP DATABASE DB_Name
   TO DISK = ‘D:\SQLBackup\FullBackup\DB_Name.bak’
GO

Transaction Log backup:  You must backup the transaction log, if SQLServer database uses either FULL or BULK-LOGGED recovery model otherwise transaction log is going to full. Backing up the transaction log truncates the log and user should be able to restore the database to a specific point in time.

BACKUP LOG DB_Name
   TO Backup_Device
GO

or

BACKUP LOG DB_Name
   TO DISK = ‘D:\SQLBackup\LogBackup\DB_Name.trn’
GO

If transaction log is full users will receive error “Log files are running out of space”

Differential backup:- The database must have full back up in order to take a differential backup, it only backups the changes since last full backup.

BACKUP DATABASE DB_Name
  TO Backup_Device WITH DIFFERENTIAL
GO

or

BACKUP DATABASE DB_Name
  TO DISK = ‘D:\SQLBackup\Differ\DB_Name.bak’ WITH DIFFERENTIAL
GO

Full/Differential/Transaction Log  Backup using SQL Server Management Studio
• Right click on the database name
• Select Tasks > Backup
• Select backup type either "Full" or "Differential" or” Transaction Logl”
• Select the appropriate Backup Destination and click "OK"

File and file group backup:- By default each database has PRIMARY file group which is tied to one data file. You can create additional filegroups and add data files to filegroup. You can perform both FILE and FILEGROUP backups.

BACKUP DATABASE DB_Name
FILEGROUP = ‘Filegroup_Name’ TO Backup_Device
GO

or
BACKUP DATABASE DB_Name
FILEGROUP = ‘Filegroup_Name’ TO DISK = ‘D:\SQLBackup\Filegroup\DB_Name.bak’
GO

File or File Groups Backup using SQL Server Management Studio• Right click on the database name
• Select Tasks > Backup
• Select backup type either "Full" or "Differential"
• Select Backup component as "Files and filegroups"
• Select the appropriate filegroup
• Select the appropriate Backup Destination and click "OK"

Note that from SQLServer 2008 Enterprise edition and later supports backup compression.
Restrictions on Backup Operation1. You cannot backup  offline Database
2. You cannot run two backups concurrently

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com


Free ASP.NET AJAX video Tutorials

You will learn the ways to work with ASP.NET AJAX to create highly response pages, enven if you are not a JavaScript Expert.

I hope these tutorials are helpful to you and you will benefit from it.

1. Get Started with ASP.NET AJAX

2. Implement Dynamic Partial-Page Updates with ASP.NET AJAX

3. Make Client-Side Network Callbacks with ASP.NET AJAX

4. Add ASP.NET AJAX Features to an Existing Web Application

5. ASP.NET AJAX Enable an Existing Web Service

6. ASP.NET AJAX Client Library Controls

7. Use an ASP.NET AJAX ScriptManagerProxy

8. ASP.NET AJAX RoundedCorners Extender

9. ASP.NET AJAX Timer Control

10. Implement the Predictive Fetch Pattern for AJAX

11. Implement the AJAX Paging Pattern

12. Implement the AJAX Incremental Page Display Pattern

13. Implement the Incremental Page Display Pattern using HTTP GET and POST

14. ASP.NET AJAX UpdateProgress Control

15. ASP.NET AJAX History Control

16. Implement the AJAX After Processing Pattern

17. Update Multiple Regions of a Page with ASP.NET AJAX

18. Choose Between Methods of AJAX Page Updates

19. Use Other JavaScript User Interface Libraries with ASP.NET AJAX

20. Use the ASP.NET AJAX Profile Services

21. Debug ASP.NET AJAX Applications Using Visual Studio 2005

22. Build a Custom ASP.NET AJAX Server Control

23. Use JavaScript to Refresh an ASP.NET AJAX UpdatePanel

24. Determine Whether an Asynchronous Postback has Occurred

25. Use the Conditional UpdateMode of the UpdatePanel

26. Implement the Persistent Communications Pattern with the UpdatePanel

27. Localize an ASP.NET AJAX Application

28. Implement the Persistent Communications Pattern using Web Services

29. Trigger an UpdatePanel Refresh from a DropDownList Control

30. Create an ASP.NET AJAX Extender from Scratch

31. Build Custom Server Controls that Work With or Without ASP.NET AJAX

32. Associate AJAX Client Behavior with an ASP.NET Server Control

33. Retrieve Values From Server Side AJAX Controls

34. Two Simple Techniques for Triggering Updates to Update Panels

35. Use ASP.NET AJAX Cascading Drop Down Control to Access a Database

36. Implement Infinite Data Patterns in AJAX

37. Basic ASP.NET Authentication in an AJAX Enabled Application

38. Dynamically Change CSS ASP.NET AJAX UpdatePanel

39. Dynamically Add Controls to a Web Page

40. Set Up Your Development Environment for ASP.NET 3.5

41. Set Up Your Development Environment for ASP.NET 2.0

42. Customize Error Handling for the ASP.NET AJAX UpdatePanel

43. Use ASP.NET AJAX Client Templates

Regards,
Satishbabu Gunukula
htttp://www.sqlserver-expert.com

Thursday, February 21, 2013

SQL Server 2012 Editions, License, Certification, Hardware and Software requirements

There are many new features introduced in SQLServer 2012. For example, there is a new licensing model, new edition introduced.

Visit below Link/Videos to learn about various Editions, License, Certification, Hardware and Software requirements for SQLServer 2012.

1. Whats New in SQLServer 2012

2. SQL Server 2012 Thinks to Know

3. SQL Server 2012 Various Editions

4. Licensing for Microsoft SQL Server 2012

5. Microsoft SQL Server Certifications

6. SQL Server 2012 Hardware Requirements

7. SQL Server 2012 Software Requirements

8. SQL Server 2012 Installation Options part 1

9. SQL Server 2012 Installation Options part 2

10. SQLServer 2012 Enhancements

Please leave your valuable comments.

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Microsoft SQL Server 2012 Enhancements Video Tutorials


You will learn about the key enhancements and features in SQLServer 2012.
Many users benefited from these videos lessons and I hope you will be benefited.    
                                                                                                   
Below Videos are from Microsoft TechNet Video’s

Regards,
Satishbabu Gunukula

Free SQLServer Vedio Training for Beginners


You will learn how to work with SQLServer Express to add database capabilities to your ASP.net Applications


1.       What is a Database













Regards
Satishbabu Gunukula
 

Wednesday, February 20, 2013

User Group or Role already exists in the current database

When trying to create user with login or edit user mapping…etc, you may receive below error message.
Error 15023: User, Group or Role already exists in the current database
You may see below error when you are dealing with group

Error 15024: The group already exists in the current database
Users will receive this message when user existed without a SQLServer login in the database.

Run below command to see current user tologin mapping

Use[DatabaseName] ;
GO
exec sp_change_users_login @Action='Report' ;
GO

If the user does NOT exist, then you can run below command using “auto_fix” attribute to create the user in the database.

USE DB_NAME
GO
EXEC sp_change_users_login 'auto_fix', 'USER_NAME'
GO
You will see the output with following result
The row for user will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

If the user is ALREADY exists and if you want to map login with the user run below command using “updated_one” attribute.


USE DB_NAME
GO
EXEC sp_change_users_login 'update_one', 'USER_NAME', 'LOGIN_NAME’
GO
For Ex:-
USE TestDB; 
GO
EXEC sp_change_users_login 'Update_One', 'TestUser', 'TestUser'; 
GO 

You can also use below Stored Procedure to fix all the Orphan users in database by mapping them to username which is already exist on server. Make sure you test the script in a testing environment first.

CREATE PROCEDURE dbo.spDBA_FixOrphanUsers
AS
DECLARE @username VARCHAR(25)
DECLARE GetOrphanUsers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1
AND (sid IS NOT NULL AND sid <> 0x0)
AND SUSER_SNAME(sid) IS NULL ORDER BY name
OPEN GetOrphanUsers
FETCH NEXT
FROM GetOrphanUsers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
EXEC sp_changedbowner 'sa'
ELSE
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT
FROM GetOrphanUsers
INTO @username
END
CLOSE GetOrphanUsers
DEALLOCATE GetOrphanUsers
GO
You can refer Microsoft Link for more information
http://msdn.microsoft.com/en-us/library/ms175475.aspx

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com