Smalltalk › Cincom › VisualWorks

[7.7] Possible to run more than one MSSQL-based store?

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

6 messages Options

Boris Popov, DeepCove Labs (SNN)

[7.7] Possible to run more than one MSSQL-based store?

The default script ends up creating 2 databases called NEWBERN1 and NEWBERN2 for me, which appears to imply that you can only run a single store database on a given instance of SQL Server? Or did I do something wrong? Here are the steps I'd used to get to this point (all our existing databases are PGSQL-based),

-- Execute to create BERN,

USE master

CREATE LOGIN BERN WITH PASSWORD=N'password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

EXEC sp_addsrvrolemember @loginame = N'BERN', @rolename = N'dbcreator'

-- Open visual.im

-- Load StoreForSQLServer

-- Run "Store.DbRegistry installDatabaseTables"

-- Interface: MS_SQLServerConnection

-- Environment: DRIVER={SQL Native Client};SERVER={DCLW701\SQL2005};UID={BERN};PWD={password};

-- User Name: BERN

-- Password: (empty)

-- Table Owner: dbo

-- Create data files? Yes: c:\visualworks\store

-- Enter a unique name for this Store repository: store77sqlsnn

-- Install management policities? No

-- Execute to create individual Store user,

USE master

CREATE LOGIN boris WITH PASSWORD=N'password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE NEWBERN1

CREATE USER boris FOR LOGIN boris

USE NEWBERN2

CREATE USER boris FOR LOGIN boris

-- Create new Store profile

-- Interface: MS_SQLServerConnection

-- Environment: DRIVER={SQL Native Client};SERVER={DCLW701\SQL2005};UID={boris};PWD={password};

-- User Name: boris

-- Password: (empty)

-- Table Owner: dbo

-- Connect

-Boris

DeepCove Labs Ltd.

+1 (604) 689-0322

4th floor, 595 Howe Street

Vancouver, British Columbia

Canada V6C 2T5

http://tinyurl.com/r7uw4

PacNet Services (Europe) Ltd.

+353 (0)61 714-360

Shannon Airport House, SFZ

County Clare, Ireland

http://tinyurl.com/y952amr

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message header. Unless otherwise indicated, it contains information that is private and confidential. If you have received it in error, please notify the sender and delete the entire message including any attachments.

Thank you.

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Boris Popov, DeepCove Labs (SNN)

Re: [7.7] Possible to run more than one MSSQL-based store?

Anyone? It's possible to run multiple store databases on pgsql, I can't imagine I'm the only one wanting to run multiple store databases on mssql?

-Boris

DeepCove Labs Ltd.

+1 (604) 689-0322

4th floor, 595 Howe Street

Vancouver, British Columbia

Canada V6C 2T5

http://tinyurl.com/r7uw4

PacNet Services (Europe) Ltd.

+353 (0)61 714-360

Shannon Airport House, SFZ

County Clare, Ireland

http://tinyurl.com/y952amr

CONFIDENTIALITY NOTICE

Thank you.

From: [hidden email] [mailto:[hidden email]] On Behalf Of Boris Popov, DeepCove Labs (YVR)
Sent: 28 May 2010 11:06
To: [hidden email]
Subject: [vwnc] [7.7] Possible to run more than one MSSQL-based store?

-- Execute to create BERN,

USE master

CREATE LOGIN BERN WITH PASSWORD=N'password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

EXEC sp_addsrvrolemember @loginame = N'BERN', @rolename = N'dbcreator'

-- Open visual.im

-- Load StoreForSQLServer

-- Run "Store.DbRegistry installDatabaseTables"

-- Interface: MS_SQLServerConnection

-- Environment: DRIVER={SQL Native Client};SERVER={DCLW701\SQL2005};UID={BERN};PWD={password};

-- User Name: BERN

-- Password: (empty)

-- Table Owner: dbo

-- Create data files? Yes: c:\visualworks\store

-- Enter a unique name for this Store repository: store77sqlsnn

-- Install management policities? No

-- Execute to create individual Store user,

USE master

CREATE LOGIN boris WITH PASSWORD=N'password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE NEWBERN1

CREATE USER boris FOR LOGIN boris

USE NEWBERN2

CREATE USER boris FOR LOGIN boris

-- Create new Store profile

-- Interface: MS_SQLServerConnection

-- Environment: DRIVER={SQL Native Client};SERVER={DCLW701\SQL2005};UID={boris};PWD={password};

-- User Name: boris

-- Password: (empty)

-- Table Owner: dbo

-- Connect

-Boris

DeepCove Labs Ltd.

+1 (604) 689-0322

4th floor, 595 Howe Street

Vancouver, British Columbia

Canada V6C 2T5

http://tinyurl.com/r7uw4

PacNet Services (Europe) Ltd.

+353 (0)61 714-360

Shannon Airport House, SFZ

County Clare, Ireland

http://tinyurl.com/y952amr

CONFIDENTIALITY NOTICE

Thank you.

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Terry Raymond

Re: [7.7] Possible to run more than one MSSQL-based store?

Boris

Yes it is pretty easy to run multiple repositories on PostgresSQL.

Simply give them different database name.

Terry

===========================================================
Terry Raymond
Crafted Smalltalk
80 Lazywood Ln.
Tiverton, RI 02878
(401) 624-4517 [hidden email]
<http://www.craftedsmalltalk.com>
===========================================================

From: [hidden email] [mailto:[hidden email]] On Behalf Of Boris Popov, DeepCove Labs (YVR)
Sent: Tuesday, June 01, 2010 7:49 AM
To: [hidden email]
Subject: Re: [vwnc] [7.7] Possible to run more than one MSSQL-based store?

Anyone? It's possible to run multiple store databases on pgsql, I can't imagine I'm the only one wanting to run multiple store databases on mssql?

-Boris

DeepCove Labs Ltd.

+1 (604) 689-0322

4th floor, 595 Howe Street

Vancouver, British Columbia

Canada V6C 2T5

http://tinyurl.com/r7uw4

PacNet Services (Europe) Ltd.

+353 (0)61 714-360

Shannon Airport House, SFZ

County Clare, Ireland

http://tinyurl.com/y952amr

CONFIDENTIALITY NOTICE

Thank you.

-- Execute to create BERN,

USE master

CREATE LOGIN BERN WITH PASSWORD=N'password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

EXEC sp_addsrvrolemember @loginame = N'BERN', @rolename = N'dbcreator'

-- Open visual.im

-- Load StoreForSQLServer

-- Run "Store.DbRegistry installDatabaseTables"

-- Interface: MS_SQLServerConnection

-- Environment: DRIVER={SQL Native Client};SERVER={DCLW701\SQL2005};UID={BERN};PWD={password};

-- User Name: BERN

-- Password: (empty)

-- Table Owner: dbo

-- Create data files? Yes: c:\visualworks\store

-- Enter a unique name for this Store repository: store77sqlsnn

-- Install management policities? No

-- Execute to create individual Store user,

USE master

CREATE LOGIN boris WITH PASSWORD=N'password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE NEWBERN1

CREATE USER boris FOR LOGIN boris

USE NEWBERN2

CREATE USER boris FOR LOGIN boris

-- Create new Store profile

-- Interface: MS_SQLServerConnection

-- Environment: DRIVER={SQL Native Client};SERVER={DCLW701\SQL2005};UID={boris};PWD={password};

-- User Name: boris

-- Password: (empty)

-- Table Owner: dbo

-- Connect

-Boris

DeepCove Labs Ltd.

+1 (604) 689-0322

4th floor, 595 Howe Street

Vancouver, British Columbia

Canada V6C 2T5

http://tinyurl.com/r7uw4

PacNet Services (Europe) Ltd.

+353 (0)61 714-360

Shannon Airport House, SFZ

County Clare, Ireland

http://tinyurl.com/y952amr

CONFIDENTIALITY NOTICE

Thank you.

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Boris Popov, DeepCove Labs (SNN)

Re: [7.7] Possible to run more than one MSSQL-based store?

Terry,

I know, I was asking about MSSQL?

-Boris

DeepCove Labs Ltd.

+1 (604) 689-0322

4th floor, 595 Howe Street

Vancouver, British Columbia

Canada V6C 2T5

http://tinyurl.com/r7uw4

PacNet Services (Europe) Ltd.

+353 (0)61 714-360

Shannon Airport House, SFZ

County Clare, Ireland

http://tinyurl.com/y952amr

CONFIDENTIALITY NOTICE

Thank you.

From: Terry Raymond [mailto:[hidden email]]
Sent: 01 June 2010 16:03
To: Boris Popov, DeepCove Labs (YVR); [hidden email]
Subject: RE: [vwnc] [7.7] Possible to run more than one MSSQL-based store?

Boris

Yes it is pretty easy to run multiple repositories on PostgresSQL.

Simply give them different database name.

Anyone? It's possible to run multiple store databases on pgsql, I can't imagine I'm the only one wanting to run multiple store databases on mssql?

-Boris

DeepCove Labs Ltd.

+1 (604) 689-0322

4th floor, 595 Howe Street

Vancouver, British Columbia

Canada V6C 2T5

http://tinyurl.com/r7uw4

PacNet Services (Europe) Ltd.

+353 (0)61 714-360

Shannon Airport House, SFZ

County Clare, Ireland

http://tinyurl.com/y952amr

CONFIDENTIALITY NOTICE

Thank you.

-- Execute to create BERN,

USE master

CREATE LOGIN BERN WITH PASSWORD=N'password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

EXEC sp_addsrvrolemember @loginame = N'BERN', @rolename = N'dbcreator'

-- Open visual.im

-- Load StoreForSQLServer

-- Run "Store.DbRegistry installDatabaseTables"

-- Interface: MS_SQLServerConnection

-- Environment: DRIVER={SQL Native Client};SERVER={DCLW701\SQL2005};UID={BERN};PWD={password};

-- User Name: BERN

-- Password: (empty)

-- Table Owner: dbo

-- Create data files? Yes: c:\visualworks\store

-- Enter a unique name for this Store repository: store77sqlsnn

-- Install management policities? No

-- Execute to create individual Store user,

USE master

CREATE LOGIN boris WITH PASSWORD=N'password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE NEWBERN1

CREATE USER boris FOR LOGIN boris

USE NEWBERN2

CREATE USER boris FOR LOGIN boris

-- Create new Store profile

-- Interface: MS_SQLServerConnection

-- Environment: DRIVER={SQL Native Client};SERVER={DCLW701\SQL2005};UID={boris};PWD={password};

-- User Name: boris

-- Password: (empty)

-- Table Owner: dbo

-- Connect

-Boris

DeepCove Labs Ltd.

+1 (604) 689-0322

4th floor, 595 Howe Street

Vancouver, British Columbia

Canada V6C 2T5

http://tinyurl.com/r7uw4

PacNet Services (Europe) Ltd.

+353 (0)61 714-360

Shannon Airport House, SFZ

County Clare, Ireland

http://tinyurl.com/y952amr

CONFIDENTIALITY NOTICE

Thank you.

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Steven Kelly

Re: [7.7] Possible to run more than one MSSQL-based store?

In reply to this post by Boris Popov, DeepCove Labs (SNN)

I wouldn’t worry that you end up with 2 databases, but rather that their names are hard-coded. However, I count only 4 methods that contain the string *NEWBERN*, so it shouldn’t be too difficult to override them to be “self dbName, '1' ” and “self dbName, '2' ”. Each Store would then have its own pair of databases, e.g. MYBERN1 and MYBERN2. You’d then need an extra field in Store.ConnectionDialog, ConnectionProfile etc., so that you could choose which database you want (unless there’s some way of using the tableOwner info to pick).

Another approach would be to have multiple SQLServer instances (or whatever MS are calling the freely distributable light version of SQLServer), and direct VW to one or the other.

Steve

From: [hidden email] [mailto:[hidden email]] On Behalf Of Boris Popov, DeepCove Labs (YVR)
Sent: 28. toukokuuta 2010 13:06
To: [hidden email]
Subject: [vwnc] [7.7] Possible to run more than one MSSQL-based store?

-- Execute to create BERN,

USE master

CREATE LOGIN BERN WITH PASSWORD=N'password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

EXEC sp_addsrvrolemember @loginame = N'BERN', @rolename = N'dbcreator'

-- Open visual.im

-- Load StoreForSQLServer

-- Run "Store.DbRegistry installDatabaseTables"

-- Interface: MS_SQLServerConnection

-- Environment: DRIVER={SQL Native Client};SERVER={DCLW701\SQL2005};UID={BERN};PWD={password};

-- User Name: BERN

-- Password: (empty)

-- Table Owner: dbo

-- Create data files? Yes: c:\visualworks\store

-- Enter a unique name for this Store repository: store77sqlsnn

-- Install management policities? No

-- Execute to create individual Store user,

USE master

CREATE LOGIN boris WITH PASSWORD=N'password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE NEWBERN1

CREATE USER boris FOR LOGIN boris

USE NEWBERN2

CREATE USER boris FOR LOGIN boris

-- Create new Store profile

-- Interface: MS_SQLServerConnection

-- Environment: DRIVER={SQL Native Client};SERVER={DCLW701\SQL2005};UID={boris};PWD={password};

-- User Name: boris

-- Password: (empty)

-- Table Owner: dbo

-- Connect

-Boris

DeepCove Labs Ltd.

+1 (604) 689-0322

4th floor, 595 Howe Street

Vancouver, British Columbia

Canada V6C 2T5

http://tinyurl.com/r7uw4

PacNet Services (Europe) Ltd.

+353 (0)61 714-360

Shannon Airport House, SFZ

County Clare, Ireland

http://tinyurl.com/y952amr

CONFIDENTIALITY NOTICE

Thank you.

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Peter Fuchs

Re: [7.7] Possible to run more than one MSSQL-based store?

In reply to this post by Boris Popov, DeepCove Labs (SNN)

It is possible to install multiple Store repositories on the same MS SQL-Server.

If you want to create a second repository, you should perform the following:

Using SQL Server Management Studio:

1. create a new create a new database owner account

(if the first repository was created with BERN, then you could name it BERN2).

2. expand Security then Schemas under the databases NEWBERN1 and NEWBERN2 and

add new schema with the same name (BERN2)

From VisualWorks:

3. Store.DbRegistry installDatabaseTables.

Connect with the new table owner name BERN2.

Answer "No" if the procedure asks if it shall create new database

(the new repository will be created in the same databases NEWBERN1 and NEWBERN2, but

with different table owner. The table owner name will be used as prefix to all table names and

you get a separate set of database tables for the second repository).

Peter

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc