Mongo DB - Document Database

Rédigé par Sozezzo - - Aucun commentaire

The big thing is … Mongo BD is NOT RDBMS!

In OOP are not tables and rows, objects use polymorphism, inheritance, and it is not uniform. Use objects with RDBMS can be hard.

Mongo DB has no schema to define, no tables and no relationships between collections of objects. Each document can be flat, simple, or complex as we wish. There are no locks of tables, or columns.

Durability vs Consistency

Durability is about whether your data was persisted to durable media before control was returned to you. Eventual consistency in a replica set has to do with whether a document was written to all of the servers, to the primaries and secondaries before control was returned to your application.

· You can ready for inconsistency. Can your application run with? Can it be an issue?

· The application takes control of schema enforcement.

We can backup and restore of Mongo, to import and export information, to monitor the Mongo server, and to share it. You can scale MongoDB horizontally with a replication.


What is BSON ? Store byte arrays on disk..

The BSON specifications can be found on Basically, BSON is elementary data types into memory into C data types then to read and to write is fast. BSON specification is data storage serialization format with memory mapped files.
BSON documents has length prefixing. It is fast to find particular a field into store arbitrary object structures and not require to adhere to a particular schema, and this is the key of MongoDB.

Index is important in MongoDb, you must be sure the index is useful and with the correct name of column.

Where and When do you use MobgoDB?

  • No transaction at all.
  • Not a lot of updates, less 1% of database.
  • Geo-distributed database;
  • Use for CMS, Logs, Dataware, etc.

Install PHP on Windows Server 2016

Rédigé par Sozezzo - - Aucun commentaire

Easy steps to install PHP on Windows Server 2016

This tutorial was create for Windows Server 2016 x64, but you can do it on Windows Workstation x86, but you just need to use x86 everywhere.



  • Windows Server 2016
  • IIS
  • PHP bin files
  • WinCache
  • VC++



IIS service with CGI extension.

Before to install, verify if you already have it.

Get-WindowsFeature web-cgi



Powershell command to install

Install-WindowsFeature -name web-server,web-cgi –IncludeManagementTools


Installing PHP


Download PHP version you want to install.

Extract file to C:\Program Files\php


Select PHP configuration to use

You have 2 configuration files:

  • php.ini-development
  • php.ini-production

You must rename the file configuration to php.ini.


Add Wincache configuration

Open php.ini file and add this line in the end:



Add PHP path on Environment variables

On Explorer click properties on This PC

Click on Advanced system settings

Click on Advanced \ Environment Variables

On System variables, edit variable : Path

Add new path. ( C:\Program Files\php )


Install WinCache Extention

Download and extract it.

Copy files to folder : C:\Program Files\php\ext

  • php_wincache.dll
  • php_wincache.pdb       (optional)



Configurate PHP in IIS

In IIS open Handler Mappings

Add Module Mapping

  • Request path : *.php
  • Module : FastCgiModule
  • Executable (optional) : C:\Program Files\php\php-cgi.exe
  • Name : PHP

Click on Request Restriction…

Select "FIle or Folder"


Installing VC++

Just install it.


Verify if it works.

Open the default web site path, root, and create a file <phpinfo.php> with this line:

<?php  phpinfo(); ?>


Usually it is this path: C:\inetpub\wwwroot


Open in your local server this page:


MySql Reset root password

Rédigé par Sozezzo - - Aucun commentaire
Step by step how you can change 'root' password
You must have rights to stop/start services, and kill process.
Step 1: Stop MySql service
run: services.msc
Step 2 : Create file to reset password
You can use your password and your file name.
We use password : Pass1Word2
and file name : C:\temp\reset.txt
USE mysql;
UPDATE mysql.user SET Password=PASSWORD('Pass!Word@') WHERE User='root';
Step 3 : Find our my.ini file
It is usually locate on 'C:\ProgramData\MySQL\MySQL Server ????\my.ini'
But, you can try to find using :
C:\ dir my.ini /s /b
You can check MySQL service's property.
For this sample, we use this:
C:\ProgramData\MySQL\MySQL Server 5.5\my.ini
Step 4 : Navigate to MySql bin directory
Open command prompt there.
Type command :
mysqld --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 5.5\\my.ini" --init-file=C:\temp\reset.txt
Attention : Use double "\\" insted "\"
Step 5 : Open "Task Manager" and kill process "mysqld.exe"
Step 6: Start service "MySql"
By now, we have a new password.
@echo off
echo * * * Reset MySql password * * *
echo Stop MySql Service
sc stop MySql
echo Kill mysqld.exe
taskkill -f -im mysqld.exe
rem dir "C:\ProgramData\MySQL\MySQL Server 5.5\my.ini"
echo Run MySqlD
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld"   --defaults-file="C:\Program Files\MySQL\MySQL Server 5.5\my.ini" --init-file=C:\Temp\reset.txt
echo Start MySql Service
sc start MySql
echo Try to connect
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe" "--defaults-file=C:\Program Files\MySQL\MySQL Server 5.5\my.ini" "-uroot" "-p"
** Attention : Make sure all file are ANSI

Simple log connections SQLServer 2000

Rédigé par Sozezzo - - Aucun commentaire

This code create a table to log all connections.

It is very simple, it just logs when, who, from where and which database.

For this we use “master.dbo.sysprocesses”

Create table to log on [Master] database.

 USE [master] GO CREATE TABLE [dbo].[dbaLogAccess] (     [dbaLogAccessId] [int] IDENTITY (1, 1) NOT NULL ,     [ConnectionDate] [datetime] NULL ,     [loginame] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,     [hostname] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,     [dbname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ) ON [PRIMARY] 

Script to add logs, you can run each minute to update the log. This script avoids to repeater, it only saves new connections. We recommend using a job and schedule each minute to run this script.

 INSERT INTO master.dbo.dbaLogAccess (ConnectionDate, loginame, hostname, dbname)   SELECT DISTINCT     tbNew.ConnectionDate,     tbNew.loginame,     tbNew.hostname,     tbNew.dbname   FROM (SELECT     CONVERT(char(10), GETDATE(), 126) AS ConnectionDate,     RTRIM(loginame) AS loginame,     hostname,     CASE       WHEN dbid = 0 THEN ''       WHEN dbid <> 0 THEN DB_NAME(dbid)     END AS dbname   FROM master.dbo.sysprocesses   WHERE (hostname <> '')) AS tbNew   LEFT OUTER JOIN master.dbo.dbaLogAccess AS tbOld     ON tbNew.ConnectionDate = tbOld.ConnectionDate     AND tbNew.loginame = tbOld.loginame     AND tbNew.hostname = tbOld.hostname     AND tbNew.dbname = tbOld.dbname   WHERE (tbOld.dbaLogAccessId IS NULL); 

This code limit connection by day, we can change by hour or by minute, you just change how many character you use it:
 print CONVERT(char(14), GETDATE(), 126) + '00:00'
print CONVERT(char(17), GETDATE(), 126) + '00'
print CONVERT(char(20), GETDATE(), 126)

Well, you may do not like to use [master] database, but it is the place to record the system-level information. Good news, you can easily change it.

or you can use :




SQL Server Sp_msforeachdb query character without limitation

Rédigé par Sozezzo - - Aucun commentaire

When we write a query than is running for every database in the instance of SQL Server, we use Sp_msforeachdb.
If your query is bigger than 2000 chars, the query cannot work because Sp_msforeachdb has a character limitation of 2000 chars.

The solution is to re-create MySp_myforeachdb. We use sp_helptext to see the definition of sp_MSforeachdb and sp_MSforeach_worker, and we change to a new character limitation of 262144 chars.

Everything works as before but without character limitation of 2000 chars.


USE master;
 * This is the worker proc for all of the "for each" type procs.  Its function is to read the
 * next replacement name from the cursor (which returns only a single name), plug it into the
 * replacement locations for the commands, and execute them.  It assumes the cursor "hCForEach***"
 * has already been opened by its caller.
 * worker_type is a parameter that indicates whether we call this for a database (1) or for a table (0)
create proc dbo.sp_Myforeach_worker
 @command1 nvarchar(max), @replacechar nchar(1) = N'?', @command2 nvarchar(max) = null, @command3 nvarchar(max) = null, @worker_type int =1

 create table #qtemp ( /* Temp command storage */
  qnum    int    NOT NULL,
  qchar    nvarchar(max) COLLATE database_default NULL

 set nocount on
 declare @name nvarchar(517), @namelen int, @q1 nvarchar(max), @q2 nvarchar(max)
   declare @q3 nvarchar(max), @q4 nvarchar(max), @q5 nvarchar(max)
 declare @q6 nvarchar(max), @q7 nvarchar(max), @q8 nvarchar(max), @q9 nvarchar(max), @q10 nvarchar(max)
 declare @cmd nvarchar(max), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd nvarchar(max)
   declare @namesave nvarchar(517), @nametmp nvarchar(517), @nametmp2 nvarchar(258)

 declare @local_cursor cursor
 if @worker_type=1 
  set @local_cursor = hCForEachDatabase
  set @local_cursor = hCForEachTable
 open @local_cursor
 fetch @local_cursor into @name

 /* Loop for each database */
 while (@@fetch_status >= 0) begin
  /* Initialize. */

      /* save the original dbname */
      select @namesave = @name
  select @useq = 1, @usecmd = 1, @cmd = @command1, @namelen = datalength(@name)
  while (@cmd is not null) begin  /* Generate @q* for exec() */
    * Parse each @commandX into a single executable batch.
    * Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we'll need to allow overflow.
    * We also may append @commandX's (signified by '++' as first letters of next @command).
   select @replacecharindex = charindex(@replacechar, @cmd)
   while (@replacecharindex <> 0) begin

            /* 7.0, if name contains ' character, and the name has been single quoted in command, double all of them in dbname */
            /* if the name has not been single quoted in command, do not doulbe them */
            /* if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname */
            select @name = @namesave
            select @namelen = datalength(@name)
            declare @tempindex int
            if (substring(@cmd, @replacecharindex - 1, 1) = N'''') begin
               /* if ? is inside of '', we need to double all the ' in name */
               select @name = REPLACE(@name, N'''', N'''''')
            end else if (substring(@cmd, @replacecharindex - 1, 1) = N'[') begin
               /* if ? is inside of [], we need to double all the ] in name */
               select @name = REPLACE(@name, N']', N']]')
            end else if ((@name LIKE N'%].%]') and (substring(@name, 1, 1) = N'[')) begin
               /* ? is NOT inside of [] nor '', and the name is in [owner].[name] format, handle it */
               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
               select @tempindex = charindex(N'].[', @name)
               select @nametmp  = substring(@name, 2, @tempindex-2 )
               select @nametmp2 = substring(@name, @tempindex+3, len(@name)-@tempindex-3 )
               select @nametmp  = REPLACE(@nametmp, N']', N']]')
               select @nametmp2 = REPLACE(@nametmp2, N']', N']]')
               select @name = N'[' + @nametmp + N'].[' + @nametmp2 + ']'
            end else if ((@name LIKE N'%]') and (substring(@name, 1, 1) = N'[')) begin
               /* ? is NOT inside of [] nor '', and the name is in [name] format, handle it */
       /* j.i.c., since we should not fall into this case */
               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
               select @nametmp = substring(@name, 2, len(@name)-2 )
               select @nametmp = REPLACE(@nametmp, N']', N']]')
               select @name = N'[' + @nametmp + N']'
            /* Get the new length */
            select @namelen = datalength(@name)

            /* start normal process */
    if (datalength(@cmd) + @namelen - 1 > 262144) begin
     /* Overflow; put preceding stuff into the temp table */
     if (@useq > 9) begin
      close @local_cursor
      if @worker_type=1 
       deallocate hCForEachDatabase
       deallocate hCForEachTable
      RAISERROR(55555, 16, 1); -- N'sp_MSforeach_worker assert failed:  command too long'
      return 1
     if (@replacecharindex < @namelen) begin
      /* If this happened close to beginning, make sure expansion has enough room. */
      /* In this case no trailing space can occur as the row ends with @name. */
      select @nextcmd = substring(@cmd, 1, @replacecharindex)
      select @cmd = substring(@cmd, @replacecharindex + 1, 262144)
      select @nextcmd = stuff(@nextcmd, @replacecharindex, 1, @name)
      select @replacecharindex = charindex(@replacechar, @cmd)
      insert #qtemp values (@useq, @nextcmd)
      select @useq = @useq + 1
     /* Move the string down and stuff() in-place. */
     /* Because varchar columns trim trailing spaces, we may need to prepend one to the following string. */
     /* In this case, the char to be replaced is moved over by one. */
     insert #qtemp values (@useq, substring(@cmd, 1, @replacecharindex - 1))
     if (substring(@cmd, @replacecharindex - 1, 1) = N' ') begin
      select @cmd = N' ' + substring(@cmd, @replacecharindex, 262144)
      select @replacecharindex = 2
     end else begin
      select @cmd = substring(@cmd, @replacecharindex, 262144)
      select @replacecharindex = 1
     select @useq = @useq + 1
    select @cmd = stuff(@cmd, @replacecharindex, 1, @name)
    select @replacecharindex = charindex(@replacechar, @cmd)

   /* Done replacing for current @cmd.  Get the next one and see if it's to be appended. */
   select @usecmd = @usecmd + 1
   select @nextcmd = case (@usecmd) when 2 then @command2 when 3 then @command3 else null end
   if (@nextcmd is not null and substring(@nextcmd, 1, 2) = N'++') begin
    insert #qtemp values (@useq, @cmd)
    select @cmd = substring(@nextcmd, 3, 262144), @useq = @useq + 1

   /* Now exec() the generated @q*, and see if we had more commands to exec().  Continue even if errors. */
   /* Null them first as the no-result-set case won't. */
   select @q1 = null, @q2 = null, @q3 = null, @q4 = null, @q5 = null, @q6 = null, @q7 = null, @q8 = null, @q9 = null, @q10 = null
   select @q1 = qchar from #qtemp where qnum = 1
   select @q2 = qchar from #qtemp where qnum = 2
   select @q3 = qchar from #qtemp where qnum = 3
   select @q4 = qchar from #qtemp where qnum = 4
   select @q5 = qchar from #qtemp where qnum = 5
   select @q6 = qchar from #qtemp where qnum = 6
   select @q7 = qchar from #qtemp where qnum = 7
   select @q8 = qchar from #qtemp where qnum = 8
   select @q9 = qchar from #qtemp where qnum = 9
   select @q10 = qchar from #qtemp where qnum = 10
   truncate table #qtemp
   exec (@q1 + @q2 + @q3 + @q4 + @q5 + @q6 + @q7 + @q8 + @q9 + @q10 + @cmd)
   select @cmd = @nextcmd, @useq = 1
  end /* while @cmd is not null, generating @q* for exec() */

  /* All commands done for this name.  Go to next one. */
  fetch @local_cursor into @name
 end /* while FETCH_SUCCESS */
 close @local_cursor
 if @worker_type=1 
  deallocate hCForEachDatabase
  deallocate hCForEachTable
 return 0


CREATE PROC sp_Myforeachdb
 @command1 nvarchar(max), @replacechar nchar(1) = N'?', @command2 nvarchar(max) = null, @command3 nvarchar(max) = null, 
 @precommand nvarchar(max) = null, @postcommand nvarchar(max) = null

 set deadlock_priority low 
 /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */ 
 /* @precommand and @postcommand may be used to force a single result set via a temp table. */ 
 /* Preprocessor won't replace within quotes so have to use str(). */ 
 declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12) 
 select @inaccessible = ltrim(str(convert(int, 0x03e0), 11)) 
 select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11)) 
 select @dbinaccessible = N'0x80000000'  /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */ 
 if (@precommand is not null) 
 declare @origdb nvarchar(128) 
 select @origdb = db_name() 
 /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */ 
   /* Create the select */ 
 exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' + 
   N' where (d.status & ' + @inaccessible + N' = 0)' + 
   N' and (DATABASEPROPERTYEX(, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess( = 1))' ) 
 declare @retval int 
 select @retval = @@error 
 if (@retval = 0) 
  exec @retval = dbo.sp_Myforeach_worker @command1, @replacechar, @command2, @command3, 1 
 if (@retval = 0 and @postcommand is not null) 
   declare @tempdb nvarchar(258) 
   SELECT @tempdb = REPLACE(@origdb, N']', N']]') 
   exec (N'use ' + N'[' + @tempdb + N']') 
 return @retval 

Fil RSS des articles de cette catégorie