Free Microsoft eBooks for you!

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 01-08-2012

3

Its really amazing to find something for free, isnt it? and if its going to make work life easy and more productive then make more sense! Here goes a post from Eric about Large collection of Free Microsoft eBooks for you, including: SharePoint, Visual Studio, Windows Phone, Windows 8, Office 365, Office 2010, SQL Server 2012, Azure, and more.

Happy Reading..

go to: Link 

Microsoft SQL Server 2008 R2 Service Pack 2

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 28-07-2012

1

Microsoft on july 26th announced that SQL server 2008 R2 service pack is now available to download. If you apply this service will upgrade SQL build version to 10.50.4000.0; Also visit this link to find what all bugs have been fixed in this service pack.

 

 

Opportunity to write about Microsoft SQL Server

Posted by Deepak Kumar | Posted in SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 21-01-2011

0

Do you have something to write about SQL Server that can help SQL community by your learning, innovation. R&d, research etc. This is an unique opportunity to reach a large group of SQL Server users, gain experience as SQL writer, publicity and much more.

image

Drop an email with your content/summary to dk@sqlknowledge.com I will review and publish your content on SQLKnowledge.com by your name and provide you instant website access to review, modify & answer user comments on website.

Your comments & suggestions are welcome; Feel free to reply if you have any questions

Monitoring & Setting Alerts for SQL Server Critical Errors

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 20-01-2011

10

As a Proactive DBA, you must monitor All SQL Server errors having severity level between 17 to 25. Any errors from level 20 to 25 are serious in nature however for 17 to 19 a DBA involvement is required for resolution. Here is self explanatory MS description of error levels, steps to get automatic alert as soon as any of error occurs and list of error messages.

What you ‘Currently’ don’t get with SQL Azure?

Posted by Deepak Kumar | Posted in SQL DBA, Uncategorized | Posted on 21-12-2010

1

image
SQL Azure is cloud based relational database platform built on SQL Server technology; it allows storing structured, semi-structured and unstructured data over the cloud. Behind the scene databases are hosted on cluster SQL servers, and are load balanced plus replicated for scalability & high availability purposes. Each logical Server consists of one master and N number of user databases based on Wed or Business Edition.

SQL Azure Standard Consumption Pricing (check offers available with MSDN subscription and Microsoft Partner Network)
Web Edition Database
•    $9.99 per database up to 1GB per month
•    $49.99 per database up to 5GB per month

Business Edition Database
•    $99.99 per database up to 10GB per month
•    $199.98 per database up to 20GB per month
•    $299.97 per database up to 30GB per month
•    $399.96 per database up to 40GB per month
•    $499.95 per database up to 50GB per month

How to connect to SQL Azure database:  You may use SQL Server management studio or

image

Alternatively you can choose SQLCMD to connect to SQL Azure database:
sqlcmd -U <ProvideLogin@Server> -P <ProvidePassword> -S <ProvideServerName> -d master

How to get started?
Once connected to SQL Azure instance. If you pass a very basic database creation command “CREATE DATABASE SQLKnoweldge”, it will create default web edition database of maxsize 1GB. You can specify maxsize as like:

CREATE DATABASE SQLKnoweldge (EDITION=’WEB’, MAXSIZE=5GB)

Following command will create a database with default Maxsize of 10GB that you can increase up to 50GB.

CREATE DATABASE SQLKnoweldge (EDITION=’BUSINESS’)

What if you do not specify edition? It will check database size and bases value it will determine edition. Say, if MAXSIZE is set to 10, 20, 30, 40 or 50GB then its business edition. Here is database creation syntax for SQL Azure.

CREATE DATABASE database_name 
{
   (<edition_options> [, ...n]) 
}
<edition_options> ::= 
{
   (MAXSIZE = {1 | 5 | 10 | 20 | 30 | 40 | 50} GB) 
    |(EDITION = {'web' | 'business'})
}[;]
To copy a database:
CREATE DATABASE destination_database_name
     AS COPY OF [source_server_name.] source_database_name [;] 

What you ‘Currently’ don’t get with SQL Azure?
In addition to SQL Profiler, Perfmon, Trace Flags, Global Temporary Tables, Distributed Transactions, SQL Azure does not support heap tables, a table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table. That is the reason ‘select into’ statement fails. Read committed Snapshot Isolation (RCSI) is default instead Read Committed in SQL server; Connection timeout is 30 minutes plus long running transactions can be terminated without your knowledge. Here is the MS list for more details:
SQL Server 2008 R2 Features Not Supported by SQL Azure: The following features that were new to SQL Server 2008 R2 are not supported by SQL Azure:
•    SQL Server Utility
•    SQL Server PowerShell Provider. PowerShell scripts can be run on an on-premise computer, however, and connect to SQL Azure using supported objects (such as System Management Objects or Data-tier Applications Framework).
•    Master Data Services
SQL Server 2008 Features Not Supported by SQL Azure: The following features that were new to SQL Server 2008 are not supported by SQL Azure:
•    Change Data Capture
•    Data Auditing
•    Data Compression
•    Extended Events
•    Extension of spatial types and methods through Common Language Runtime (CLR)
•    External Key Management / Extensible Key Management
•    FILESTREAM Data
•    Integrated Full-Text Search
•    Large User-Defined Aggregates (UDAs)
•    Large User-Defined Types (UDTs)
•    Performance Data Collection (Data Collector)
•    Policy-Based Management
•    Resource Governor
•    Sparse Columns
•    SQL Server Replication
•    Transparent Data Encryption
SQL Server 2005 Features Not Supported by SQL Azure: The following features that were new to SQL Server 2005 are not supported by SQL Azure:
•    Common Language Runtime (CLR) and CLR User-Defined Types
•    Database Mirroring
•    Service Broker
•    Table Partitioning
•    Typed XML and XML indexing is not supported. The XML data type is supported by SQL Azure.
Other SQL Server Features Not Supported by SQL Azure: The following features from earlier versions of SQL Server are not supported by SQL Azure:
•    Backup and Restore
•    Replication
•    Extended Stored Procedures
•    SQL Server Agent/Jobs

Script to find used and free space in Database files

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 02-08-2010

2

In an Enterprise world, you may be responsible to monitor 1000+ databases hosted on hundreds of SQL Server instances. On a lazy afternoon, suddenly low disk space alarm hit your inbox. What will you do, storage can not be added or expended on the fly. Here is the tested script to find used and free space from SQL Server database’s data files that you can shrink to easy the current situation.

-- Find Log file information and save in temp file using dbcc sqlperf logspace
CREATE TABLE #LogSpace (DatabaseName varchar(40), LogSize int, LogSpace int, status bit)
INSERT #LogSpace exec ('dbcc sqlperf(logspace) WITH NO_INFOMSGS') 
 
-- Find data file information using cursor and dbcc showfilestats
Declare @DatabaseName varchar(500)
create table #DBSpace(FielId tinyint, Filegroup tinyint, TotalSpace int, 
Used_Space int, Name1 varchar(25),NameofFile Varchar(900) )
Declare curDB cursor for select name from master..sysdatabases 
open curDB
fetch curDB into @DatabaseName
while @@fetch_status = 0
begin
    if databasepropertyex(@DatabaseName,'Status') = 'ONLINE'
    begin
    insert into #DBSpace exec ('USE [' + @DatabaseName + ']  DBCC SHOWFILESTATS WITH NO_INFOMSGS')
    end
    fetch curDB into @DatabaseName
end
close curDB
deallocate curDB 
 
-- Select data in tabular format with proper headings & order by
select left(sd.name,30) AS 'DatabaseName',
(ff.[DataFileSpace(MB)])+ls.LogSize as 'DatabaseSize(MB)',ff.[DataFileSpace(MB)],
ff.[DataFileUsedSpace(MB)], ff.[DataFileFreeSpace(MB)],
ls.LogSize as 'LogFileSize(MB)', ls.LogSpace as'LogFileSpaceUsedIn(%)'
from #DBSpace dbs join master..sysdatabases sd on sd.filename=dbs.NameofFile
join #LogSpace ls on sd.name=ls.DatabaseName
join
(select  sf.dbid, sum(dbss.TotalSpace/16) as 'DataFileSpace(MB)', 
sum(dbss.Used_Space/16) as 'DataFileUsedSpace(MB)', 
(sum(dbss.TotalSpace/16)- sum(dbss.Used_Space/16)) 
as 'DataFileFreeSpace(MB)' from #DBSpace dbss
join master.sys.sysaltfiles sf on rtrim(sf.filename)= rtrim(dbss.NameofFile)
group by sf.dbid) ff on ff.dbid=sd.dbid
order by 'DataFileFreeSpace(MB)' desc 
 
-- Drop temporary tables manually
Drop table #DBSpace
DROP TABLE #LogSpace 

Database Logshipping issues & Considerations!

Posted by Deepak Kumar | Posted in SQL DBA, Uncategorized | Posted on 04-06-2010

3

While working on SQL Server logshipping a DBA must consider following points to have log shipping setup running smoothly over a long period of time. Logshipping architect will look like:

image

  • Transaction log Backup and restoration jobs should work constantly (in parallel) and if backup/restoration jobs are not in sync due to network/server or any other issue it will crash logshipping setup and you may need to setup again or apply full backup restore once again. A DBA must add good number of alerts to notify as soon as transaction log backup, file copy or restoration job fails and act immediately to resolve it.
  • Its highly recommended that you should use almost similar set (size/drive name) etc on both servers. if your database log file increases to 100GB on primary server that means your secondary server should have 100GB capacity also. Similarly if you add new log/data files on primary database it should have similar location on secondary server, or you may need to manually restore TL backup on secondary server with move command.
  • Primary database can create huge transaction log backups during alter index/dbreindex tasks that you may find hard to move to secondary server(s); to mitigate the delay and risk you may choose to buy 3rd party products like Red-Gate/ Idera etc with logshipping features.
  • Use a customized script to transfer SQL Server logins to secondary server(s).
  • Logshipping failover is not fully automatic, manual DBA intervention is expected to recover databases during failure.
  • For SQL 2000 logshipping is good, however if you are using SQL2005 or 2008 use database mirroring which is advance to logshipping with hot standby feature of SQL Server.
© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com