Multiboot: Vista and Windows Server 2003--bcdedit

One SATA drive with two primary partitions created.

I installed Vista first, and then install Windows Server 2003 in the second partition.

I can only boot into Windows Server 2003.

To make the computer boot into Vista, I restart my computer with booting from Vista DVD. Repair your system and choose the Startup Repair.

It is good that I could boot into Vista. But where is my Windows Server 2003? I don't want to reinstall both OSes.

C:\Windows\system32>bcdedit /displayorder {ntldr} /addlast

Problem solved.

====

One EIDE hard drive:80GB.

1. Install Vista in the first primary partition with 30GB. And then create a second Primary Partition for Windows server 2003. You must not install Windows server 2003 on a logical drive.
2. After Vista is installed, I install the Windows Server 2003 on the second primary partition. The system would boot up.
3. Start my system from Vista DVD--Repair --Startup Repair
4. Restart the system into Vista;

Open Command Prompt Windows as Administrator

C:\Windows\system32>bcdedit /displayorder {ntldr} /addlast

• Restart the system and select the Earlier Version of Windows to continue Windows Server 2003 installation.

SQL: How to selectively create an index?

CREATE TABLE Sales
(
VideoID INT identity (1,1) not null Primary Key NonClustered,
VideoTitle varchar(15) NOT NULL,
RetailPrice Money NULL,
WholesalePrice Money NOT NULL,
Genre Char(15) NULL,
Barcode char(20) NOT NULL
);

You want to run the following query:

SELECT VideoID,VideoTitle, RetailPrice From Sales Order By VideoTitle

SELECT VideoTitle,Barcode,VideoID, WholesalePrice, Genre From Sales WHERE VideoID=5

What indexes should you create?

Solution:

Create Clustered index CL_videoTitle ON Sales(VideoTitle);

Create NONCLUSTERED INDEX inBV ON Sales(Barcode,VideoID)
INCLUDE (WholesalePrices, Genre);

==
You have a table named product, which has 40 columns. The most queries are as follows:
SELECT [Name],[ProductLine],[ListPrice],[Class],[Style]
FROM Product
WHERE ProductLine='T'

SELECT * FROM Product
ORDER BY [Name]

Solution:
Create a clustered index on the Name column because both queries include the [Name] column as its first field.

Create a nonclustered index on the ProductLine column and include the ListPrice,Class and Style columns.

MBSA and Windows Server Update Services (WSUS)

Your intranet is restricted from accessing the Internet. You implement WSUS in your intranet. You want to identify the computers with missing patches, hotfixes, and service packs. How do you do that?

By default, when you run Microsoft Baseline Security Analyzer, it checks your computers against Microsoft Windows Update. You should configure the MBSA to check the vulnerabilities against your WSUS.

Task Manager: Server Performance problems

Users report that all client applications that connect to SERVER are responding slowly. You discover that the SERVER's CPU, memory, and disk utilization is low. How do you identify the problem?

Task Manager to examine utilization of SERVER's network adapter.

Smart use of Secedit.exe

You create a security template that disables unnecessary services and other security settings. You must ensure that the template automatically overrides any manual configuration changes made by other administrators within an hour.

Configure Task Scheduler to import the security template by using Secedit.exe. Configure the task to run every half hour.

Performance alert: Denial-of-service (DoS)

How to use the performance alert to quickly response and appropriately trace the attack?

Create a performance alert to start a Network Monitor capture;
Create a performance alert to monitor the Bytes Total/sec counter;

DMV--sys.dm_db_index_usage_stats

Which non-clustered index(es) is/are most frequently used?

ibhect_id: identify a table

index_id --ID of the index. index_id is unique only within the object.
0 = Heap
1 = Clustered index
> 1 = Nonclustered index

use AdventureWorks
go
SELECT object_id,index_id from sys.dm_db_index_usage_stats

WHERE (user_seeks > 0
or user_scans > 0
or user_lookups > 0)
and index_id>1

SSIS: package configuration--deployment utility

Scenario: development environment and production environment

You created SQL server SSIS package to import data in your development environment. After successfully testing the SSIS package, you want to deploy the SSIS package in your production environment. However, your production environment uses a different table names than your development environment. What do you do?

Creata a SQL Server package configuration
Build a deployment utility (right click on the Project--properties--Deployment Utility--AllowConfigurationChanges to True--CreateDeploymentUtility to True--Build the project);
Copy the deployment folder of your SSIS project to your production SQL Server;
Execute the manifest file;

Note:
The deployment utility is a folder that contains the files you need to deploy the packages in an Integration Services project on a different server.

AllowConfigurationChange --A value that specifies whether configurations can be updated during deployment, such as the table name change.

SQL: OpenQuery, OpenRowSet, and OpenDataSource

My SQL Server instance is: Vision.

Enable the Ad Hoc query:

Run the Ad Hoc query:

The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked-server name. Therefore, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement.

SELECT TOP 5 *
FROM OPENDATASOURCE('SQLNCLI',
'Data Source=Vision;Integrated Security=SSPI')
.AdventureWorks.HumanResources.Employee

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Vision;Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks.HumanResources.Department
ORDER BY GroupName, Name') AS a;
===
OpenQuery is used with a Linked Server.

--create a linked server, first
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
--run the OPENQUERY function
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO

SQL: Bulk-logged recovery model

One application scenario for Bulk-logged recovery model

In replication topology, the Subscriber database is set to bulk-logged recovery model when the snapshot is being applied. Doing so allows minimal logging of the bulk inserts performed during the application of the snapshot at the subscriber. After the snapshot application, you can change the subscription database to FULL RECOVERY MODEL.

SQL:NOT FOR REPLICATION

Create a transaction publication:

Modify the articles' properties:Copy foreign key constraints, Copy check constraints, and Copy user triggers are changed to TRUE.
If Foreign Key Constraint, Check Constraint, and User Triggers are not copied to Subscribers, they are definitely not enforced. After they are copied to the Subscribers, NOT FOR REPLICATION option will make the differences.

If you encrypt your triggers, they are not copied to the subscribers.

If a row is inserted by a user at the Publisher and that insert satisfies a check constraint on the table, database engine will not enforce the same constraint when the row is inserted by a replication agent at the Subscriber.

SSIS: Fuzzy Grouping

Source file: sample.txt

Mr.,Gustavo,Achong,gustavo0@adventure-works.com
Ms.,Catherine,Abel,catherine0@adventure-works.com
Ms.,Kim,Abercrombie,kim2@adventure-works.com
Sr.,Humberto,Acevedo,humberto0@adventure-works.com
Sr.,Pilar,Ackerman,pilar1@adventure-works.com
Mr.,Gustavo1,Achong,gustavo1@adventure-works.com
Mr,Gustavo2,Achong,gustavo2@adventure-works.com
Ms.,Catherine1,Abel,catherine1@adventure-works.com
Ms,Kim,Abercrombie,kim2@adventure-works.com
Ms.,Kim,Abercrombie,kimAbercrombie@adventure-works.com

BIDS--new project--Drag "Data Flow control" onto Control Flow pane and double click it;

Flat File Source:

Connection Managers: Flat

File Name: C:\Sample.txt

Fuzzy Grouping:

As seen, select the fuzzy criteria: Title, FirstName,LastName, and EmailAddress. Similarity Threshold: 0.8

Flat File Destination:

Flat File Connection Manager:Out

File Name: c:\output.txt

Input and output mapping:

1,1,1,Mr.,Gustavo,Achong,gustavo0@adventure-works.com
6,1,0.92130822,Mr.,Gustavo1,Achong,gustavo1@adventure-works.com
7,1,0.92130822,Mr,Gustavo2,Achong,gustavo2@adventure-works.com
2,2,1,Ms.,Catherine,Abel,catherine0@adventure-works.com
8,2,0.93621671,Ms.,Catherine1,Abel,catherine1@adventure-works.com
4,4,1,Sr.,Humberto,Acevedo,humberto0@adventure-works.com
5,5,1,Sr.,Pilar,Ackerman,pilar1@adventure-works.com
9,9,1,Ms,Kim,Abercrombie,kim2@adventure-works.com
3,9,0.98750001,Ms.,Kim,Abercrombie,kim2@adventure-works.com
10,10,1,Ms.,Kim,Abercrombie,kimAbercrombie@adventure-works.com

_key_out identifies the grouping.

SSIS: Fuzzy Lookup

Sample.txt

1.00010001;AED;Afghanl;0.99960016
1.00010001;ADD;Algerian Dinarl;1.00100100
1.00020004;ARR;Argentine Pesol;0.99990001

Referenced Table:dbo.DimCurrency

The data in Sample.txt, AED, ADD, ARR can not be used for transformation because the referenced table does not have the exact match. Fuzzy lookup control can solve the problem. Based on the CurrencyAlternateKey and CurrencyName, the fuzzy lookup control can find the CurrencyKey.

Flat File Source:

Column 0 --AverageRate --numeric [DT_NUMERIC], Data Precision:18 and DataScale:8
Column 1--CurrencyAlternateKey--Unicode text stream [DT_WSTR], OutputWidth:3
Column 2--CurrencyName--Unicode text stream [DT_WSTR], OutputWidth:50

Column 3 --CurrentRate--numeric [DT_NUMERIC], Data Precision:18 and DataScale:8
Fuzzy Lookup:

Based on CurrencyAlternateKey and CurrencyName, the CurrencyKey will be closely matched.

Flat File Destination:

Execute the package and open the output file:


1.00010001,AED,Afghanl,.99960016,1
1.00010001,ADD,Algerian Dinarl,1.00100100,2
1.00020004,ARR,Argentine Pesol,.99990001,3

Adjust the Similarity threshold to 0.50

Re-execute the package, the output file will have:

1.00010001,AED,Afghanl,.99960016,
1.00010001,ADD,Algerian Dinarl,1.00100100,2
1.00020004,ARR,Argentine Pesol,.99990001,3


The CurrencyKey of the first row cannot be located. If you move the Similarity threshold to 0.8, the CurrencyKey of the first row and second row cannot be located.

SSIS: Lookup

Lookup:Exact Match

Database: AdventureWorksDW

Reference Tables: dbo.DimTime and dbo.DimCurrency

SELECT TimeKey,FullDateALternateKey FROM DimTime

SELECT CurrencyKey,CurrencyAlternateKey FROM DimCurrency

Flat File: sample.txt

1.00050025;AED;9/7/2001 0:00;0.99990001
1.00050024;AED;9/8/2001 0:00;1.001001001
1.00030025;AED;9/9/2001 0:00;1
1.00010001;AED;9/10/2001 0:00;1.00040016
1.00020004;AED;9/11/2001 0:00;0.99980801
1.00050001;AED;9/12/2001 0:00;1.001101211

Data in sample.txt file will be transformed. The transformation references two tables:DimTime and DimCurrency.



BIDS--Create an SSIS project


Drag "Data Flow Task" onto Control Flow pane;


Double Click "Data Flow Task" to navigate to Data Flow pane;





"Flat File Source"

File Name=C:\sample.txt and create the advanced mapping as:

• Column 0 --AverageRate --numeric [DT_NUMERIC], Data Precision:18 and DataScale:8


• Column 1--CurrencyAlternateKey--Unicode text stream [DT_WSTR], OutputWidth:3


• Column 2--FullDateAlternateKey--database timestamp [DT_DBTIMESTAMP]


• Column 3 --EndOfDayRate--numeric [DT_NUMERIC], Data Precision:18 and DataScale:8

"Lookup" transformation item
Connection to dbo.DimCurrency of AdventureWorksDW database;

Make sure you tick the CurrencyKey in the Available Lookup Columns. OK. Save it.



"Lookup 1 control"



Connection to dbo.DimTime of AdventureWorksDW database;


Make sure you tick the TimeKey column.


"Flat File Destination"

New Flat File Connection Manager: File Name C:\outputFile.txt

Save the package and execute it;

The c:\outputFile.txt will have:

1.00050025,.9999000100,35,69
1.00050024,1.0010010010,35,70
1.00030025,1.0000000000,35,71
1.00010001,1.0004001600,35,72
1.00020004,.9998080100,35,73
1.00050001,1.0011012110,35,74

====

data cleansing

Flat File:sample1.txt
1.00010001;AED;7/3/2001 1:00;0.99960016
1.00010001;AEC;7/4/2001 0:00;1.00100100
1.00020004;AFB;7/5/2001 2:00;0.99990001
1.00020002;ALL;7/6/2001 0:00;1.00040015
1.00050025;ALL;7/7/2001 4:00;0.99990001
1.00050024;ALH;7/8/2001 2:00;1.001001001
1.00030025;AND;7/9/2001 0:00;1
1.00010001;AND;7/10/2001 5:00;1.00040016
1.00020004;AMD;7/11/2001 1:00;0.99980801
1.00050001;AMD;7/12/2001 0:00;1.00110121

AED and AEC are not in the dbo.DimCurrency. Similarly, the time (in red) is not in dbo.DimTime table.

If you apply the sample1.txt to the above package, it fails with the error message:

Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\sample1.txt" has ended.
Information: 0x402090DD at Data Flow Task, Flat File Destination [745]: The processing of file "D:\AOL\outputFile.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (745)" wrote 0 rows.

SSIS:passing a value from package A to package B

Parent Package (A) and Child Package (B)

USE AdventureWorks
GO
CREATE TABLE tableName
(
Tid INT NULL,
TName VARCHAR(50) NULL
)
INSERT tableName (Tid,TName)
VALUES (100,'Kim');

Create a Integration Service Solution with two projects: P_SQL.dtsx and C_SQL.dtsx.

Double_click P_SQL.dtsx;

Drag "Execute SQL Task" and "Execute Package Task" onto Control Flow pane;

SSIS menu--Variables--Add a new variable: varName with INT32 type

Double_Click "Execute SQL Task"
In General screen, three inputs are expected:

SQL Statement: SELECT Tid FROM dbo.tableName WHERE TName='Kim';

OLE DB connection: QUEEN.AdventureWorks

ResultSet: Single Row

When the SQL statement returns a single row, the column of ResultSet can be assigned to a package variable.

Double_click "Execute Package Task": call C_SQL.dtsx package

Save the P_SQL.dtsx package.

Double_click C_SQL.dtsx package;

Drag "Execute SQL Task" onto Control Flow pane;

Create a same variable as the one in P_SQL.dtsx package: varID with INT32 type

Double_click "Execute SQL Task";

When OLEDB connectionType is chosen, you must use ? as the parameter placeholder.

SELECT * INTO test5 FROM HumanResources.Employee Where EmployeeID>?

Mapping the variable:varID to the parameter in the query

OLEDB connection type uses ordinal number (0,1,2,3,...) to identify the parameters. The query has one parameter whose ordinal number is 0.

Parameter Mapping:
INPUT: passing the value of a variable onto a query parameter
OUTPUT:assigning the value from the query parameter to a variable



C_SQL.dtsx package configuration; make sure you choose the Parent Package Variable


Save the S_SQL.dtsx package;


Double Click P_SQL.dtsx

Execute the package

You will find a Test5 table created and populated with data whose EmployeeID number is greater than 100.

Observation:
Don't define a table parameter. I tried the following: SELECT * FROM ?

The variable and the parameter cannot be mapped.

======
FROM book online:

The Execute Package task can run packages stored in the SQL Server msdb database and packages stored in the file system. The task uses an OLE DB connection manager to connect to SQL Server and a File connection manager to access the file system.

The Execute Package task can also run a database maintenance plan, which lets you manage both SSIS packages and database maintenance plans in the same Integration Services solution. A database maintenance plan is similar to an SSIS package, but a plan can include only database maintenance tasks, and it is always stored in msdb.

A child package requires access to data that is dynamically derived by the parent package. For example, the parent package extracts data from a table and loads the rowset into a variable, and the child package performs additional operations on the data.