SQL (70-431) Question of the week…Q3

You are the database administrator for a shipping company named Cargoflow. You are asked to create a database for the company’s marketing department for trend analysis of shipments. This database will be bulk loaded with information from a data warehouse when it is first created. Data will be analyzed but not modified in any way. You are trying to decide on an appropriate recovery model for the database. Which recovery model should you implement for this new database? Choose the best option(s) from those listed below.

a) Full recovery
b) Bulk-logged recovery
c) Simple recovery
d) Warehouse recovery

Self Evaluation:
Compare your answer to the explanation and correct option(s) provided below.

Explanation:
The simple recovery model is the most appropriate recovery model to use in this scenario. Since the data in the database will never change, point-of-failure recovery is not necessary. This means that data in the transaction log is not critical to recovering the database and does not necessitate being backed up. The simple recovery model relies strictly on full and differential backups of the database to recover.

Correct Option(s):
c) Simple recovery

Incorrect Option(s):
a) Full recovery – The full recovery model is inappropriate in this scenario due to the unnecessary administrative overhead associated with transaction log backups.
b) Bulk-logged recovery – The bulk-logged recovery model is inappropriate in this scenario due to the unnecessary administrative overhead associated with transaction log backups.
d) Warehouse recovery – SQL Server 2005 does not support a warehouse recovery model.

Questions Provided by SkillSoft

SQL (70-431) Question of the week…Q2

You are creating a new SQL Server 2005 database for Brocadero’s sales department. To ensure maximum availability and reliability you decide to implement the database across multiple data files. When creating the data files you want to follow Microsoft’s recommended best practices for naming. How should the primary and secondary data files be named? Choose the best option(s) from those listed below.

a) The primary data file should have an .mdf extension.
b) The primary data file should have an .ndf extension.
c) The secondary data file should have an .mdf extension.
d) The secondary data file should have an .ndf extension.

Self Evaluation:
Compare your answer to the explanation and correct option(s) provided below.

Explanation:
Microsoft’s recommended best practices state that a heavily used database should store the database catalog in a primary data file and all data and objects in secondary data files for the best performance, availability, and reliability. Microsoft recommends that primary data files use the .mdf file extension, while secondary data files should use the .ndf extension.

Correct Option(s):
a) The primary data file should have an .mdf extension.
d) The secondary data file should have an .ndf extension.

Incorrect Option(s):
b) The primary data file should have an .ndf extension – Primary data files should use the .mdf extension.
c) The secondary data file should have an .mdf extension – Secondary data files should use the .ndf extension.

Questions Provided by SkillSoft

SQL (70-431) Question of the week…Q1

You are the SQL Server administrator for your company. You have been assigned the task of installing Microsoft SQL Server 2005 Enterprise Edition on an existing server. This server has a 600 MHz Pentium III processor, 256 MB of RAM, 10 GB hard disk and Microsoft Windows 2000 Server with Service Pack 1 installed. All of the components installed are upgradeable if required. What components must you upgrade before installing SQL Server 2005 on this server? Choose the best option(s) from those listed below.

a) Processor
b) RAM
c) Hard disk
d) Operating System

Self Evaluation:
Compare your answer to the explanation and correct option(s) provided below.

Explanation:
Before installing SQL Server 2005 Enterprise Edition on the server, you would need to upgrade to 512 MB of RAM and install Service Pack 4 or later just to meet the minimum system requirements. Microsoft recommends the following system requirements for a 32 bit system:

Processor – 600 MHz Pentium III-compatible or faster processor; 1 GHz or faster processor recommended

Operating System – Microsoft Windows 2000 Server with Service Pack (SP) 4 or later; Windows Server 2003 Standard Edition, Enterprise Edition, or Datacenter Edition with SP 1 or later; Windows Small Business Server 2003 with SP 1 or later

Memory – 512 MB of RAM or more; 1 GB or more recommended

Hard Disk – Approximately 350 MB of available hard-disk space for the recommended installation

Correct Option(s):
b) RAM
d) Operating System

Incorrect Option(s):
a) Processor – The minimum requirement for a processor is a 600 MHz Pentium III-compatible; therefore, the processor in this scenario meets the minimum requirements.

c) Hard disk – The minimum requirement for a hard disk is approximately 350 MB of available hard-disk space; therefore, the available hard disk space in this scenario exceeds requirements.

Questions Provided by SkillSoft

Stored Procedures…good or bad?

This topic can be an ongoing argument between the parties who swear by Stored Procedures (SP) and the parties that absolutely apose them. Each perspective make valid points, but it is entirely up to you to decide. If you love them that is great and if you hate them that is great too.

I have only been using (SPs) for a short time so I cannot complain, but then again I am not an expert. I do find that (SPs) help me a lot with my daily support. So let’s run through an example of creating a simple (SP) that returns a simple result set. Then we will create a slightly more advanced (SP) that requires a variable in order to return a specific result set. Again these are simple examples!

Let’s just say you have a table called “tbl_Contacts” and in it you have firstName, LastName, DOB, a computed column called AGE and a IsDeleted column with a BIT data type (0 being active and 1 being deleted).

CREATE PROC sp_GetAllActiveContacts
AS
SELECT firstName, lastName, CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (IsDeleted = 0) –Returns all active records

To test your freshly created (SP) run the following:
EXEC sp_GetAllActiveContacts

If you do not have a table called tbl_Contacts don’t worry just use the examples I posted below!

— [ CREATE TABLE ]
CREATE TABLE [dbo].[tbl_Contacts]
(
 [ContactID] [int] NOT NULL,
 [firstName] [nvarchar](50) NOT NULL,
 [lastName] [nvarchar](50) NOT NULL,
 [IsDeleted] [bit] NULL CONSTRAINT [DF_tbl_Contacts_conDeleted] DEFAULT ((0)),
 [createDate] [datetime] NULL CONSTRAINT [DF_tbl_Contacts_conCreateDate] DEFAULT (getdate()),
 [DOB] [smalldatetime] NULL,
 [Age] AS (datediff(year,[DOB],getdate()))
)

— [ CREATE RECORDS ]
INSERT INTO tbl_Contacts
VALUES (1,‘Adam’,‘Sandler’,DEFAULT,DEFAULT,‘9-9-1966’)
INSERT INTO tbl_Contacts
VALUES (2,‘Emmanuelle’,‘Chriqui’,DEFAULT,DEFAULT,’12-10-1977′)
INSERT INTO tbl_Contacts
VALUES (3,‘Rob’,’Schneider’,DEFAULT,DEFAULT,’10-31-1963′)
INSERT INTO tbl_Contacts
VALUES (4,‘Val’,’Kilmer’,DEFAULT,DEFAULT,’12-31-1959′)
INSERT INTO tbl_Contacts
VALUES (5,‘Megan’,’Fox’,DEFAULT,DEFAULT,‘5-16-1986’)

— [ VERIFY RECORDS ]
SELECT * FROM tbl_Contacts

— [ CREATE STORED PROCEDURE ]
CREATE PROC sp_GetAllActiveContacts
AS
SELECT firstName,lastName,CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (IsDeleted = 0)

— [ TEST STORED PROCEDURE ]
EXEC sp_GetAllActiveContacts

— [ CREATE STORED PROCEDURE WITH VARIABLE ]
CREATE PROC sp_GetAllActiveContactsByID ( @ContactID INT )
AS
SELECT firstName,lastName,CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (ContactID = @ContactID) AND (IsDeleted = 0)

— [ TEST STORED PROCEDURE ]
DECLARE @ID INT
SET @ID = 1
WHILE @ID <= 5
BEGIN
   EXEC sp_GetAllActiveContactsByID @ID
   SET @ID = @ID +1
END

Table Variables…SQL Server 2005

If you have ever created or have used #tempTables in SQL then using Table Variables will be a snap!

the norm for #tempTables are:

CREATE TABLE #tempTable
(
tempID INT NOT NULL,
firstName NVARCHAR(50),
createDate DATETIME DEFAULT (getdate() )
)

INSERT INTO #tempTable VALUES (100,‘Samson’,DEFAULT)
INSERT INTO #tempTable VALUES (200,‘Stephen’,DEFAULT)
INSERT INTO #tempTable VALUES (300,‘David’,DEFAULT)
INSERT INTO #tempTable VALUES (400,‘Peter’,DEFAULT)
INSERT INTO #tempTable VALUES (500,‘Diana’,DEFAULT)

SELECT * FROM #tempTable
DROP TABLE #tempTable



the norm for Table Variables:

DECLARE @tableVariable TABLE
(
tableVarID INT NOT NULL,
firstName NVARCHAR(50),
createDate DATETIME DEFAULT (getdate() )
)

INSERT INTO @tableVariable VALUES (100,‘Samson’,DEFAULT)
INSERT INTO @tableVariable VALUES (200,‘Stephen’,DEFAULT)
INSERT INTO @tableVariable VALUES (300,‘David’,DEFAULT)
INSERT INTO @tableVariable VALUES (400,‘Peter’,DEFAULT)
INSERT INTO @tableVariable VALUES (500,‘Diana’,DEFAULT)

SELECT * FROM @tableVariable
DELETE @tableVariable

enable xp_cmdshell…SQL Server 2005

In order to determine if xp_cmdshell is enabled there are a few ways to go about it. The quickest way for me is to run the following in a query window:

xp_cmdshell ‘echo hello world’

If you see Hello World in the output then xp_cmdshell is enabled and you are good to go.

However, if received the following error:

“Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online. “

that means xp_cmdshell is not enabled! So let’s enable it! There are a few ways to do it…

A. Run a sql statement

1. EXEC sp_configure ‘show advanced options’, 1
2. RECONFIGURE
3. EXEC sp_configure ‘xp_cmdshell’, 1
4. RECONFIGURE

B. Use the GUI

1. Open SQL Server Surface Area Configuration
2. Click on Surface Area Configuration for Features



3. Click on xp_cmdshell



4. Check Enable xp_cmdshell
5. Click OK