Monday, 17 February 2014

Microsoft: Running Multiple Instances of Microsoft SQL

In order for central management and saving the cost of SQL license, many database administrators like to setup multiple instances in one SQL server. Every instance should listen to an unique port. Thus, normally, the port setup of the instances would be port 1433 for one of the instances and the rest of the instances will be configured with dynamic port. However, for example, if you enable the Windows Firewall, dynamic port may not be a good idea.

To configure the instances to listen to a static port, please follow the steps below:
1.  In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.

2.  Delete the 0 at TCP Dynamic Ports.

3.  In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK.

4.  In the console pane, click SQL Server Services.

5.  In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.
 

6.  If you have enabled your Windows Firewall, you may want to create the rules to allow the ports. Please refer to the page below for the Windows Firewall configuration:

Microsoft: Microsoft SQL Network Error 26

Error Message:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Solution:
1.  Determine the port that your SQL server is listening by opening CMD in your SQL server ( Run as administrator ) and running the following command to obtain the connecting port:
netstat /abo >>c:\output.txt
* Note: The default port should be 1433

2.  In your SQL server, go to Control Panel >> Windows Firewall and click on Advanced Settings on the left pane. You should see the Windows Firewall with Advanced Security.

3.  Select the Inbound Rules on the left pane and click on New Rule... on the right pane.
4.  This opens the New Inbound Rule Wizard which you can use to allow inbound traffic on Port 1433 for TCP/IP. Just follow the steps outlined below for the configuration.

5.  Create exception of sqlbrowser.exe. Sqlbrowser service needs to be enabled for named instance. Windows Firewall may prevent sqlbrowser.exe to execute. So, it is imperative to add exception for the same in Windows Firewall. Firstly, search for sqlbrowser.exe on your local drive where SQL Server is installed. Copy the path of the sqlbrowser.exe ( * Note: C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe ) and create the exception of the file in Firewall, as delineated in Step 4 except this time please select Program rather than Port.

That’s it, success! From here you should be able to access your SQL Server remotely.


References:
1.   How to enable remote connections in SQL Server 2008?
http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

2.  How to determine and change the port of an SSAS Instance
http://support.microsoft.com/kb/2466860

3.  SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )
http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

Wednesday, 12 February 2014

Microsoft: Verify Successful Replication to a Domain Controller

To verify successful replication to a domain controller, please follow the steps below:
  1. Open a Command Prompt as an administrator: On the Start menu, right-click Command Prompt, and then click Run as administrator. If the User Account Control dialog box appears, provide Domain Admins credentials, if required, and then click Continue.
     
  2. At the command prompt, type the following command, and then press ENTER:
          repadmin /showrepl <servername> /u:<domainname>\<username> /pw:<password>


Reference:
Verify Successful Replication to a Domain Controller
http://technet.microsoft.com/en-us/library/cc794749%28v=ws.10%29.aspx

Microsoft: Flexible Single Master Operations ( FSMO ) / Operations Master

There are five operations master roles managing the single-master operations in Active Directory domain service.

Two operations master roles exist in each forest:
  • Schema master - govern all changes to the schema
  • Domain naming master - add and remove domains to and from the forest
In addition to the two forestwide operations master roles, three operations master roles exist in each domain:
  • Primary domain controller (PDC) emulator - process all replication requests from Microsoft Windows NT 4.0 backup domain controllers and processes all password updates for clients that are not running Active Directory–enabled client software
  • Relative identifier (RID) master - allocate RIDs to all domain controllers to ensure that all security principals have a unique identifier
  • Infrastructure master - maintain a list of the security principals from other domains that are members of groups within its domain
By default AD assigns all operations master roles to the first DC created in a forest. If new domains are created in the forest, the first DC in a new domain holds all of the domain-wide FSMO roles. This is not a satisfactory position. Microsoft recommends the careful division of FSMO roles, with standby DCs ready to take over each role. When a FSMO role is transferred to a different DC, the original FSMO holder and the new FSMO holder communicate to ensure no data is lost during the transfer. If the original FSMO holder experienced an unrecoverable failure, you can force another DC to seize the lost roles; however, there is a risk of data loss because of the lack of communications. If you seize a FSMO role instead of transferring the role, that domain controller can never be allowed to host that FSMO role again, except for the PDC emulator Master operation and the Infrastructure Master Operation. Corruption can occur within Active Directory. FSMO roles can be easily moved between DCs using the AD snap-ins to the MMC or using ntdsutil which is a command line based tool.

Certain FSMO roles depend on the DC being a Global Catalog (GC) server as well. For example, the Infrastructure Master role must not be housed on a domain controller which also houses a copy of the global catalog in a multi-domain forest (unless all domain controllers in the domain are also global catalog servers), while the Domain Naming Master role should be housed on a DC which is also a GC. When a Forest is initially created, the first Domain Controller is a Global Catalog server by default. The Global Catalog provides several functions. The GC stores object data information, manages queries of these data objects and their attributes as well as provides data to allow network logon.

Certain domain and enterprise-wide operations that are not well suited to multi-master updates must be performed on a single domain controller in the domain or in the forest. The purpose of having a single-master owner is to define a well-known target for critical operations and to prevent the introduction of conflicts or latency that could be created by multi-master updates. Having a single-operation master means that the relevant FSMO role owner must be online, discoverable, and available on the network by computers that have to perform FSMO-dependent operations.

The PDC emulator and the RID master should be on the same DC, if possible. The Schema Master and Domain Naming Master should also be on the same DC. To provide fault tolerance, there should be at least 2 domain controllers available within each domain of the Forest. Furthermore, the Infrastructure Master role holder should not also be a Global Catalog Server, as the combination of these two roles on the same host will cause unexpected (and potentially damaging) behaviour in a multi-domain environment.


References:
1.  How Operations Masters Work
http://technet.microsoft.com/en-us/library/cc780487%28v=ws.10%29.aspx

2.  Flexible single master operation
http://en.wikipedia.org/wiki/Flexible_single_master_operation

3.  FSMO placement and optimization on Active Directory domain controllers
http://support.microsoft.com/kb/223346

Microsoft: Time Configuration in Microsoft Active Directory

Let's see how time should be configured in Active Directory:
  • In Active Directory, we use the Windows Time service for clock synchronization: W32Time
  • All member machines synchronize with any domain controller
  • In a domain, all domain controllers synchronize from the PDC Emulator of that domain: using NT5DS (which simply means: follow the domain hierarchy and get you your PDC emulator)
    The PDC Emulator of a domain should synchronize with any domain controller of the parent domain: using NTP
  • The PDC Emulator of the root domain in a forest should synchronize with an external time server (could be clock device, a router, another standalone server, an internet time server…)
You can configure the time by using W32TM. Here are the steps for you to configure the time of your domain controller / PDC.
1a.  Run the following command on the PDC emulator: 
       w32tm /config /manualpeerlist:timeserver /syncfromflags:manual /reliable:yes /update
       (where timeserver is a –space delimited– list of your time source servers)

1b.  Once done, restart W32Time service by running the following commands:
       net stop w32time
       net start w32time
  
2a. To point the domain controllers to the PDC, run the following command on all other DCs (that
      are not PDC): 
      w32tm /config /syncfromflags:domhier /update

2b.  Once done, restart W32Time service by running the following commands:
       net stop w32time
       net start w32time

To check the source time server, please run the following commands:
w32tm /query /status


Reference:
“It’s Simple!” – Time Configuration in Active Directory
http://blogs.technet.com/b/nepapfe/archive/2013/03/01/it-s-simple-time-configuration-in-active-directory.aspx

Tuesday, 11 February 2014

Microsoft: Lost All Messages, Calendars and Contacts in Outlook 2011 for Mac

If you have lost all the messages, calendars and contacts in your Outlook 2011, here are the steps that you can recover them:
1.  Make sure you have quit your Outlook 2011
2.  Go to Documents/Microsoft User Data/Office 2011 Identities in the MacBook and Time Machine ( I assume that you have turned on Time Machine to backup your MacBook )
3.  Replace the Main Identity file from the MacBook with the file from the Time Machine
4.  Launch Outlook 2011
5.  A box will prompt up and click on Rebuild. It may take a while to rebuild the database which depends on the database size
6.  Once the database is rebuilt, you should be able to view your messages, calendars and contacts

Monday, 27 January 2014

Microsoft: Delete Files after a Certain Number of Days

You may delete the file(s)  after a certain number of days with the ForFiles command.
forfiles -p "C:\what\ever" -s -m *.* /D -<number of days> /C "cmd /c del @path"

FORFILES.exe (Native command in Vista/Windows7/2008, via Resource Kit for XP)
Select a file (or set of files) and execute a command on each file. Batch processing.

Syntax
      FORFILES [/p Path] [/m SrchMask] [/s] [/c Command] [/d [+ | -] {date | dd}]

Key
  • /p Path: The Path to search  (default=current folder)
  • /m SrchMask: Select files matching the specified search mask (default = *.*)
  • /s: Recurse into sub-folders
  • /C command: The command to execute for each file. Wrap the command string in double quotes. Default = "cmd /c echo @file" The Command variables listed below can also be used in the command string.
  • /D date: Select files with a last modified date greater than or equal to (+), or less than or equal to (-), the specified date, using the region specific date format typically "MM/DD/yyyy" or "DD/MM/yyyy"
  • /D + dd: Select files with a last modified date greater than or equal to the current date plus "dd" days. (in the future)
  • /D - dd: Select files with a last modified date less than or equal to the current date minus "dd" days. (in the past) A valid "dd" number of days can be any number in the range of 0 to 32768.   (89 years) "+" is taken as default sign if not specified.
   Command Variables:
  • @file: The name of the file
  • @fname: The file name without extension            
  • @ext: Only the extension of the file            
  • @path: Full path of the file
  • @relpath: Relative path of the file      
  • @isdir: Returns "TRUE" if a file type is a directory and "FALSE" for files                
  • @fsize: Size of the file in bytes
  • @fdate: Last modified date of the file
  • @ftime: Last modified time of the file
To include special characters in the command line, use the hex code for the character in 0xHH format (ex. 0x09 is theTAB character, 0x22 is the double quote " character.) so "C:\Program Files\" becomes ^0x22C:\Program^ Files\^0x22

Internal CMD.exe commands must be preceded with cmd /c

If ForFiles finds one or more matches if will return %errorlevel% =0
If ForFiles finds no matches if will return %errorlevel% =1 and will print "ERROR: No files found with the specified search criteria."

Early versions of FORFILES were supplied in the NT resource kits and used unix style '-' parameters, (still supported for backwards compatibility) also the /D option accepted dates only in DDMMYYYYHHMN format.

Last modified dates set in the future are not common but can happen when your computer clock date/time is changed e.g. due to daylight savings time.


Reference:
FORFILES.exe
http://ss64.com/nt/forfiles.html