Thursday, April 30, 2015

Configure Windows 2012 Server network to join domain using netsh scripting

C:\Users\Administrator>netsh interface ip show config

Configuration for interface "Ethernet0"
    DHCP enabled:                         Yes
    IP Address:                           192.168.229.134
    Subnet Prefix:                        192.168.229.0/24 (mask 255.255.255.0)
    Default Gateway:                      192.168.229.2
    Gateway Metric:                       0
    InterfaceMetric:                      10
    DNS servers configured through DHCP:  192.168.229.2
    Register with which suffix:           Primary only
    WINS servers configured through DHCP: 192.168.229.2

Configuration for interface "Loopback Pseudo-Interface 1"
    DHCP enabled:                         No
    IP Address:                           127.0.0.1
    Subnet Prefix:                        127.0.0.0/8 (mask 255.0.0.0)
    InterfaceMetric:                      50
    Statically Configured DNS Servers:    None
    Register with which suffix:           Primary only
    Statically Configured WINS Servers:   None

C:\Users\Administrator>netsh interface ipv4 set address "Ethernet0" static 192.168.229.210 255.255.255.0 192.168.229.200 1


C:\Users\Administrator>netsh interface ipv4 set dnsserver "Ethernet0" static 192.168.229.200 primary


C:\Users\Administrator>netsh interface ip show config

Configuration for interface "Ethernet0"
    DHCP enabled:                         No
    IP Address:                           192.168.229.210
    Subnet Prefix:                        192.168.229.0/24 (mask 255.255.255.0)
    Default Gateway:                      192.168.229.200
    Gateway Metric:                       1
    InterfaceMetric:                      10
    Statically Configured DNS Servers:    192.168.229.200
    Register with which suffix:           Primary only
    Statically Configured WINS Servers:   None

Configuration for interface "Loopback Pseudo-Interface 1"
    DHCP enabled:                         No
    IP Address:                           127.0.0.1
    Subnet Prefix:                        127.0.0.0/8 (mask 255.0.0.0)
    InterfaceMetric:                      50
    Statically Configured DNS Servers:    None
    Register with which suffix:           Primary only
    Statically Configured WINS Servers:   None

C:\Users\Administrator>netdom renamecomputer %computername% /newname:VMMSQL03
This operation will rename the computer WIN-7FVEEOP8PPF
to VMMSQL03.

Certain services, such as the Certificate Authority, rely on a fixed machine
name. If any services of this type are running on WIN-7FVEEOP8PPF,
then a computer name change would have an adverse impact.

Do you want to proceed (Y or N)?
y
The computer needs to be restarted in order to complete the operation.

The command completed successfully.


C:\Users\Administrator>shutdown /r /t 0

C:\Users\Administrator>netdom join VMMSQL03 /domain:dbaglobe.com /ud:dbaglobe\administrator /pd:*
Type the password associated with the domain user:

The computer needs to be restarted in order to complete the operation.

The command completed successfully.

C:\Users\Administrator>shutdown /r /t 0 /f

Saturday, April 18, 2015

Windows Server 2012 R2 prevent automatic logoff due to inactivity

Unlocked the missing Power Settings feature in Server 2012.

  1. Open the following registry key -HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\PowerSettings\7516b95f-f776-4464-8c53-06167f40cc99\8EC4B3A5-6868-48c2-BE75-4F3044BE88A7

  2. Set the following value - Attributes => 2

  3. Now open Control Panel>Power Options>Change Plan Settings>Change Advanced Power Settings
    a. The new Display section Console lock display off timeout is now available.
    b. Configure your “Plugged in” value accordingly (0 to disable)

  4. Set timeout to 0 to turn it off.

enter image description here

Tuesday, April 14, 2015

Differences between snapshot isolation and read committed isolation using row versioning

https://technet.microsoft.com/en-us/library/ms189050(v=sql.105).aspx

 

Property

Read-committed isolation level using row versioning

Snapshot isolation level

The database option that must be set to ON to enable the required support.

READ_COMMITTED_SNAPSHOT

ALLOW_SNAPSHOT_ISOLATION

How a session requests the specific type of row versioning.

Use the default read-committed isolation level, or run the SET TRANSACTION ISOLATION LEVEL statement to specify the READ COMMITTED isolation level. This can be done after the transaction starts.

Requires the execution of SET TRANSACTION ISOLATION LEVEL to specify the SNAPSHOT isolation level before the start of the transaction.

The version of data read by statements.

All data that was committed before the start of each statement.

All data that was committed before the start of each transaction.

How updates are handled.

Reverts from row versions to actual data to select rows to update and uses update locks on the data rows selected. Acquires exclusive locks on actual data rows to be modified. No update conflict detection.

Uses row versions to select rows to update. Tries to acquire an exclusive lock on the actual data row to be modified, and if the data has been modified by another transaction, an update conflict occurs and the snapshot transaction is terminated.

Update conflict detection.

None.

Integrated support. Cannot be disabled.

Sunday, April 12, 2015

Explain Lead/Lag with simple example

select RegionID,RegionDescription,
lag(RegionDescription,1,0) over(order by RegionID) proceding_row,
lead(RegionDescription,1,0) over(order by RegionID) following_row
from [dbo].[Region]

image

Created Indexed View in T-SQL

Wrong Example:

create view Employees_View as select * from [dbo].[Employees]
GO
create index Employees_View_Idx on Employees_View(LastName,First_name);
GO

What’s are possible errors for above 2 T-SQL Statements


Msg 1939, Level 16, State 1, Line 5
Cannot create index on view 'Employees_View' because the view is not schema bound.


Msg 1054, Level 15, State 6, Procedure Employees_View, Line 13
Syntax '*' is not allowed in schema-bound objects.


Msg 1940, Level 16, State 1, Line 36
Cannot create index on view 'Employees_View'. It does not have a unique clustered index.


Msg 1942, Level 16, State 1, Line 38
Cannot create index on view 'NORTHWND2.dbo.Employees_View'. It contains text, ntext, image, FILESTREAM or xml columns.

Correct Example:


drop view Employees_View
GO
create view Employees_View with schemabinding
as select [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[Title]
      ,[TitleOfCourtesy]
      ,[BirthDate]
      ,[HireDate]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[HomePhone]
      ,[Extension]
      ,[ReportsTo]
      ,[PhotoPath] from [dbo].[Employees]
GO

create unique clustered index Employees_View_Idx on Employees_View(LastName,Firstname);
GO

Different XML output format for “XML RAW”, “XML AUTO” and “ELEMENTS”

select C.CustomerID,C.AccountNumber,Soh.SalesOrderID,Soh.OrderDate
from AdventureWorks2012.Sales.SalesOrderHeader Soh
inner join AdventureWorks2012.Sales.Customer C
on Soh.CustomerID=C.CustomerID
where C.CustomerID=29580
for XML RAW

<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="43665" OrderDate="2005-07-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="44284" OrderDate="2005-10-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="45043" OrderDate="2006-01-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="45782" OrderDate="2006-04-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="46611" OrderDate="2006-07-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="47666" OrderDate="2006-10-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="48757" OrderDate="2007-01-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="49826" OrderDate="2007-04-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="51089" OrderDate="2007-07-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="55241" OrderDate="2007-10-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="61182" OrderDate="2008-01-01T00:00:00" />
<row CustomerID="29580" AccountNumber="AW00029580" SalesOrderID="67266" OrderDate="2008-04-01T00:00:00" />

select C.CustomerID,C.AccountNumber,Soh.SalesOrderID,Soh.OrderDate
from AdventureWorks2012.Sales.SalesOrderHeader Soh
inner join AdventureWorks2012.Sales.Customer C
on Soh.CustomerID=C.CustomerID
where C.CustomerID=29580
for XML RAW ,ELEMENTS

<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>43665</SalesOrderID>
  <OrderDate>2005-07-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>44284</SalesOrderID>
  <OrderDate>2005-10-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>45043</SalesOrderID>
  <OrderDate>2006-01-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>45782</SalesOrderID>
  <OrderDate>2006-04-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>46611</SalesOrderID>
  <OrderDate>2006-07-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>47666</SalesOrderID>
  <OrderDate>2006-10-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>48757</SalesOrderID>
  <OrderDate>2007-01-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>49826</SalesOrderID>
  <OrderDate>2007-04-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>51089</SalesOrderID>
  <OrderDate>2007-07-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>55241</SalesOrderID>
  <OrderDate>2007-10-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>61182</SalesOrderID>
  <OrderDate>2008-01-01T00:00:00</OrderDate>
</row>
<row>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <SalesOrderID>67266</SalesOrderID>
  <OrderDate>2008-04-01T00:00:00</OrderDate>
</row>

select C.CustomerID,C.AccountNumber,Soh.SalesOrderID,Soh.OrderDate
from AdventureWorks2012.Sales.SalesOrderHeader Soh
inner join AdventureWorks2012.Sales.Customer C
on Soh.CustomerID=C.CustomerID
where C.CustomerID=29580
for XML AUTO

<C CustomerID="29580" AccountNumber="AW00029580">
  <Soh SalesOrderID="43665" OrderDate="2005-07-01T00:00:00" />
  <Soh SalesOrderID="44284" OrderDate="2005-10-01T00:00:00" />
  <Soh SalesOrderID="45043" OrderDate="2006-01-01T00:00:00" />
  <Soh SalesOrderID="45782" OrderDate="2006-04-01T00:00:00" />
  <Soh SalesOrderID="46611" OrderDate="2006-07-01T00:00:00" />
  <Soh SalesOrderID="47666" OrderDate="2006-10-01T00:00:00" />
  <Soh SalesOrderID="48757" OrderDate="2007-01-01T00:00:00" />
  <Soh SalesOrderID="49826" OrderDate="2007-04-01T00:00:00" />
  <Soh SalesOrderID="51089" OrderDate="2007-07-01T00:00:00" />
  <Soh SalesOrderID="55241" OrderDate="2007-10-01T00:00:00" />
  <Soh SalesOrderID="61182" OrderDate="2008-01-01T00:00:00" />
  <Soh SalesOrderID="67266" OrderDate="2008-04-01T00:00:00" />
</C>

select C.CustomerID,C.AccountNumber,Soh.SalesOrderID,Soh.OrderDate
from AdventureWorks2012.Sales.SalesOrderHeader Soh
inner join AdventureWorks2012.Sales.Customer C
on Soh.CustomerID=C.CustomerID
where C.CustomerID=29580
for XML AUTO ,ELEMENTS

<C>
  <CustomerID>29580</CustomerID>
  <AccountNumber>AW00029580</AccountNumber>
  <Soh>
    <SalesOrderID>43665</SalesOrderID>
    <OrderDate>2005-07-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>44284</SalesOrderID>
    <OrderDate>2005-10-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>45043</SalesOrderID>
    <OrderDate>2006-01-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>45782</SalesOrderID>
    <OrderDate>2006-04-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>46611</SalesOrderID>
    <OrderDate>2006-07-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>47666</SalesOrderID>
    <OrderDate>2006-10-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>48757</SalesOrderID>
    <OrderDate>2007-01-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>49826</SalesOrderID>
    <OrderDate>2007-04-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>51089</SalesOrderID>
    <OrderDate>2007-07-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>55241</SalesOrderID>
    <OrderDate>2007-10-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>61182</SalesOrderID>
    <OrderDate>2008-01-01T00:00:00</OrderDate>
  </Soh>
  <Soh>
    <SalesOrderID>67266</SalesOrderID>
    <OrderDate>2008-04-01T00:00:00</OrderDate>
  </Soh>
</C>

Saturday, April 11, 2015

Using WITH ENCRYPTION option obfuscates the definition of the procedure in T-SQL

CREATE PROCEDURE [dbo].[CustOrdersDetail_Encrypted] @OrderID int
WITH ENCRYPTION
AS
SELECT ProductName,
    UnitPrice=ROUND(Od.UnitPrice, 2),
    Quantity,
    Discount=CONVERT(int, Discount * 100),
    ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

Effects after encryption

 

image

 

image

image

image

image