Moving Database Files on the secondary server in Log Shipping

http://blogs.technet.com/b/mdegre/archive/2009/09/25/moving-database-files-on-the-secondary-server-in-log-shipping.aspx

 

 

You are using Microsoft SQL Server Logshipping for one of your VLDB (very large database). You would like to move data files on another disk on the secondary server without reconfiguring log shipping.

As you cannot use the detach/attach database, you could follow the way below :

This solution is inspired of the documentation "Moving Database Files" http://technet.microsoft.com/en-us/library/ms345483.aspx


Step 1 : If your secondary is with the option "Standby Mode", you must change by "no recovery mode". And perform a transaction (insert, delete or update)
After, you have to manually run the job of backup, Then the jog of copy and the restore job.

Step 2 : Collect the logical name :

SELECT
  name as logicalname, physical_name as filename
FROM
  sys.master_files
WHERE
  database_id = DB_ID('yourDatabaseName');

Step 3 : Move the file on the path expected :

ALTER DATABASE yourDatabaseName

MODIFY FILE( NAME = logicalname, FILENAME = 'M:\newpath.mdf')

Step 4 : Stop SQL server service, move the database file and start SQL server Service

Step 5 : Check that the update is ok with the query below :

SELECT
  name as logicalname, physical_name as filename
FROM
  sys.master_files
WHERE
  database_id = DB_ID('yourDatabaseName');


You can follow the same way to add a file to a log shipped database. 

user is not able to access the database "msdb" under the current security context.

Symptoms when the guest user is disabled in the msdb database

USE msdb;
SELECT prins.name AS grantee_name, perms.*
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS prins
ON perms.grantee_principal_id = prins.principal_id
WHERE prins.name = 'guest' AND perms.permission_name = 'CONNECT';

GO

If you receive a result set that resembles the following, the guest user has the necessary permissions. 

 

If you receive either an empty result set or if the state_desc shows DENY in the result set that is mentioned here, the guest user is disabled in the msdb database. You may receive error 916 when you connect to a database.

How to resolve the issue

USE msdb;
GRANT connect TO guest;
GO

http://support.microsoft.com/kb/2539091/en-us

Add Article Replication _Simpler Way

Use Test_Model
go
Exec sp_changepublication 'Test_model_pub','allow_anonymous',false
GO
Exec sp_changepublication 'Test_model_pub','immediate_sync',false
GO

sp_addarticle 'Test_model_pub', product5, @Source_object=product5
GO
sp_refreshsubscriptions 'Test_model_pub'

GO
sp_startpublication_snapshot 'Test_model_pub'

Editing Published SSIS Package in SQL Server 2012

Editing Published SSIS Package in SQL Server 2012

Many Thanks  :- http://www.mssqlgirl.com/editing-published-ssis-package-in-sql-server-2012.html


So, you have inherited a set of SQL Server 2012 Integration Services projects that have been deployed on a server. Your company do not use source control so you can’t easily see what was last published (Oh no!!). Or, you have deployed something on the server but you are not sure if it is the right version. Bottom line is you want to inspect or to edit a package that is already published on the server. Unfortunately at the moment  there is no way of just quickly downloading that single SSIS package.
SQL Server 2012 Integration Services now is now making use of  Project Deployment Model. For more information, read it here. Back to the issue at hand, if you want to get a copy of what is in the production, in summary, there are two “easy” ways of doing so.

A. Creating a new project in SQL Server Data Tools (SSDT) and import the project from existing catalog.

B. Exporting the project into ispac file via SQL Server Management Studio (SSMS).

Caveat: Both these options require access to the project on the server.

Option A: New Project

1. In the New Project dialog box of SSDT, browse to Installed Templates > Business Intelligence > Integration Services on the left navigation pane.
2. Choose Integration Service Import Project Wizard option, enter the Name, Location and Solution Name accordingly. Then click OK to proceed to the next step.

3. Click Next on the Integration Services Import Project Wizard window

4. On the Select Source dialog, choose Integration Services Catalog. Then provide the details of the project on the server that you wish to import.

5. Follow the rest of the instruction.

6. Once the project is created, you can browse to the package(s) that you wish to inspect. In my example here, I have  4 connection manager files and 9 dtsx files.

This option is one of the safest / simplistic ways of getting SSIS project from the server. All the components in the Project, such as Project Variables and Project Connection Managers are kept in tact.

Option B: The shortcut

1. Navigate to the SSIS project on SSMS; i.e.  [Server Name] > Integration Services Catalogs > SSISDB > [Project Folder] > Projects > [Project Name] as shown on the picture below.

2. Right click on the [Project Name], then choose Export….
3. Choose the destination folder on “Save As” to save the .ispac file
4. Navigate to the folder where the file has been saved and rename the file extension to .zip file.
5. Browse the zip file and you should be able to see the .dtsx file(s).  Please note that in this example, I have 4 connection manager files and 9 dtsx files.

6. Now you can extract the desired .dtsx file(s) from the zip file.
Note: If the server is in Production, you could ask your DBA to export the .ispac file of the project (Step 1 & 2). Then you can continue with Step 3.
Special thanks to Josh Fennessy (b | t), for providing Option B.

Would it work for SSDT Visual Studio 2012?

Update – 6 March 2013: Both options work for Visual Studio 2012 too. (Read Microsoft announcement on SQL Server Data Tools – Business Intelligence for Visual Studio 2012). At the time of writing (a few minutes of exploring SSDT Visual Studio 2012), everything works the same for Option A. There is a major face-lift with monochrome look.

Wrap Up

Integration Services in SQL Server 2012 introduces Project Deployment Model. This means that accessing a single SSIS package that has been published from the server is not as trivial. This article describes two ways of accessing the desired package(s) or the project as a whole from a server. Option A, which is importing the package from the Integration Services Catalogs is the safest way. Option B, is a shortcut method that is recommended only for advanced level users.
This article does not describe how the pros and cons of the new Integration services Project Deployment Model as it is covered in a couple of blogs as listed on the Further Reading section.

WMI Provider Error - Access is denied - while changing SQL Server account password

To give this right:
Login with administrative privileges to SQL Cluster nodes.
Click Start, type mmc in the Search programs and files box, and then press ENTER.
On the File menu, click Add/Remove Snap-in.
Under Available snap-ins, double-click WMI Controls, and then:

• Select the computer that you manage. Connect to: select Local Computer, click Finish. Click OK.
• On Console Root tree, select WMI Controls, right click, select Properties, then go to Security tab, locate root\Microsoft\SqlServer\ ComputerManagement10 (or 11 for SQL Server 2012) namespace then click Security.
• Add new domain user to Group or Users Names
• Select new domain user in Group or Users Names and Allow Read Security for the Permissions of new account, click OK.