SQL Server Instances are missing on the network servers.

Today I installed SQL Server in my environment. Everything well good SQL Server installed successfully.

I connected to the SQL Server and was able to login. So happily logged off and was resting happily.

Than thought of doing one more check just to be sure. This time I already had another server opened of the environment (both were in the same domain). I opened ssms and clicked on browse for more and than to network servers to find my newly installed SQL Server. But to my surprise it wasn’t there.

I logged on the server and opened SSMS and logged in successfully. I was thinking might be there’s some issue with the domain. Not going to lie I am a novice in domains and everything related to AD. I have made a promise to myself to learn it some time soon. I thought to check the network servers here and to my surprise there they are, every SQL Server Instance of the domain.

I have replicated the problem in my test environment.

I have three servers BASE2012, First and HADOMAINCONTROL and there are three instances on each.

When checking the network servers from First I see everything in the domain.

1

But when checking the network servers from the BASE2012.

2

As you can see all the instances installed on the server First are not showing here.

What is happening here is that Windows Firewall is “On” for the domain. Once the port number is added or firewall is switched OFF for the domain or SQL Server is added in the exception (which ever way suits you and your environment) the SQL Server instances running on server First will be visible.

In Windows firewall, or any other third party firewall we have to make the exception for SQL Server Management Studio exe file i.e. sqlservr.exe present in the Binn folder of the SQL Server installation directory(To be done for each instance). Or you can add the ports that are in use by the SQL Server(for each instance again). Remember if you are having Reporting Services and Analytical Services than you have to add exception for SSAS or SSRS also.

3

I have disabled the firewall here(the quick way out 🙂 ), and after doing that I can see the instances of FIRST.

4

 

 

Suspect Pages

As BOL “A Suspect table contains one row per page that failed with a minor 823 error or an 824 error. Pages are listed in this table because they are suspected of being bad, but they might actually be fine. When a suspect page is repaired, its status is updated in the event_types column.”

The different event_types are:

1 = An 823 error that causes a suspect page (such as a disk error) or an 824 error other than a bad checksum or a torn page (such as a bad page ID).

2 = Bad checksum.

3 = Torn page.

4 = Restored (page was restored after it was marked bad).

5 = Repaired (DBCC repaired the page).

7 = Deallocated by DBCC.

That is whenever SQL Server comes across a page that is having some problem it will be marked in the suspect_pages table of msdb database.

If you are not running or not being able to run DBCC CHECKDB on a scheduled basis than its better you keep a check on this table. At least you will you come to know that there are some pages that needs to restored or repaired.

To repair a SUSPECT_Page there is no other easier way than running DBCC CHECKDB. But all the pages can’t be repaired. So what we are left with is RESTORE.

We can do a Full database restore if you wish. But can also do a page level restore. Page level restore is good in case you have only one or two corrupt pages.

suspect1

Here I have created a corrupt page using DBCC WRITEPAGE.

When I try to run DBCC CHECKDB on it I get the error stating:

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 1765581328, index ID 1, partition ID 72057594045595648, alloc unit ID 72057594051297280 (type In-row data), page (1:2287). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.

Msg 8928, Level 16, State 1, Line 1

Object ID 1765581328, index ID 1, partition ID 72057594045595648, alloc unit ID 72057594051297280 (type In-row data): Page (1:2287) could not be processed.  See other errors for details.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 1765581328, index ID 1, partition ID 72057594045595648, alloc unit ID 72057594051297280 (type In-row data). Page (1:2287) was not seen in the scan although its parent (1:1075) and previous (1:2286) refer to it. Check any previous errors.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 1765581328, index ID 1, partition ID 72057594045595648, alloc unit ID 72057594051297280 (type In-row data). Page (1:2288) is missing a reference from previous page (1:2287). Possible chain linkage problem.

CHECKDB found 0 allocation errors and 4 consistency errors in table ‘Person.Person’ (object ID 1765581328).

CHECKDB found 0 allocation errors and 4 consistency errors in database ‘AdventureWorks2012’.

 repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureWorks2012).

Repair_allow_data_loss is the minimum level of repair possible. But that means we will lose data. The only way left is restoring the database or page.

suspect2

 

Configuring Log Shipping

Log shipping creates jobs for Backup, Copy and restore. We need to create folders for the Backup and Copy. Backup folder should be on the network and SQL Server Agent service accounts of both primary and secondary server should have access (r/w) to the folder. Copy folder can be either local to secondary or on network.

1. Create folder for backup either on network or create a local folder and share it.

1

2. Open folder properties.
2

3. Open Sharing tab. Click “Share”.

3

4. Add Service accounts of SQL Agent of primary and secondary server and Read/Write permissions. Click “Share”.

4

5. Now the folder is shared on the n/w and network address of the folder is \\WIN2\Log_Backups.

5

6. Create COPY folder. And add permissions for Service account of SQL Agent of secondary server.

6
7. Now open properties of the Database on log shipping is to be configured and click “Transaction Log shipping”.

7

There is nothing as of now we have to configure it.

8. Click on “Enable this as a primary database in a log shipping configuration”.

8

9. Click on “Backup Settings”.

9

10. In the “Network path to backup folder” give the network path of the backup folder we created earlier.

10

11. We can decide the retention period of the backup files. Here we have set the retention period as 10hours that means the transaction log backups older than that will be deleted automatically. We can also mention the time in which the alert will be generated if no backup occurs. Here we have set that time as 30 minutes.

11

12. The default Schedule of backup jobs is always 15 minutes. If you want to change that click on “Schedule”. Change the schedule as per the requirements of your environment.

12

13. After setting the schedule click “Ok”. And then again “OK ” to save the backup settings. Now we have configured the backup jobs settings.

13

14. Now we have to add the secondary server(s). Click on “Add”.

14

15. In the Secondary Database Settings click on “Connect” and connect to the secondary server.

15

16. Once you are connected (Here our secondary server is WIN2\INSTANCE3). It will ask for the secondary database name. If you have already initialized the Secondary database than choose that else provide the name of the Secondary database. By default its same as Primary database.

16

17. In the tab “Initialize Secondary Database” choose the appropriate option. There are three options. a) Let it create a backup and restore it. b) You already have a backup and can provide the location of the backup. c) You have already initialized the secondary database.

I haven’t that’s why chosen the “Yes, generate a full backup of the primary database and restore it into the secondary database (and create the secondary database if it doesn’t exist)”.

You can also provide the database data file and log file backup directory by clicking on “Restore Options..”

17

18. In the Copy Files tab provide the destination folder location (the one we created earlier). We also have the option of deleting the old log backup files. Here I have chosen it as 10 Hours. If you want to change the schedule of Copy job click on “Schedule” and change it according to your needs. I am happy with the 15 minutes so haven’t changed them.

18

19. Click on “Restore Transaction Log” tab.

19

20.Here we have the option to choose whether we want to keep the secondary server in “No recovery mode” i.e it will not accessible or “Standby” by so that it available but in read-only mode. I have chosen the “Standby mode”.

20

21. If you want some delay between your secondary and primary server than you can choose the delay that you wish. It is helpful in case someone has deleted some table from primary and your secondary is running with some delay than you can copy the table back to primary from secondary.

21

22. Change the Restore job name and schedule if you wish too. I haven’t made any changes to them. Click “OK” if everything is as per your need.

22

23. Now we have a secondary server.

23

24. If you want a monitor server instance than click on “Use monitor server instance”. And click “Settings”.

24

25.Connect to the Instance of the SQL Server where history of log shipping will be stored and jobs for alerts will run.

25

26. I have chosen the same instance as secondary server for monitoring.

26

27. Provide the service account for the jobs to connect on the monitor server instance. If the service account of SQL Agent of both secondary and primary have access rights to the monitor chose the “By impersonating…” else provide the Login which does have the access.

27

28. Everything is set click “OK”.

28

29. If everything goes well you will see all around Success.

29

30. Check whether the database is created on the Secondary server or not.

30

31. You can check the Server level standard report for Transaction Log shipping Status for verifying and monitoring later on.

31

Enabling\Disabling a Trace Flag

Trace Flags are used to switch On/Off a specific characteristic in SQL Server.

A trace can be either set for global scope or session scope depending upon your choice and requirement.

A Trace flag enabled on the sessional scope will remain enable for the time and scope of that very session only, which has enabled that trace flag.

A Trace flag enabled at the global scope will either remain enabled as long it is not disabled or till the SQL Server services are restarted, depending upon the way it has been enabled. And saying this we come to the question on “How to enable a Trace Flag?”.

Session Scope.

To enable the Trace flag on session scope we use DBCC TRACEON command.

When running DBCC PAGE command, it’s output is not visible until and unless you use the TRACE FLAG 3604/3605.

traceonsession

To see which Trace flags are open in the environment we use DBCC TRACESTATUS.

2tracestatus

The columns are self explanatory.

Status – 1 for enabled.

Global – 1 if the trace flag is enabled for the global level.

Session – 1 if the trace flag is enabled for the session level.

Here as we have set the trace flag 3604 for this session that’s why we are seeing 1 in session column for 3604.

Note: If we run the DBCC TRACESTATUS from different session we will not see the flag 3604, as being sessional its scope is limited to that session only (54 here).

3tracestatussessiondiff

Global Level

There can be a requirement when we need a trace flag enabled on the global level, i.e for every sessions. This can done two ways, first by using the DBCC TRACEON command and second by using the startup parameters.

The main difference between these two methods is that by using the command trace flag remains enabled until either someone explicitly disables the trace flag or the SQL Server services are restarted. But when using the startup parameter the trace flag gets automatically enabled whenever the SQL Server services are restarted. So if you want to have a trace flag enabled 24/7 without worrying about the restart of the services use the startup parameter.

To enable the trace flag using command we use DBCC TRACON but this time with ‘-1’. Here we are enabling the trace flag 3605.

4traceglobal

Note: This time if we run DBCC TRACESTATUS from different session we will see the trace flag 3605 as it’s global and every session can use the characteristics enabled by it.

5tracestatusglobal

As mentioned before the trace flag will remain enabled as long as someone doesn’t disable it or SQL Server services are not restarted.

Global Scope using Startup Parameter.

To enable a trace flag for the global scope and want it to remain open no matter how many times the SQL Server is restarted we use Startup Parameters of SQL Server.

The steps involved in putting a trace flag in startup parameter is:(Here we are enabling the trace flag 3226)

1. Open SQL Server configuration manager.

2. Right click on the SQL Server services. (The instance for which you want to enable the trace flag). And click on properties.

3. Goto Startup Parameters.

4. Enter “-T3226”. And Click Add. Apply

.startupparameter1      startupparameter2

5. Restart SQL Server Services.

Now when you’ll check the SQL Server log, you can see that SQL Server has enabled the trace flag.

startupparameter3

Checking the status of the trace flag.

startupparameter4

Disabling the Trace Flag

To disable the trace flag DBCC TRACEOFF is used.

Similar to TRACEON when we are disabling a trace flag off global scope we use ‘-1 ‘.

Here we will be disabling the trace flag 3605 which enabled at the global scope.

Checking the current status of trace flags in the env.

traceoff

Disbaling the trace flag using DBCC TRACEOFF

traceoff1

Checking the status of trace flags.

traceoff2

We can see that Trace flag 3605 is not showing in the status. Which means it is disabled.

Pages & Extents

PAGES:

As per BOL “The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.”

Pages are blocks of 8 KB space that are used by SQL Server for storing Data or Information of and about your database. This could be anything, it could be your table rows, indexes, images and so on.

When you create a Database of 1 GB. Then there will be 1*1024*10124/8 number of pages i.e a 1 GB database file has 131072 Pages. And when you create tables and insert rows than logically rows are seen in your tables but physically they are stored in these pages.

There are different types of pages depending upon what type of information or data is stored into them. They are:

Data

Data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON.

Index

Index entries.

Text/Image

Large object data types:

text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data

Variable length columns when the data row exceeds 8 KB:

varchar, nvarchar, varbinary, and sql_variant

Global Allocation Map, Shared Global Allocation Map

Information about whether extents are allocated or not, along with the type of extent.

Page Free Space

Information about page allocation and free space available on pages.

Index Allocation Map

Information about extents used by a table or index per allocation unit.

Bulk Changed Map

Information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.

Differential Changed Map

Information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.

EXTENTS:

As per BOL “Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.”

Extent 19th Aug

 

Extents is the way space is managed by SQL Server. Each extent is made of 8 contiguous pages and these pages may or may not be related to the same object.

There are two types of Extents Mixed and Uniform.

In a Mixed Extent all the 8 pages might be used by 2 or more different objects i.e in those 8 pages 2 might be used by object1, 3 by object2 and 2 by object3 and 1 might be used by SQL Server. (PFS,BCM..)

Whereas in a Uniform Extent all the 8 Pages are used by the same object.

Reference:

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

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-an-extent/

 

Finally !!!

Better late than never.

For the last 2-3 years I was thinking to create a blog of my own, but always something or the other comes. Job changes (3 to be precise in the last 3 years), getting married and than the birth of my lovely son “Manvik”. Telling the truth, I have no guilt of side lining to create a blog as I am more than happy to give all my free time to be with him and my beautiful wife. 🙂

So how did I get the time now when I am busy with them?? They are not at home as they have gone to celebrate the festival of Raksha Bandhan . ha ha ha..

Firstly I was thinking of creating a blog from wix or foursquare or ipage but after considering alot about all the other options I finally decided to go with WordPress. So after finalising WordPress the next question was to choose a hosting service provider. WordPress suggests to go with Bluehost and that’s what I did and bought the hosting from Bluehost.in and domain from Bigrock.

I bought the hosting and domain on 16th Aug 2016 but today 18th Aug I am writing my first blog. I chose this day as for a very specific reason. 3 years back on the same day i.e 18th Aug 2013. I  met my beautiful wife “Priyanka” 🙂 I Love you Priyanka and thanks for coming in my life.

Now about this blog, the purpose of this is basically that I have a really bad memory, I forget things easily and so it will be like my online notes for anything that I have learned and will learn in the future. And if  in the process someone visits the page and learn something new than it’ll be added benefit. 🙂