Azure SQL Server new Database import and username setup

You have moved to the cloud. Congrats!

1. Find the databases
2. Set Server Firewall
3. Add Client IP
4. Then check out the ADO.NET Connection string
5. will have the server name, username, and password
6. Make sure to have the 1433 port number when connecting with SSMS
7. After connecting to the database
8. I was able to Import my .bacpac file
9. Then came the problem fo the new databases username and password that Sitecore will connect with
10. Run this script on the newly imported database:

    ALTER USER masteruser WITH PASSWORD = 'asdfghjklmznxcvn';
    EXEC sp_addrolemember 'db_owner', 'masteruser';

11. This will reset the password and give the new user the necessary access.
12. Then change the connectionstrings.config in the App_Config folder
13. and Voila!
14. Your Sitecore is now using the new database.

 

~ always ask, Without asking you will not get answers. ~

Sitecore Analytics stopped working after server move or rename?

Not sure what happened?

I didn’t either until stack exchange came back online.

 

so, there is this SQL server database that Sitecore uses, called ShardMapManager.

In which there is a table called [__ShardManagement].[ShardsGlobal]

See that ServerName column on the right?

You know what to do with it.

There are also other steps you need to take to clean up other configs, but that’s another day another post.

 

 

~ Just Do It. ~

Get your Sitecore admin password back… The famous admin / b

You are on a dev machine.

forgot admin password? got the database from the client and didn’t give you the password? don’t want to use a complicated password for dev? Someone else changed it on you then left the company or got hit by a bus? password123 is too long to type?

Run this SQL script on xxxx_Core database

FOR Sitecore 8 or older:

UPDATE [aspnet_Membership] 
SET Password='qOvF8m8F2IcWMvfOBjJYHmfLABc=' WHERE 
UserId IN (SELECT UserId FROM [aspnet_Users] 
          WHERE UserName = 'sitecore\Admin')

Thanks, Mark Cassidy

FOR Sitecore 9 and newer:

UPDATE 
    [aspnet_Membership] 
SET 
    [Password]='qOvF8m8F2IcWMvfOBjJYHmfLABc=', 
    [PasswordSalt]='OM5gu45RQuJ76itRvkSPFw==', 
    [IsApproved] = '1', 
    [IsLockedOut] = '0'
WHERE 
    UserId IN (
        SELECT UserId FROM dbo.aspnet_Users WHERE UserName = 'sitecore\Admin'
    ) 

Thanks, Derek Dysart

 

 

~ Always wear a Helmet, Inside, outside, Above and below. ~

Replace text in a SQL server table. Error then Increasing the size of the column.

The best way is to use

update table set column = REPLACE(column, 'new text' , 'old text')

I did get an error.

String or binary data would be truncated

Not to worry.

This only means the text I was replacing with was pushing the max limit of the field’s length. You can either reduce the text length of the field. or increase the size of the column.

the best way to do that is:

Alter Table anytable Alter column Col2 Varchar(64)

With this I again ran into an error:

The index ‘IX_keyword’ is dependent on column ‘Col2’.
ALTER TABLE ALTER COLUMN Col2 failed because one or more objects access this column.

To fix that I had to drop the index. Update the length and then recreate the index.

To Drop Index:

Drop Index IX_pl_Keywords on pl_keywords

To create an index use:

USE [Database]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Keywords] ON [dbo].[Keywords] 
(
[keyword] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

~ Life is just a bunch of ifs and elses with some random function calls in between. ~

Oops!! you made a database on a newer version on dev and now you need to downgrade to roll it out!!!

I had access to SQL server 2017 on localhost. Worked on that to get everything ready. The upstream servers are all 2016 and client is not ready to upgrade the servers.

There is no easy one-click way fo doing it.

There is some info on here: https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/

So, I pretty much right clicked and generated scripts for all objects with schema and got that to the DBA to run on 2016.

& Voila!