SQL Replication

-- to get what are the errors that are in the server
select  * from distribution..MSrepl_errors with(nolock) where time > '20120221' order by time desc
select  top 10 * from distribution..MSrepl_errors with(nolock)  order by time desc
---------------------------------------------------------------------------------

-- to get what was the command that was cauing the issue. From here we get the hint what is the table that is involved.
exec distribution..sp_browsereplcmds @xact_seqno_start='0x0001492D0000299B000600000000', @xact_seqno_end='0x0001492D0000299B000600000000'

{CALL [sp_MSdel_dbot_server_status] (53966)}

msdb table  which has replication issues
-------
select * from sysreplicationalerts
select distinct subscriber,article from sysreplicationalerts
select distinct publisher,publisher_db,article from sysreplicationalerts
select distinct 'select count(*) '+''''+article+''''+'from ' +publisher_db + '..'+ article + ' with (nolock)' from sysreplicationalerts



-- to run a job from command line.
EXEC msdb.dbo.sp_start_job N'FESCOVAWSA04W3-Aarow_acp-aarow_acp-11' ;
GO

--Adding transactional article in an existing publication. Here a lot of parameters are environment specific and t_aaflc_fund_fmly_lkup
--is the table that is being added to the existing publishing.
use [Aarow_acp]
exec sp_addarticle @publication = N'aarow_acp',
@article = N't_aaflc_fund_fmly_lkup',
@source_owner = N'dbo',
@source_object = N't_aaflc_fund_fmly_lkup',
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@destination_table = N't_aaflc_fund_fmly_lkup',
@destination_owner = N'dbo',
@status = 24,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_dbot_aaflc_fund_fmly_lkup]',
@del_cmd = N'CALL [sp_MSdel_dbot_aaflc_fund_fmly_lkup]',
@upd_cmd = N'SCALL [sp_MSupd_dbot_aaflc_fund_fmly_lkup]'
GO


-- to compare data on row basis. using -c to compare table data on column basis.
tablediff -sourceserver PWIBOSSQLA10V3 -sourcedatabase RSOWDistribution2 -sourcetable t_server_status -destinationserver PWIBOSSQLA10V3 -destinationdatabase RSOWDistribution1 -destinationtable t_server_status -q -o


select log_reuse_wait_stats,name from sys.databases --- check if replication appears in the description

distribution.dbo.msrepl_commands

----------------------------
to find the ditributor/subscriber/publisher detail

select article,publisher_db from distribution..msarticles
select * from distribution..MSpublisher_databases
select * from distribution..MSsync_states
select * from distribution..MSsubscriber_info

IHsubscriptions
MSsubscriber_info

http://saveadba.blogspot.com/2011/08/adding-new-article-without-generating.html


Thanks,
Tushar Kanti

Identify the Database Server which is used to mount the database in Sharepoint 2010

Hi Guys,
Presently working in 2nd largest Sharepoint Environment globally. I have been working with a lot of Sharepoint Enironments with different levels of complexities. Well Today I will talk about the a couple of important tables in the Config database related to Sharepoint.
As you know the config database houses a lot of important tables. These tables have a lot of information which can be very crucial in understanding the links between a lot of internals inside sharepoint. Today we will take a peek into the Objects, Class and the SiteMap tables in the COnfig database. I definitely do miss the documentation on these tables. However there were little bread crums over the internet which I could follow and reach to this juncture.
The article is divided into two parts in the first part we will look  into the very basics of queries and in the next section we will look into the complex query merging a couple of the basic queries.

PART 1

a.SiteMap Table will have most of the information required for the Sharepoint to maintain a SiteCollection. ApplicationId,DatabaseId,Status,Version,HostHeaderIsSiteName, SubscriptionId,etc colums are there which are very useful in SiteMap table.

b.Objects Table will have the entries for most the objects that are related to config may be the Server Names,Databases,etc information.  Id,ClassId, ParentId , Name ,Status ,Version etc are colmns which are very useful in Objects table.

c.CLasses Table will have the Id,BaseClassId and FullName columns which will be useful to get basic information.

To Identify The Databases mounted in the Farm:

Query1 -> Select distinct DatabaseID from SiteMap

This Query1 will give us the databaseid's mounted for the whole farm sharing the config database.

Query2 -> Select distinct Name as DBName , DatabaseId,ParentId

from SiteMap s join Objects o on s.DatabaseID=o.ID

This Query2 will give us  the database names for the Farm that are presently mounted.

These two queries are good to start with the inquition of database information inside Sharepoint Config database.

PART2

Now lets move to the next section of this post where we will discuss other details about the database servers that are being used in Sharepoint to Mount the databases.

Query 3 -> select distinct O.Name As DatabaseName, servers.Name as ServerName

from SiteMap s

inner join Objects o on o.id= s.databaseid

inner join Objects Parent on Parent.Id= o.ParentId

inner join Objects servers on servers.Id= Parent.ParentId

This query will hunt for the parentid to which the database is belonging and then hunt for the child for the Servers and joining them we identify the Databse Servers for our Databases. Here we are self joining the Objects table to get all the information.

Thanks,

Tushar Kanti



DBA for your Data

Hi,
What is the most important resource in the planet?
 As the title might have already hinted it is "The Data". Data is the New Oil. It is the costliest resource known to humans. There are so many things which mention about the importance of Data.
But I am not here to pile on with the same information. I am here to tell you something more important.
The Guardian of your Data is Database Administrator also known as DBA. But more than often people in IT ignore this profile and grant the role to either a Windows Admin or a SQL Developer.
Imagine handing over Windows Administration to SQL Developer and SQL Developer role to Windows Administrator. Right it gives you a kick immediately. You are ready to give a knee jerk reaction. But no one understands the Database like your Friendly Neighborhood DBA.

The basic problem of IT Management hiring a DBA is giving the responsiblity to HR (who has no clue what SQL Server, MYSSQL or Oracle and let's not get started on cosmos, mongo, dynamodb). I recently read an article where a company HR posted a 12 years experience for Kubernetes. Seems like Google were 6 years too slow. The next problem is job interviews unless you have a DBA who is taking your interview (I am lucky to have some amazing interviews with very senior DBA's) you have no clue what answer the interviewer is looking for. Most of the times they will bring up very specific questions with minimal details like the  tempdb is full. Well there is no right answer and it is more of a approach the problem answer rather than a bullet point this is how it is done.

Now let's say the poor DBA still manages to siphon through the HR's list of desirable candidates and even manages to convince the non-DBA techies(no offence I love Windows Admins and SQL Developers) to impress. The company does not find much value in hiring a DBA for a single project and hence asks the person to be shared with multiple projects and the DBA loves to have more no problem there. The actual problem is no one is really ready for any change the DBA wants to do to improve security, performance or profitability. So after some time the organization finding no value in the work done asks to turn roles to a SQL Developer so they can justify the time and money spent on a DBA.

So you see the person looking after the most important resource is not even close to what he/she should have achieved. That said it is not the story of every DBA and IT of late has started to get really respectful with Data and there are even very specific and specialized profiles now related to Data like Data Scientist ,Machine Learning Engineer and so on. In the core all these profiles have a DBA in them but they specialize in specific discipline.

So there it is my view about the DBA for your Data.

Thanks,
Tushar Kanti

Looking for password to your router in windows 10

Hi,

How often have you forgotten the password to your router. I know it is not save to keep in a text file. So what I do is to keep them in keypass  You can use keypass to generate and store passswords for your home/organization. Well now coming back to today's topic. How to view the old password stored in Windows10. 

Now for checking the existing password for your router to which you are connected. As in you need to connect to that network router. 

For powerusers -> you can type ncpa.cpl and go to step for wifi adapter settings directly. 

Once done click on the wifi icon again in the tool bar.  

Then go to the network and internet settings option. 

Then choose the wifi in the left hand side of the screen. 

Then go to the right hand side of the screen and choose Change Adapter Options. 

Then go to the wifi adapter setting and click on status. 

Then click on the wireless properties. 

Then go to the security tab and click on show characters. 

There you go Bam.. you have the password you were looking for. 

Remember this only works in Windows 10 and not in Mac or Linux. You need to be careful while changing passwords else you will have to reset the router which will drain all your settings. 

Thanks,

Tushar