Copy only Backups Differential Backups

Copy only backup feature was introduced in SQL Server 2005 to support ad-hoc backups. However, this feature is valid for Full backups and Log backups to not to disturb backup chains. Since ad-hoc Differential Backups do not disturb the backup chains, you don’t need Copy only backups for Differential Backups.

However, in SQL Server 2008, still you have the option of taking a Copy only differential backups as shown in below image.

In SQL Server 2012, this is changed as you are not allows to take ad-hoc differential backups as that option is grayed out.

SSSLUG January 2013

Slide decks, sample code and photos from the January 2013 #SSSLUG meet-up are now ready for downloading and viewing.
Downloads: http://www.sqlserveruniverse.com/SSSLUG/Downloads/2013.aspx
Gallery: http://www.sqlserveruniverse.com/SSSLUG/Gallery/January2013.aspx

What are the key qualities for a SQL Server DBA?

• Productive Under Pressure
• Problem Solver
• Understand Both the Business Needs and Technology Landscape
• Willingness to Learn and Open Minded
• Experience to Know When to be Firm and When to be Flexible
• Trust, Honesty and Integrity
• Communication

Read all in detail

Hadoop + SQL Server + Excel = Big Data Analytics

Customers will need a modern data platform to evolve with the needs of the business and the data they are collecting. Big data has created a massive business opportunity for businesses around the world to find new, actionable insights from all the data they collect, whether structured or unstructured. Because at the end of the day, the biggest promise of Big Data is to drive smarter decisions from data. To do that you have to gain new insights from all types of data.

One of SQL Server 2012’s most significant differentiators to help enterprises handle large datasets from SQL Server 2008 is its compatibility with Hadoop. Hadoop allows users to process large amounts of both structured and unstructured data to quickly find insights on that data, and because Hadoop is open-source, it can provide these insights at a low cost.

Microsoft’s strategy seems to be to offer path of least resistance for it’s customers to adopting Big Data – by extending existing tools such as SQL Server and Office to work seamlessly with new data types and allowing companies to take advantage of their existing investments while making new ones.

Read more and related articles from here.

Performance Impact of Using SELECT COUNT(*) In Queries

Some important information about count( * ) . Read it here.

[SS SLUG] January 2013 Meet-up

Database Administra​tion - PASS DBA Virtual Chapter Live Meeting Event

Date: January 23rd, 2013

Noon Eastern Time – http://www.timeanddate.com/worldclock/fixedtime.html?msg=PASS+DBA+Virtual+Chapter+Live+Meeting&iso=20130123T12&p1=198&ah=1

Topic: Revenge: Resource Governor – sponsored by Quest Software

Presenter: Dinesh Asanka

Abstract:

Resource Governor is a new technology introduced in SQL Server 2008 that enables you to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests.

Bio: Dinesh Asanka has been an MVP in SQL Server since 2008 and has been working with SQL Server since 1999. He has worked with SQL Server versions 7, 2000, 2005, 2008, 2012. He currently works as Production DBA and supporting a 24X7 database system. He has contributed to five SQL Server books, is Co-founder of SQL Server Sri Lankan User Group and actively works to promote SQL Server in Sri Lanka. He has presented at Tech Insight 2011, Malaysia.He is also a lecturer at Sri Lanka Institute of Information Technology for Proactive Database Administrator and Data ware House & Data Mining and is a contributing editor at SQL Server Performance.com

Door prize: There will be a raffle for a $100 Amazon gift certificate. You do not need to register for the meeting, but if you want to enter the raffle, you must register at https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=f3b4gtqq6qlp0bcb no later than 5:00 PM EST on January 22.

Live Meeting Link: https://www.livemeeting.com/cc/8000181573/join?id=BQ2D94&role=attend

Note: the meeting will be recorded and available on the meeting archives page at dba.sqlpass.org about a week after the event.

How to Get pageId for Each Record

This many not be something you can use in your application. This will be helpful during demonstrations of SQL Serve internals.

This sample code will tells you which row is in which page. Highlighted section has undocumented function.

  1: USE tempdb
  2: GO
  3: 
  4: CREATE table tblLoc
  5: (ID BIGINT IDENTITY,
  6: fld1 CHAR(1000) DEFAULT 'ABC',
  7: DATE1 Date DEFAULT GETDATE()
  8: )
  9: 
 10: INSERT INTO tblLOC
 11: 
 12: VALUES (DEFAULT,DEFAULT)
 13: GO 10000
 14: 
 15: SELECT * FROM tblLOC T
 16: CROSS APPLY fn_PhysLocCracker  (%%PHYSLOC%%) pl
 17: 

Result of this is,

SlotId is the record id of the page.

DownloadAdventureWorks Databases – 2008, 2008R2 and 2012

 

http://msftdbprodsamples.codeplex.com/

Microsoft SQL Server 2012, 2008R2, 2008, 2005, 2000 and 7.0 Builds

If you are not sure what you have installed, by looking at the version number you can get that information,

more details at http://www.sqlserverbuilds.blogspot.com/

NOLOGGING In Oracle Databases

In Oracle there is a valuable feature where you can disable logging for only one table. In SQL Server maximum granularity you have is database level. So in case, you are doing lot of inserts or index rebuilding for only one table you have the option of disabling the logging for that table.

ALTER TABLE t1 NOLOGGING;

So above command will suspend the logging for table t1,

Only the following statements can make use of nologging:

Alter Table

• Move Partition


• Split Partition


• Add Partition


• Merge Partition


• Modify Partition

Alter Index

• Split Partition


• Rebuild


• Rebuild Partition

Create Table

Create Index

More info from following links

http://www.orafaq.com/wiki/Nologging_and_force_logging

http://www.adp-gmbh.ch/ora/misc/nologging.html

http://www.dba-oracle.com/t_nologging_append.htm

So I think it is good feature to have for SQL Server as well.