MSSQL Query Index Stats

/ MSSQL Server /

Display index physical stats.

SELECT * FROM sys.DM_DB_INDEX_PHYSICAL_STATS (
  DB_ID('database_name'),
  Object_ID('schema.tablename'),
  NULL, -- Index ID
  NULL, -- Partition Number
  'DETAILED' -- Mode: [NULL|DEFAULT|LIMITED|SAMPLED|DETAILED]
)
fragmentation <10% geen actie nodig
fragmentation 10-30% reorganize index (ALTER INDEX index_name ON schema.table_name REORGANIZE)
fragmentation >30% rebuild indexes (ALTER INDEX… REBUILD of CREATE INDEX met DROP_EXISTING optie)

MSSQL Create Indexed View

/ MSSQL Server /

Instruction written in the Dutch language…

M.b.v. onderstaande T-SQL statements maak je een indexed view. Bij een indexed view worden de gegevens als aparte objecten opgeslagen in de database. Dit kan in bepaalde omstandigheden performance winst opleveren. Er zijn echter ook een aantal restricties. Bij een update van een record waar een dergelijke view van afhankelijk is wordt ook de view bijgewerkt. Dit kan dus ook performance verlies opleveren. SCHEMABINDING zorgt ervoor dat onderliggende tabellen niet kunnen worden aangepast zonder eerst de VIEW te verwijderen.

SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Indexed_Viewname] WITH SCHEMABINDING
AS
SELECT x2.column1 as [column1a], column1, x3.column2
FROM dbo.table1 x1
JOIN dbo.table2 x2 ON x1.column3 = x2.column5
JOIN dbo.table3 x3 ON x2.column4 = x3.column1
WHERE column2 = 'X'

Met onderstaande T-SQL statement maak je een clustered index.

CREATE UNIQUE CLUSTERED INDEX Cl_Idx_Viewname
ON dbo.Indexed_Viewname(column1)

 

MSSQL Create Table Partitions

/ MSSQL Server /
Instruction written in the Dutch language…
  1. Open SQL Server Management Studio en klik met de rechter muisknop op de gewenste database. Selecteer properties.
  2. Maak nieuwe bestandsgroepen op de de Filegroups pagina. Markeer zonodig de filegroup als readonly.
  3. Selecteer de gewenste database in het drop-down menu.
  4. Voer onderstaande query uit om de partition function aan te maken op basis van een kolom met datums waarbij de eerste partitie stopt bij datums kleiner of gelijk aan de huidige datum – 365 dagen (LEFT). Bij RANGE RIGHT wordt de tabel gesplitst bij waarden kleiner dande opgegeven waarde. 
    CREATE PARTITION FUNCTION pfTablename (datatype) AS RANGE LEFT FOR VALUES (Getdate() - 365)
  5. Voer onderstaande query uit om een partion schemeaan te maken met de eerder aangemaakte filegroups. 
    CREATE PARTITION SCHEME pfTablename AS PARTITION pfTablename TO (Filegroup1, Filegroup2, Filegroup3)
  6. Koppel het partition scheme aan de kolom in de tabel m.b.v. de table designer (Regular Data Space Specification).

MSSQL JOIN Selection Queries

/ MSSQL Server /

INNER JOIN: Only records with joing fields in both tables.
LEFT OUTER JOIN: All records from table 1 and only matching fields from table 2 that match on join field.
RIGHT OUTER JOIN: All records from table 2 and only matchin records from table 2 that match on join field

Below an example…

SELECT DISTINCT [Database].[dbo].[Table1].[Field1] AS [Table1-Field1]
,[Database].[dbo].[Table1].[Field2] AS [Table1-Field2]
,[Database].[dbo].[Table2].[Field3] AS [Table2-Field3]
FROM [Database].[dbo].[Table1]
INNER JOIN [Database].[dbo].[Table2] ON [Database].[dbo].[Table2].[Field0] = [Database].[dbo].[Table1].[Field0]
WHERE [Database].[dbo].[Table1].[Field1]  [Database].[dbo].[Table2].[Field3]
ORDER BY [Database].[dbo].[Table1].[Field1],[Database].[dbo].[Table1].[Field1];

 

MSAD Move FSMO Roles

/ MS Windows Server /

Commands or tools to move or view Active Directory Flexible Single Master Operations roles.

Schema Master (1 per forrest)

regsvr32 schmmgmt.dll
mmc -> Schema

Domain Naming Master (1 per forrest)

AD Domains and Trusts

RID Master (uitgave SID’s) (1 per domain)

AD Users and Computers

Infrastructure Master (AD infrastructuur) (1 per domain)

AD Users and Computers

PDC Emulator (Time Server, log-on NT4 clients, Password chenges) (1 per domain)

AD Users and Computers

 

Netwerk IPv4 Subnetting

/ Other / 
max aantal subnetten: 2^n (-2) // n = aantal masked bits *subnet* mask
max aantal hosts in subnet = 2^n -2 // n = ongemaskeerde bits

next multiple of 8 = next mult. of 8 greater or eq. to CIDR netmask not.
interesting value = (next multiple of 8) / 8

incremental value = 2^ (next multiple of 8 - CIDR netmask notation)

bijv. 192.168.10.50/27:
incremental value = 2^(32-27) = 2^5 = 32
interesting value = 32 / 8 = 4
netwerkadressen: 192.168.10.0, 192.168.10.32, 192.168.10.64, enz.
broadcastadressen: 192.168.10.31, 192.168.10.63, 192.168.10.95, enz.
netwerk 192.168.10.32
broadcast 192.168.1.63 (192.168.10.(64-1))

bijv. 10.6.127.255/14:
incremental value: 2^(16-14) = 2^2 = 4
interesting value = 16 / 8 = 2
netwerkadres: 10.4.0.0
broadcast: 10.7.255.255 ( 10.(8.0.0-1)

 

OpenSSL Certificate Commands & Examples

/ GNU/Linux /

Below script creates al selfgesigned certificate from a private key and removes the password from the key so you can you can use the key-pair in Apache.

# Create Key and Certificate Signing Request (option -des3 creates a triple des encrypted key)...
openssl genrsa -des3 -out server.key 4096
openssl req -new -key server.key -out server.csr

# Remove password from key and sign certificate with key... 
cp server.key server.key.org
openssl rsa -in server.key.org -out server.key
openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt

# Re-encrypt private key...
openssl rsa -des -in server.key.org -out server.key
openssl rsa -aes256 -in server.key.org -out server.key

For ease you can add all key and signing options to a config file. This way you can also add a subjectAlternate to the certificate.

>openssl req -new -config server.cnf -key server.key -out server.csr

# server.cnf #
[ req ]
default_bits = 4096
prompt = no
encrypt_key = no
distinguished_name = dn
req_extensions = req_ext

[ dn ]
C = NL
O = Totietoot
CN = examplefqdn.totietoot.nl

[ req_ext ]
subjectAltName = DNS:examplefqdn.totietoot.nl, DNS:examplealtname.totietoot.nl

Use the following command to convert the key-pair to pkcs12 format.

openssl pkcs12 -export -in server.crt -inkey server.key [-name tomcat] -out server.p12 -CAfile chain.pem -caname root -chain

Convert pkcs12 file to java keystore (jks):

keytool -importkeystore -deststorepass <password> -destkeypass <password> -destkeystore server.jks -srckeystore server.p12 -srcstoretype PKCS12 -srcstorepass <password> -alias <name>

Decode a certificate request or a x509 certificate:

openssl req -in server.csr -noout -text
openssl x509 -in server.crt -noout -text

Convert a PFX file to PEM-format (single file)…

openssl pkcs12 -in server.pfx -out key-n-certs.pem -nodes

Check certificate and connection using openssl…

openssl s_client -showcerts -connect f.q.d.n:1234
openssl s_client -starttls smtp -showcerts -connect f.q.d.n:25 -servername f.q.d.n

Convert certificate (PEM) to public key…

openssl x509 -inform pem -in certificate.cer -pubkey -noout > pubkey.pem

Add a (CA) certificate to the JAVA CACerts certificate truststore…

"C:\Java\jdk1.8.0_121\bin\keytool" -import -alias ADCERT-CA-1 -keystore "C:\Java\jdk1.8.0_121\jre\lib\security\cacerts" -trustcacerts -file ADCERT-CA-1.cer