Databases not backed in a server

--This query will let you know the databases not backed up in a server



SELECT MAX(ISNULL(DATEDIFF(dd,ISNULL(b.backup_start_date, '01/01/1900'),GETDATE()),0)) AS 'NumDays'
, d.name as 'DBName'
FROM master..sysdatabases d
LEFT JOIN msdb..backupset b ON d.name = b.database_name
AND b.backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb..backupset b2
WHERE b.database_name = b2.database_name AND b2.type IN ('D','I'))
WHERE d.name != 'tempdb'
--AND d.name NOT IN (SELECT db_name FROM dbautility.dbo.db_exclude)
AND DATABASEPROPERTYEX(d.name, 'Status') = 'ONLINE'
GROUP BY d.name, b.type, b.backup_size
HAVING MAX(ISNULL(DATEDIFF(dd,ISNULL(b.backup_start_date, '01/01/1900'),GETDATE()),0)) > 1

SSAS-Calculation member error

When accessing calculation tab in Business Intelligence Development Studio (BIDS) I am getting error "Unexpected error occurred:Error in application".

Fix / Workaround/Solution

Most likely this error is caused by mismatched SSAS dlls: msmdlocal.dll and msmgdsrv.dll. To fix this move this 2 dlls from folder:

c:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\
To folder:
c:\Program Files\Common Files\System\Ole DB\

Note: Before replacing any dll make sure you backup them to safe location.

This solution worked for me.

Converting DateTime to Date

Converting Datetime to date in a table

Updating all the bookdate in a table from datatime to date
For example tablename = t1

update t1 bookdate = convert(varchar(10),bookdate,101)

converting datetime to date

convert(varchar(10),getdate(),101)

Installing SQL Server 2005

Installing SQL Server 2005 standard edition and windows 2008

Please make sure all the pre-requisties are installed.

1. The important componnet IIS Feature to be installed, with all the relevent components.click on start --- server manager ---- roles --- add roles.if IIS is intalled and to need the required components please click on Web services IIS --- go to Role services --- add roles.Make sure the follow compnents are added.

• Web Management Tools
• IIS 6 Management Compatibility
• IIS 6 WMI Compatibility IIS Metabase and
• IIS 6 configuration compatibility
• World Wide Web Services
• Application Development .NET Extensibility
• ASP.NET
• ISAPI Extensions
• ISAPI Filters Common
• HTTP Features
• Default Document Directory Browsing
• HTTP Redirection Static Content Security Windows Authentication
  

2. Now insert the CD and click on setup if autorun does not run.

3. Please clikc next and go through the normal installation.

4. When you get the window system configuration check make sure there is no warning message.

5. If there is message on IIS do not continue make sure the IIS component is completed or if there is warning message on Edition change, note there is a existing sql 2005 installed on this system.

6. To install another version start the cd from the command prompt. type in D:\setup.exe SKUUPGRADE=1

7. Now when there is no warning message click next, select the components you want to install.

8. Enter the instance name for example : MSSQL1

9. Now use the domain account to set up. for example username : vijay password : vijay domain : DOM

10. Select the authentication mode windows mode or mixed mode. This can be changed to mixed mode later as well.

11. Make all the selected components are installed successfully.

To check the installed components, let us first check sql server services.

1. Open sql management studio and select database services and enter username and password based on your authentication selection either windows or sql authentication.

2. When connected you have successful sql database services installed.

3. If not click on sql server configuration manager and start the sql server service.

To check Analysis services

1. Open sql management studio and select analysis services, this is opened through windows authentication.

2. When connected you have successful analysis services installed.

3. If not click on sql server configuration manager and start the analysis services.

To check Reporting services
1. Open sql management studio and select reporting services, this is opened through windows authentication.

2. When connected you have successful reporting services installed.
3. If not click on sql server configuration manager and start the reporting services.

4. Click on Reporting services configureation make sure the following is on.

• Server status --- running
• Report virtual directory --- name : reportserver --- website : default website
• Report manager virtual directory --- name : reports --- website : default website
• Windows services Identity --- service name : reportserver$MSSQL1 --- service account : localsystem builtin account : local system please note in this example MSQL1 is the sql instance name
• Web services Identity --- asp.net service account : NT Authority\NetworkService --- report server: reportserver$MSSQL1 --- report manager : reportserver$MSSQL1
• Database setup ---- server name : local(MSSQL1) --- database name : reportserver --- credential type : windows credentials --- account name ---- DOM\vijay ( please note the user name you had given earlier for installation)
• email setting : sender name : emailid (or any other company email id) --- current delivery method --- user smtp server ---- smtp server ---- enter the ip address of smtp server.

If there is still error on the reporting services

Fix / workaround solution

please go to C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager

open the RSWebapplicaton file on the notepad and check the following.

1.ReportServerUrl>

2.reportServerVirtualDirectory>ReportServer ( this is the name on the virtual directory of the report server configuration.

3. FullTrust

DATABASE MIRRORING - SQL 2005

Database Mirroring

Let us do database mirroring between two servers. In this example we are using sql 2005 standard edition with service pack 2.

I.Please note the database mirroring can also be done on the same server with multiple instances.

I. Before you get started with database mirroring please make sure the following are checked.
1. The principal and mirror server are either standard or enterprise edition. Please note we can also use the witness server for automatic failover.
2. The principal and mirror server are on the same service pack.(Recommended service pack 2). It is available for download from Microsoft. Please use the appropriate version based on 64 bit edition, 32 bit edition or 32 ia edition.
3. Please make sure there is sufficient disk space available on both the servers principal and mirror server.
4. The principal server should be on FULL RECOVERY option. This can be set by right click on the database on the principal server --- click properties --- click options --- set to FULL RECOVERY.
5. Make sure the SQL SERVER CONFIGURATION --- click on SQL SERVER instance --- properties --- check if this in Built in account or This Account makes sure the same is applicable for the mirror server.
6. Make sure you connectivity to the mirror server from the principal server. i.e., you can connect the mirror server from the principal server, in the sql server management studio.
The following sql script can be used to identify the version and service pack.
--to identify the version of sql
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

II. After check the above now we are ready to create the end points.
-- to create endpoint on principal server
Create endpoint [endpoint1mirroring]
state = started
as tcp (listener_port = 1430, Listener_ip = all)
for data_mirroring (role = partner, authentication = windows negotiate,
encryption = required algorithm rc4)

-- to create end point on mirror server
create endpoint [endpoint1mirroring]
state = started
as tcp (listener_port = 1440, Listener_ip = all)
for data_mirroring (role = partner, authentication = windows negotiate,
encryption = required algorithm rc4)


-- to create end point on witness server we do not have it in this example
create endpoint [endpoint1mirroring]
state = started
as tcp (listener_port = 1450, Listener_ip = all)
for data_mirroring (role = partner, authentication = windows negotiate,
encryption = required algorithm rc4)

--to check the endpoints
select name,type_desc,port,ip_address from sys.tcp_endpoints
select name,role_desc,state_Desc from sys.database_mirroring_endpoints



--granting permissions this is optional from principle server or instance
grant connect on endpoint ::mirroring to [servername\vijay];

---from mirror server or instance this is optional
grant connect on endpoint : : endpoint4mirroring to [servername\vijay];

---from witness server or instance this is optional
grant connect on endpoint : : endpoint4mirroring to [servername\vijay];

III. Now on the principal server we need to the backup.

--backup database
backup database [databasename] to disk = N'E:\Transfer\db1.bak' with format

--backup log
backup log [databasename] to disk = N'E:\Transfer\db1log.bak' with format

IV. Now on the mirror server we need to the restore.

--restore database
restore database databasename
from disk = 'c:\transfer\db1.bak'
with norecovery,
move 'db1_Data' to 'E:\db1_Data.mdf',
move 'db1_Log' to 'F:\db1_Data.ldf',

please note the restore of the log will also have to be with NO RECOVERY option

--restore database
restore database databasename
from disk = 'c:\transfer\db1log.bak'
with norecovery,
move 'db1_Data' to 'E:\db1_Data.mdf',
move 'db1_Log' to 'F:\db1_Data.ldf',



V. Now we have created the endpoints. We can now use the mirroring wizard to create mirroring.

Right Click on the principal database --- click properties --- click mirroring – click on configure security --- say no on witness server option (reminder this example is without witness server) – so check No option – click next --- click on the principal and mirror server and click next ---
The principal server is identified with ip or name with default port number --- click next --- select the mirror server ip --- connection as domain\username --- ( please note there should be no warning message on the screen) --- click next ---- service accounts can be left blank or provide the domain\username ---- click finish --- Start Mirroring.


This principal database will show as databasename (Principal, synchronized)

The mirror database will show as databasename (Mirror,synchronized,restoring) --- please note this database cannot be used at this state.

To check if the database mirroring is working click on the principal server database ---- right click properties --- click mirroring --- click failover.

SQL Error 1418 - DB Mirroring

Error: 1418 – Microsoft SQL Server – The server network address can not be reached or does not exist. Check the network address name and reissue the command

Fix/Work Around / Solution

1. Make sure the principal and mirror server are standard or enterprise edition.

2. Make sure both have the same service packs ( recommended).

3. Make the firewall on the server are not blocking the port. To check this you can use telnet. Telnet from command prompt as c:/telnet servername portnumber --- this will return blank screen when success else will return message.

4. Make sure the account of MSSQL Server is same in both the servers. i.e., under sql server configuration manager --- click sql server 2005 services --- select sql server ---- right click properties --- built in account or this account. Make sure both have the same.

5. Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network ConfigurationEnable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.

6. Make sure the principal database is backed up and restored in mirror server with norecovery option.

7. Make sure the principal datbase log is backed up and restored in morror server with norecovery option.

Reporting Services ---- error - No report servers are found on the specified system.

The follow error occurs when you try to configure reporting services.

Error - No report servers are found on the specified system.

Check the following

1. Reporting services is installed.
2. If installed make sure the services is started in --- start --- administrative tools --- services
3. If reporting services is not installed possible readon IIS is not installed or installed partially not all components.

For Reporting Services, the following IIS components should be installed:
Web Management Tools
IIS 6 Management Compatibility
IIS 6 WMI Compatibility
IIS Metabase and IIS 6 configuration compatibility
World Wide Web Services
Application Development
.NET Extensibility
ASP.NET
ISAPI Extensions
ISAPI Filters
Common HTTP Features
Default Document
Directory Browsing
HTTP Redirection
Static Content
Security
Windows Authentication

Make sure the above IIS componenents are installed.

For windows server 2008 --- click start --- server manager --Roles --- add roles --- click IIS and follow the steps and guidelines.
If IIS is already installed then click on (Web server)IIS --- go to role services ---add role services --- select components to install and follow the guidelines.

After this successful installation of IIS. Go to control panel --- programs --- programs and features --- click on sql server 2005 --- change --- and point to the setup.exe file from the sql server CD, and install reporting services.